OLE and Excel

[t1950]

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.

[t1950 · Accepted solution]

Thanks for all of the answers, but I still can't the programming to work.

With some MINOR changes, this is the actual programming below

nvo_ole     lnvo_ole

for lp = 1 to filecount

     if isValid( lnvo_ole)=False then

          lnvo_ole = Create nvo_ole

          io_ole = Create OleObject     // this is in a function call, io_ole is an instance variable in the nvo

          io_ole.ConnectToNewObject( "Excel.Application" )     // in the same function call as above

     end if

     lnvo_ole.io_ole.ConnectToObject( fileName[ lp ] // function call in the nvo

     ... Read the Excel file and move data into a datastore // this works correctly

     // this line throws an error, which I trapped with Catch Try

     // error is Name not found accessing external object property activeworkbook...

     // lnvo_ole.ActiveWorkBook.Close()

     // this line throws an error, which I trapped

     // error is Name not found accessing external object property application...

     // lnvo_ole.io_ole.Application.Quit()

     lnvo_ole.io_ole.Disconnect()     // nvo function call

     // tried this, but it still doesn't work, i get the same data as the first file

     Destroy lnvo_ole

next

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

I suggest that you encapsulate all the logic/processing for the excel files in the nvo that you have created for that purpose.

Some code snippet to properly open and close the excel file.

say you have a function in your nvo called of_process_excel_files(...)

since io_ole is an instance variable, so you may create that in the constructor event or post a user event to create the oleobject in the nvo. A function call to create that is also fine.

ue_create_ole event:

     io_ole = create oleobject

     li_rc   = io_ole.ConnectToNewObject("excel.application")

     if li_rc <> 0 then

        messagebox("error","Error running MS Excel")

        destroy io_ole

        ib_flag=false

    else

       ib_flag = true  //boolean instance variable to indicate that the oleobject was created successfully.

    end if

of_process_excel_files()

    oleobject lnv_wb, lnv_ws

    For i = 1 to totalexcelfiles

          io_ole.WorkBooks.Open(excelfiles[i],stgRead!,stgDenyRead!)

          lnv_wb = io_ole.application.workbooks(1)  // reference to the workbook

          lnv_ws = lnv_wb.worksheets(1) //reference to the worksheet

          // do your processing for the workbook/worksheet here

          // if you need to populate a datawindow or datastore, pass a reference to it in this function.

           lnv_wb.close() //close the workbook, but not the excel app

      Next

destructor event of the nvo

     if IsValid(io_ole) Then

         io_ole.application.Quit()

         io_ole.DisconnectObject()

         Destroy io_ole

     end if

In the calling window/object

     nvo_ole lnv_ole

     lnv_ole = create nvo_ole

     if lnv_ole.ib_flag = True Then

        lnv_ole.of_process_excel_files() // pass the necessary parameters.

     end if

     destroy lnv_ole.

That's it, I think. Modify as you deem fit.

Please note that I did not declare some of the variables in the code snippet.

[t1950 · Accepted solution]

Thanks for you help Renee.  Your suggestions got me on the right path.  Neil Garcia had some ideas that I also used.  I'm adding the skeleton of my programming here.

lnvo_ole = Create nvo     // which creates io_ole an OleObject

lnvo_ole.io_ole.ConnectToNewObject("Excel.Application")

for lp = 1 to filecount

     path_and_file_name = pfn[ lp ]     // so i could debug

     errorString = String( io_ole.Workbooks.Open( path_and_file_name ) )   

     // some programming here

     cellData = String( io_ole.Application.WorkBooks(1).WorkSheets(1).cells( row, cell ).value )

     // more programming

     errorString = String( io_ole.Workbooks.Close() )

next

Destroy lnvo_ole

Again, thanks to all of those that replied to my post.

[Former Member]

You may also want to look into buying LibXL from http://www.libxl.com . It's a DLL that can directly open and read/write XLS/XLSX/XLSM files. It's free to distribute and has no dependency on Excel.

[Former Member]

Use ConnectToNewObject to connect to Excel application.

Use ConnectToObject to connect to Excel with a specific file.

You need only one of the two ways!

As Jeff wrote I would prefer to use ConnectToNewObject. Then you can loop through the files. After all files are processed you should close, Disconnect and destroy.

[Former Member]

I think you need to close Excel properly before going to the next iteration.

I generally use the following steps:

ioo_excel = CREATE OleObject

li_rc = ioo_excel.ConnectToNewObject("Excel.Application")

ioo_excel.workbooks.Add()

// add processing here

ioo_excel.activeworkbook.saveas(ls_filename)

ioo_excel.activeworkbook.close()

ioo_excel.Application.quit  // Ensure Excel is closed properly

ioo_excel.DisconnectObject()

DESTROY ioo_excel

[Former Member]

Hi Tom, maybe you have some typos in your sample? Is the OLE object NVO_OLE or IO_OLE?

Assuming IO_OLE, you should be doing something like this...

OLEOBJECT

io_ole

for loopCounter = 1 to fileNameCount

     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

     io_ole.DisconnectObject ( )

     destroy io_ole

next

[t1950]

hello Jeff,

thanks for looking at my issue.  i created an nvo that has all of the calls i need to "talk" to Excel.

there's one instance variable
OLEObject io_ole

the functions are as follows:

f_excel_create()
      io_ole = CREATE OleObject
      io_ole.ConnectToNewObject("Excel.Application")

f_excel_connect( path_and_file_name )
      io_ole.ConnectToObject( path_and_file_name )

f_excel_cell_data_get ( row, cell, ref cellData )
      cellData = String( io_ole.Application.WorkBooks(1).WorkSheets(1).cells( row, cell ).value )

f_object_disconnect()
      io_ole.DisconnectObject ( )

f_excel_destroy()
      Destroy io_ole

even if i DESTROY the nvo and recreate it, i still get the data from file1 when it SUPPOSEDLY connects to file 2

[Former Member]

That looks right.  The only thing I do differently is that I've never used ConnectToObject... I believe you're re-referencing a new OLE object into io_ole?  Not sure.

You may want to try something like this, which works for me.

io_ole.Application.Visible = FALSE

io_ole.Workbooks.Open( ls_filename )

(Work with the spreadsheet)

io_ole.ActiveWorkbook.Save()

io_ole.ActiveWorkbook.Close()

io_ole.Quit()

io_ole.DisconnectObject()

destroy io_ole