Skip to Content

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • Best Answer
    Oct 31, 2015 at 06:24 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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.

  • avatar image
    Former Member
    Oct 26, 2015 at 07:40 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Tom Mangano

      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

  • avatar image
    Former Member
    Oct 27, 2015 at 02:38 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 27, 2015 at 06:47 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Oct 27, 2015 at 12:14 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded