Cancel
Showing results for 
Search instead for 
Did you mean: 

Excel COM to copy multiple Sheets

Pioneer
Pioneer

Hello,

 

I want to copy multiple sheets from a single excel file. I am able to use multiple readexcel files, but it seems slow.  I am guessing this is becuase it is initiating a COM connection each time it reads a new sheet.

 

I am thinking it may be more efficient to create an excel workbook object as shown below, then read and write each sheet.  This way the COM connetion is only made once.  However, I can not think of a way to move the data from the excel sheet to a Plant Sim tablefile.

Is there a method to do this?  If so,  shouldn't this be a faster method?

 

var excel:=createCOMObject("Excel.Application","")
excel.workbooks.open(Filepath)

Thanks

2 REPLIES 2

Re: Excel COM to copy multiple Sheets

Gears Esteemed Contributor Gears Esteemed Contributor
Gears Esteemed Contributor

you can access all methods and attributes of the Excel- COM object to read the data e.g.


var excel:=createCOMObject("Excel.Application","")
excel.workbooks.open(Filepath)
for var i:=1 to y loop
for var k:=1 to z loop
tableFile[i,k]:=excel.activeWorkbook.sheets("Sheet1").cells(k,i).value
next
next

Steffen Bangsow
freelance simulation specialist  
web: www.bangsow.eu
mail: steffen@bangsow.net

Re: Excel COM to copy multiple Sheets

Pioneer
Pioneer

I imagine this would be a slow soution as well.  Within excel I can copy a range to a range, which is fairly effcient. But since I am copying a range within excel to a PlantSim table this is not available.