COMLIB--Excel webdata download

[Edja]

I have configured an Excel-sheet to download stock exchange data from a financial website on opening the sheet. It then updates the same data every 10 min as long as the file is open.
The file is opened by my BB4W program, using COMLIB, which reads it every 10 min after Excel has done a new download of the webdata (also every 10 min, but there is no need to be in sync)
All this behaves as described above if I do this manually. However it does not work from the BB4W code : the Excel file reads the data that are already present (from a previous session) but doesn’t download any new data from the website. No error messages are produced.
The following code is merely for the purpose of illustrating how I've gone about. The code has been tested and shows the content of cell(109,1). This cell should show a new value every 10 min but always stays the same.

Code: Select all

      INSTALL @lib$+"COMLIB":INSTALL @lib$+"NOWAIT"
      Invest$="F:\DOCU_NEW\BEURS\Experiment1.xlsx"
      Invest=OPENUP(Invest$):CLOSE#0:IF Invest=0 PRINT "File not available":END
      PROC_cominit:ON ERROR PROC_Cleanup
      Xap=FN_createobject("Excel.Application")
      Inv=FN_getobject(Xap,"Workbooks.Open("""+Invest$+""")")
      Stl=FN_getobject(Inv,"WorkSheets(1)")
      FOR z=1 TO 50
        PROCwait(60000)
        testvalue$=FN_getvaluestr(Stl,"cells(109,1).value")
        PRINT     testvalue$
      NEXT z
      PROC_Cleanup
      END

      DEFPROC_Cleanup
      Stl+=0:IF Stl PROC_releaseobject(Stl):Stl=0
      Inv+=0:IF Inv PROC_callmethod(Inv,"Close(B FALSE)"):PROC_releaseobject(Inv):Inv=0
      Xap+=0:IF Xap PROC_callmethod(Xap,"Quit"):PROC_releaseobject(Xap):Xap=0
      PROC_comexit
      ENDPROC

So, for now, I have to update the data by manually opening/closing/saving the sheet, then start my BB4W program and repeat this every 10 min.
… unless someone can advise me how to control this correctly from BB4W with COMLIB ?
Any ideas someone ?

[Zaphod]

Perhaps you need to Activate WorkSheet(1)?

Z

[Edja]

Zaphod, thanks for your suggestion !
I've tried the following code :

Code: Select all

      Inv=FN_getobject(Xap,"Workbooks.Open("""+Invest$+""")")
      PROC_callmethod(Inv,"WorkSheets(1).Activate")
      testvalue$=FN_getvaluestr(Inv,"ActiveSheet.cells(109,1).value")
      FOR z=1 TO 20
           PROCwait(3000)
           testvalue$=FN_getvaluestr(Inv,"ActiveSheet.cells(109,1).value")
      NEXT z

Unfortunately, it works the same as my first code.

But I was able to dig a little deeper :
The renewal is configured in Excel to start on opening the excel-sheet and then again every 3 min until Excel is closed.
But the very first renewal (when Excel is launched from BB4W) does not go through.
The first few iterations in the FOR ...NEXT sequence succesfully then read the contents of cel(109,1) until a renewal starts after 3 min.
An error is then generated after exactly 3 min :

FN_getvaluestr failed on "cells(109,1).value" - UNKNOWN INTERFACE in module C:\Program Files (x86)\BBC BASIC for Windows\lib\COMLIB

I suppose that performing a read (getvaluestr) on the Excel-sheet while a web-data renewal is ongoing by Excel creates a conflict.

If I can find a way to do a read AFTER the web data has been done then maybe it'll work. But this will require some synchronisation. Maybe a solution can be found in the MSDN pages, but usually I find that very hard to chew.
I'll experiment further in the coming days.
All ideas welcome.
Edja