OLE and Excel
I'm using PB 12.5 build 2511, Win7 professional, Excel 2010
I'm trying to capture a list of Excel files, and READ the data in each of the cells.
the programming is as follows:
for loopCounter = 1 to fileNameCount
create nvo_ole //create an nvo that has the Excel calls
nvo_ole.io_ole = Create OleObject
io_ole.ConnectToObject( path_and_file_name ) // pass in the path and file name
// do some processing // this part works as expected for the FIRST file
nvo_ole.io_ole.DisconnectObject ( )
when the loop tries to process the second file, the data is not correct. it's ALWAYS the data from the first file.
I tried recreating and destroying the nvo object as a last resort.
Ideally, I would like to create the nvo once, "clear" the data in the object, connect the next file, and continue the processing.
René Ullrich replied
io_ole.ConnectToNewObject( "Excel.Application" )
connects you to excel application
lnvo_ole.io_ole.ConnectToObject( fileName[ lp ])
you connect the OLEobject to a excel workbook.
A Workbook object doesn't have a ActiveWorkbook property.
You have to made a decision:
You may connect to Excel.Application. Don't use ConnectToObject in this case! Use io_ole.Workbooks.Open to open the file you want to read. Close the file with ActiveWorkbook.Close. Quit excel if you read all files with Application.Quit.
Or you wan't to connect the OLEObject to the excel file. Then you shouln't use ConnectToNewObject. Disconnect the OLEObject if you finished reading the file. After that you can connect to the next file. I think you don't need to close the file and application in this case.