Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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.ConnectToNewObject( "Excel.Application")

     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 ( )

     destroy nvo_ole

next

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.

Tags:
replied

io_ole.ConnectToNewObject( "Excel.Application" )

connects you to excel application

but then

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.

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question