sample code for automation

[sgsong]

Hi,

Can anyone post a simple example on how to automate excel using thinbasic code? The "comm" directory for the smaple code is empty. Thanks!

[ErosOlmi]

COMM directory was for Serial Communication examples. But due to possible licence violation with the compiler we are using (Power Basic compiler) we had to remove it for a while.

Regarding COM (Component Object Model) automation, it is something we are working on in this period but it will take still many months of work before releasing something stable.

Our goal is to have a syntax very close to VBScript one: Interface.Property or Interface.Method(...). On this purpose we just introduced VARIANT data type whose usage will be mainly for COM automation. Again, it will take some time before having a stable COM interface.

Sorry
Eros

[sgsong]

Hi Eros,

Thanks for the clarification and thanks for the good work embeded in thinbasic. I hope it gets stronger everyday.

Best,
Shige

[ErosOlmi]

Shige,

we try not to promise anything we are not sure we will be able to develop but in this case I can promise next thinBAsic version will have a starting of COM automation module.

I have improved VARIANT data type and Roberto has developed a new thinBasic module (thinBasic extension) called COM with a list of functions that will allow to interface with COM servers. This will be just the basis of a more complete and easy to use COM interface we will talk in future thinBasic releases.

Next thinBasic version will be ready in a couple of weeks.

But let's stop talking abou it and see following example that is already working on our test machines:

uses "COM"

%Max_Param = 3

dim pXlApp as dword
dim pXlBooks as dword
dim pXlSheet as dword

dim vParam(%Max_Param) as variant
dim vRetVal as variant

dim RetVal as long
dim nRow as long
dim nCol as long

%MaxRows = 100
%MaxCols = 20

dim T1, T2 as double

'---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_Execute(pXlApp, "Visible", %TB_DISPATCH_PROPERTYPUT, 1, vParam, 0)) then

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

'---Now add a new workbook ...
if COM_Succeeded(COM_Execute(pXlApp, "Workbooks", %TB_DISPATCH_PROPERTYGET, 0, 0, vRetVal)) then

pXlBooks = vRetVal
if COM_Succeeded(COM_Execute(pXlBooks, "Add", %TB_DISPATCH_METHOD, 0, 0, 0)) then
msgbox 0, "A new workbook should be added!"
else
msgbox 0, "Add workbook fails"
end if

'--- ... now get the active sheet and change it's name
if COM_Succeeded(COM_Execute(pXlApp, "ActiveSheet", %TB_DISPATCH_PROPERTYGET, 0, 0, vRetVal)) then

pXlSheet = vRetVal
vParam(1) = "ThinBASIC"
if COM_Succeeded(COM_Execute(pXlSheet, "Name", %TB_DISPATCH_PROPERTYPUT, 1, vParam, 0)) then

msgbox 0, "ActiveSheet name should be changed!"
T1 = timer
randomize
'--- Fill a suqre region with random numbers
for nRow = 1 to %MaxRows
for nCol = 1 to %MaxCols
vParam(3) = nRow
vParam(2) = nCol
vParam(1) = rnd(1, 20000)
COM_Execute(pXlSheet, "Cells", %TB_DISPATCH_PROPERTYPUT, %Max_Param, vParam, 0)
next
next
T2 = timer

msgbox 0, "Filled " & str$(%MaxRows * %MaxCols) & " cells in " & format$(T2 - T1, "#0.000") & " seconds"

'--- Exit from Excel
if COM_Succeeded(COM_Execute(pXlApp, "Quit", %TB_DISPATCH_METHOD, 0, 0, 0)) then
msgbox 0, "OK script finished. Quit method executed. Excel should ask to save or not the sheet. Up to you."
end if
end if
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(pXlSheet))) then
msgbox 0, "ActiveSheet release fails"
end if
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