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

[chris_bdnsee]

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 :violent:!

best regards

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

[Petr Schreiber]

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]

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

[Petr Schreiber]

Hi Chris,

I guess GetIndexedProperty could serve ... but not sure how :oops:

Maybe, in the meantime, shelling of VBS file could partially save the problem? I am not sure what your application needs to do in the end.


Petr