COM with Excel - I will fail without support.....

**chris_bdnsee** · 30-03-2009, 22:14

All,

I am totally frustrated......
I thought that I had some basic knowledge about programming but I am totally lost.

I am struggling with the COM Interface of thinBasic, or to say it better I can't handle it.....

I have examined the COM example, learned something and tried to figure out a very simple task. Just open an existing xls file - well sound's easy, but I can't get it working.

So i need some smarter brain from the community.

I figured out that there is a method of the application object:

Workbooks.Open "ANALYSIS.XLS"

However my thinbasic translation:

if COM_Succeeded(COM_CallMethod(pXlApp, "Workbooks.Open Filename:=" & $DQ & "D:\thinBasic\Projekte\GraphWizard\GraphWiz_Output_v1.xls" & $DQ, 0, 0, 0)) then
MsgBox 0, "Klappt"
end if

returns always with an error of:
Error: 80020006
Desc: Unknown name
Function: GetIDsOfNames()
Member: Workbooks.Open Filename:"....
TB equate: Unavailable

Im using Win XP SP2 with Excel 2000.

Any more insight on the COM functions of thinBasic with Excel is really appreciated......

My plan is to use thinBasic as an intellegent interface between MS Access and setting up some advanced xls graphs, so I would need to go down very deep in the excel object model - but I am not sure whether thinBasic is right for me or the COM interface of thinBasic is spelled out enough for me to solve the task.....

Thank you in advance for supporting a real frustrated guy

!

best regards

Christian
PS: To make one thing clear - I really like thinBasic - it's more about my missing knowledge....

**Petr Schreiber** · 30-03-2009, 23:29

Hi,

no need to be frustrated any more ( well, I hope ), here is my minimal example to open file in Excel.
The trick is to separate Method name, and its parameters, which should be passed as array of variants.

Tested with MS Office 2007, elemental student version.

See here:

 uses "COM"

 %Max_Param = 1

 dim pXlApp  as dword
 dim pXlBooks as dword
 
 dim vParam(%Max_Param) as variant
 dim vRetVal      as variant

 dim RetVal  as long

 '---Try to create an Excel application reference
 pXlApp = COM_CreateObject("Excel.Application", RetVal)

 '---If OK we will procede
 IF COM_Succeeded(RetVal) THEN

  '---Try to set excel visible
  vParam(1) = 1
  if COM_Succeeded(COM_SetProperty(pXlApp, "Visible", 1, vParam)) then

   '---OK, we got it
   msgbox 0, "Now Excel should be visible!"

   '---Now add a new workbook ...
   if COM_Succeeded(COM_GetProperty(pXlApp, "Workbooks", vRetVal)) then

    pXlBooks = vRetVal
    
    ' -- Com call method takes parameters from VARIANT array!
    vParam(1) = "D:\thinBasic\Projekte\GraphWizard\GraphWiz_Output_v1.xls"
    if COM_Succeeded(COM_CallMethod(pXlBooks, "Open", 1, vParam(1), vRetVal)) then
     msgbox 0, "File opened!"
    else
     msgbox 0, "File open failed, why ... why ... why"
    end if
   end if   
  else 
   msgbox 0, "Visible fails"
  end if
  
 END IF

 '--- Time to release the allocated interface objects
 if isfalse(COM_Succeeded(COM_Release(pXlBooks))) then
  msgbox 0, "Workbooks release fails"
 end if
 if isfalse(COM_Succeeded(COM_Release(pXlApp))) then
  msgbox 0, "Excel application release fails"
 end if

Have a nice day,
Petr

**chris_bdnsee** · 31-03-2009, 13:59

Hello Petr,

again thank you for your help....

As I understand it now, tb has no layer in between the native COM interface and some of the most used apps, like MS Excel.

So I need to work with the plain COM calls.

Allright, I also understand that COM calls need to be constructed down to the object which has the method to do something (in stead of app.workbooks.open allocate the app, allocate the workbook and then call the method of the workbook).

How do I find out what paramaters of the COM_CallMethod do i need to fill ?
How is the return values from object collections handled ?
How can I pass not only the value to a cell but the formula ?

I have added an xls file and the typical tasks I would need to get accomplished by tb are changes like those (in VB code, also in the xls as macro):

Sheets("Graph").Select
ActiveSheet.ChartObjects("Diagramm 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "=Data!R5C3:R10C3"
ActiveChart.SeriesCollection(1).Values = "=Data!R5C5:R10C5"

and

Sheets("Graph").Select
ActiveSheet.ChartObjects("Diagramm 4").Activate
ActiveChart.ChartArea.Select
ActiveChart.ChartType = xlBubble3DEffect
ActiveChart.ChartType = xlBubble3DEffect
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = "=Data!R5C2:R9C2"
ActiveChart.SeriesCollection(2).Values = "=Data!R5C5:R9C5"
ActiveChart.SeriesCollection(2).Name = "=""DatenReihe2"""
ActiveChart.SeriesCollection(2).BubbleSizes = "=Data!R5C4:R9C4"
ActiveChart.ChartType = xlBubble3DEffect

I would understand if I have put too many questions on your table.....
However I got stuck and I don't see a way out with thinBasic....

best regards

Christian