1 Attachment(s)
COM with Excel - I will fail without support.....
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....
Re: COM with Excel - I will fail without support.....
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:
Code:
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
1 Attachment(s)
Re: COM with Excel - I will fail without support.....
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
Re: COM with Excel - I will fail without support.....
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