Excel COM question... [Archive] - thinBasic: Basic Programming Language Community Forum

John Spikowski

17-07-2014, 21:06

Hi Gerald,

Sorry to hear you are struggling with COM. Funny that COM was PB's claim to fame (COM done right) but no one ever used it. (too difficult even with Jose Roca's includes) Here is a Script BASIC Excel example that might help you along.

import com.inc

'on error resume next

filename = "c:\\warehouse.xls"

if FileExists(filename) then
print "File already exists deleting: ", filename,"\n"
delete filename
end if

oExcelApp = CreateObject("Excel.Application")

if oExcelApp = 0 then
print "Failed to create Excel Object do you have it installed?"
return
end if

'vbs: Set ExcelWorkbook = ExcelApp.Workbooks.Add
oWorkBook = CallByName(oExcelApp, "Workbooks", vbGet)
oExcelWorkbook = CallByName(oWorkBook, "Add")

'vbs: Set ExcelSheet = ExcelWorkbook.Worksheets(1)
oExcelSheet = CallByName(oExcelWorkbook, "Worksheets", vbGet, 1)

print "Adding cells...\n"

for i=0 to 10
for j=0 to 10
'vbs: ExcelSheet.Cells(i, j).Value = "test-" & i & "-" & j
oCell = CallByName(oExcelSheet, "Cells", vbGet, i, j)
CallByName(oCell, "Value", vbLet, "test-" & i & "-" & j)
ReleaseObject(oCell)
next
next

print "Saving document as:", filename, "\n"

CallByName(oExcelWorkbook, "SaveAs", vbMethod, filename)
CallByName(oExcelWorkbook, "Close")
CallByName(oExcelApp, "Quit")

print "Releasing objects from memory...\n"

ReleaseObject(oExcelSheet)
ReleaseObject(oExcelWorkbook)
ReleaseObject(oWorkBook)
ReleaseObject(oExcelApp)

print "Script complete!\n"

http://files.allbasic.info/ScriptBasic/SBCOM/sbvb_excel.png

Gerald Sutherland

17-07-2014, 21:25

@ Petr

Thanks, Petr! At least I have a reference to use. Now the problem is finding and translating the correct parts into something that thinBasic will be happy with. ;-)

@ John

Thank you, John! I have done some Excel COM using PB (and it worked well). However, it seems that thinBasic wants things phrased somewhat differently and that has tripped me up. :-) Or perhaps that was just the example script.

Your example looks a lot closer to what I am used to so maybe there is some hope for me yet. :-)

Thank you both! ;-)
Jerry

John Spikowski

18-07-2014, 01:09

I'm sure between Petr and Eros they will iron out your COM issues with thinBasic. While you're waiting, maybe you could test out the new Script BASIC IDE/Debugger (https://github.com/dzzie/ScriptBasic_Control/raw/master/SBDebug_Setup.exe).

http://files.allbasic.info/ScriptBasic/SBCOM/sbdebugger.png

This project aims to create a VB6 usable ScriptBasic Engine.
along with a an integrated IDE + debugger.

Features include:

VB6 access class to ScriptBasic Engine
- AddObject
- AddCode
? Eval

IDE as VB6 ActiveX control
- intellisense
- syntax highlighting
- integrated debugger
- breakpoints
- single step
- step over
- step out
- variable inspection
- call stack
- variable modification
- run to line

Notes:

- auto complete/intellisense has several scopes. hit ctrl+space to trigger.
if there is a partial identifer already typed, with only one match, the
string will be auto completed. If there are multiple matches, then the
filtered results will be show in intellisense list. If no matches are found
then entire list will be shown.

The following scopes are supported:

- import statements - lists *.bas in specified /include directory
- external module functions - parses the *.bas headers to build func list.
- built in script basic functions
- is not currently aware of script variable names

- for module functions (ex curl::) to show up, the matching import must exist
(include file name, must match embedded module name)

- debugger variable inspection / modification - When debugging a list view
of variable names, scopes, and values is kept. You can edit values by right
clicking its list entry. Array values can be viewed by double clicking on
its variable name to bring up the array viewer form.

You can also display a variable value, by hovering the mouse over it in
the IDE window. A call tip will popup showing its value. Click on the call tip
to being up the edit value form. Longs and string values are supported. You can
also prefix a string with 0x for hex numbers.

- parse errors will show up in their own listview. Each error will get its own entry.
where possible line numbers, files, and error descriptions are provided. Clicking
on the entry will jump to that line in the IDE (if one was given by SB engine)

- changes to scripts are automatically saved each time they are executed.

Gerald Sutherland

18-07-2014, 04:10

Thanks John,

I've downloaded Script Basic, but my not be able to do much until the weekend. :-)

I think I might know where I'm going wrong here, but I won't know until I get a few small details working. It just get' frustrating because whatever documentation you find ... you have to alter the commands to fit the language you're trying to use them from (except for VBA, I guess). :-)

So, once I get to the point where I know how to convert the commands in the documentation I think I'll be okay.

I'll be leaving the company I work for so I need to find another language translate my programs into because I'm not giving them my PB licenses. :-)

John Spikowski

18-07-2014, 05:54

I'll be leaving the company I work for so I need to find another language translate my programs into because I'm not giving them my PB licenses.

I hear what you're saying about PB licenses becoming rare. Worse case they should be able to pick up a copy of PB on a torrent somewhere. :evil:

Gerald Sutherland

18-07-2014, 16:24

John, there seems to be life at PB again. :-) Ok, still too soon to tell but things are looking better from the outside. Besides, I love my PB compilers. Oh, and my looking for another language has nothing to do with any situations at PB ... I just need to find something that I can leave behind at my current company. And frankly, thinBasic is looking really good ... I just have to adapt to the syntax. :-)

The MSDN links above are good, but only cover Excel 2010 and 2013. Unfortunately, I am still working with 2003. So digging around in the Office 2003 install folders I found VBAXL10.CHM which is specific to my version, easier to use than the online versions and has a nice chart of Microsoft Excel Object Model. This has in turn made me think that I am trying to do things from the wrong level.

The example script is apparently using "Cells" from the worksheet level to insert the random numbers into a block of cells. I am thinking that I need to go one level deeper and select/ get handle to an individual cell (or group or cells) before trying to manipulate the cell properties. Anyway, that's my best guess at the moment and I'll tinker with that as I have time.

Thanks! ;-)
Jerry

John Spikowski

18-07-2014, 21:55

Gary Beene is not new life at PB. He doesn't work for PB and is just being a good guy helping out. There has been no word from the PowerBASIC camp in months. I think the reality is setting in that PB is in the same boat as FreeBASIC (author abandoned project) but without source.

mike lobanovsky

19-07-2014, 12:44

Hello Gerald and John,

In fact, msvbvm60.dll (VB6 runtime virtual machine) is a system32 library on all MS Windows platforms from XP upwards. This makes it possible to be used more or less directly rather than include COM as a native language layer (a.k.a. module, addon, extension, whatever).

The following is a quick and dirty implementation of a thin (125 VB6 code lines) wrapper around msvbvm60.dll usable in any procedural language without resorting to COM Variant structures. I'm not including the VB6 code here because it can't be compiled without a very special VB6 IDE setup that allows for native (not ActiveX!) DLL compilation.

The zip contains equivalent thinBasic and FBSL scripts as well as a precompiled FBSL executable. The VB6COM.dll module should be placed alongside the scripts and EXE for them to run successfully. Please note that the DLL is not complete; it implements only such functionality as is needed for these particular scripts and it shouldn't be used for further experimentation. The both scripts mimic thinBasic's classic Excel_RandomCellFill.tBasic sample but do not use TB's COM extension.

Due to the extreme simplicity of VB6COM.dll, it is functional under Win XP only.

John Spikowski

19-07-2014, 19:42

I did a quick search of the thinBasic forum for COM Excel examples and found this thread (http://www.thinbasic.com/community/showthread.php?9707-COM-with-Excel-I-will-fail-without-support&p=73227&viewfull=1#post73227). I'm not sure if anything has been done since in this area but maybe Petr or Eros will give us an update.

Funny. Who would of guessed Script BASIC would be the one ending up doing COM right. :)

Petr Schreiber

19-07-2014, 20:05

mike lobanovsky

19-07-2014, 20:51

Funny. Who would of guessed Script BASIC would be the one ending up doing COM right. :)

A very arrogant statement, don't you think? In my opinion, VB, VBA and VBS are the languages that have their COM access interfaces implemented in the most comfortable way. All other implementations (TB, FBSL and SB included - can't say anything about PB though) are nowhere near them as far as user friendliness and intuitive intelligibility go.

John Spikowski

19-07-2014, 21:43

I think Dave surprised all of us with his Script BASIC COM interface. Why should I not be proud of that effort? Gerald has confirmed that the SB example is more like what he is use to. I think most BASIC users could give a rats ass about low level COM and just want use it with as little pain as possible.

mike lobanovsky

20-07-2014, 01:03

John,

I'm very happy that you're so proud of Dave's achievements against the former total lack of that feature in the language. But if you have a closer look at FBSL and TB scripts that I've posted, you will see that both of them implement the exact same low-level syntax that Dave uses in his ActiveX plug-in for SB. And that was done in 45 minutes and in 125 lines of VB6 code and it is valid for two languages at once, and it's gonna be valid for SB too had you cared to model your own SB script after the ones that I provided.

Still all of it is very, very far away from the beauty of MS's simple dot notation used consistently throughout their COM aware products.

There's also another point to it. ActiveX is a technology that requires COM components to be registered in the HKEY_CLASSES_ROOT branch of Windows registry. Most of us can easily live with it because we are usually the only owners of our machinery and we all have Administrator (i.e. "root") privileges on our computers. But an ordinary user with limited access rights won't be able to install and use your smart ActiveX interface for SB because their UAC watchdog won't let them do it. VisualBasic is able to register its COM components in HKEY_CLASSES_ROOT only.

In contrast to this, my VB6COM.dll is a module written in VB6 and compiled in the VB6 IDE as a native dynamic load library. It doesn't require registration yet it interacts with msvbvm60.dll which is a 100% COM-based and must-register product. VB6 application context initialization is automatic under usual ActiveX conditions (provided your UAC allows it, hehe) but it becomes a PITA if you want to overcome this restriction. Yet my quick and dirty sample allows you to enjoy VB6 COM support in its glory in almost any language regardless of your root privileges. But again, only through this barbaric low-level syntactic nightmare.

My real message was "Look how easy COM functionality really is. Don't waste your time re-inventing the wheel. Concentrate yourselves on a decent COM access syntax instead."

On a side note, such a low level COM interface has been available and fully functional in FBSL for decades. But I was always feeling uneasy about its printf()-style syntax. It's alien to BASIC in all its implementations be it TB, or SB, or FBSL.

Or VB6COM.dll, for that matter.

John Spikowski

20-07-2014, 01:24

I'm very happy that you're so proud of Dave's achievements against the former total lack of that feature in the language.

That is untrue. Charles has had low level COM support in the Script BASIC DLLC extension module for awhile now. What Dave brings to the table is a way to use VB6 custom forms and components within SB. He has created a SB ActiveX scripting control that can be easy used with other COM supporting languages. The IDE/DEbugger was a long time coming requirement if SB was to be taken seriously. And to top it off he has demonstarted .NET component use with the COM extension module. Lets all give Dave a hand. :party: With the many contributions from the talented guys that helped Script BASIC get were it is today is a testimony that open source works.

mike lobanovsky

20-07-2014, 01:49

John,

When I'm saying "I'm happy", "I'm happy" is what I mean. There's no teasing in my words, neither evident nor latent.

Of what you said in your message, the only thing that's really appealing to me is the incremental debugger. This is what I should have in FBSL too but still don't.

As for "open source works", well, it does, sorta. Sometimes. But that's not its indispensable feature. Open source relies heavily on philanthropy of generous individuals, such as Charles or Dave, to name a few, and communal projects are almost always doomed to enthropy and oblivion in their absence.

But that's another story that doesn't relate well to the "thinBasic (the language)" or "Modules specific issues" boards of this forum. :)

Gerald Sutherland

20-07-2014, 05:03

Hello all,

Thank you very much! However, it was not my intention to start a competition or feud.

I find that when using Excel macros, the VBA structure is fairly clear. My problem I translating that into how TB would want to see it. I had the same problem with PB in the beginning ... just trying to figure out what was required. I have made progress with PB, but there are still a lot of things I haven't figured out yet. That is not a problem with either language ... it IS a problem with MY understanding of what is required.

It is true that SB looked more familiar to me than TB ... but I was comparing that to PB (and of course, even they are not the same). It's just that I'm having difficulty figuring out what TB expects to see as a "methodName", for example. I've tried a lot of the combinations that I have pulled from VBA, but I get a lot of error messages and a lot of them say that some TB equate is not available. Again, it is my lack of understanding that is at fault. I'll admit that a few examples would probably go a long way to enhancing my understanding (just have to get my foot in the door). ;-)

Petr, that sounds interesting, however I'm not sure that what little I have done so far would do anyone very much good ... but I would be interested in the technique you mentioned. One other thing though ... wouldn't it be against PB's policy to encapsulated their functionality into something for different language (perhaps with the exception of a DLL)? I'm not clear on this subject, and as I recall, TB had some issues with this in the past. Anyway, I would interested learning how this sort of thing can be done.

Tomorrow I'll see if I can get a couple of the things I have done working in SB so that I can demonstrate where I am headed with this. Is it okay for me to post either SB or PB code here for such an illustration? (wanted to ask first).

I'll try to look into that VB6 DLL as well, thanks Mike!

Thank you !
Jerry

John Spikowski

20-07-2014, 05:16

Gerald,

the reason for current level of COM implementation state is that it is not used much in ThinBASIC. You are the first one to ask for it in... well, years :)

If you are owner of PB compiler, I can guide you through process how to create wrapper module, which would allow OOP like usage in ThinBASIC.
Eros put lot of effort in OOP modules, and the way they are designed allows wrapping COM object to TB in straightforward manner.

If we could put together neatly working Excel interface module this way, whole community would benefit.

Are you familiar with Git?

Petr

With the seemingly demise of PowerBASIC, one would think Jose Roca would have shown some interest in making thinBasic more COM aware. I asked him to help out with the Script BASIC COM effort working with Dave but never got a reply. He seems partially active on his forum answering questions and updating his includes. There is a lot of idle talent that could be put to good use if the current mentality wasn't to program in BASIC you must create a variation of the language first. :aggressive:

John Spikowski

20-07-2014, 07:15

Tomorrow I'll see if I can get a couple of the things I have done working in SB so that I can demonstrate where I am headed with this. Is it okay for me to post either SB or PB code here for such an illustration? (wanted to ask first).

The SB COM ext. module need beta testing and feedback. I think everyone benefits when done in an open way. Everyone is welcome to use SB source code or embed the scripting API in open source projects or closed commercial products. I facilitate and manage the Script BASIC open source project and provide fee based commercial support and programming services.

Petr Schreiber

20-07-2014, 18:53

One other thing though ... wouldn't it be against PB's policy to encapsulated their functionality into something for different language (perhaps with the exception of a DLL)? I'm not clear on this subject, and as I recall, TB had some issues with this in the past. Anyway, I would interested learning how this sort of thing can be done.

Hi Gerald,

wrapping for example REGEX command from PowerBASIC would be violation of their policy (understandable), but wrapping usage of third party COM calls is perfectly fine.
I would not expose their COM engine, I would expose the Excel methods and properties in VBA like way.

Hi John,

one would think Jose Roca would have shown some interest in making thinBasic more COM aware

Watching the frequency of COM requests for ThinBASIC (one in multiple years), I think we have the answer to this question, don't we... ;)

Petr

Gerald Sutherland

20-07-2014, 22:11

From what I hear, Jose is very busy right now and probably doesn't have a lot of spare time anyway. :-) Besides, he has already done an incredible amount of things for the programming community.

Petr, you have made a good point. If I am the only person who has shown any interest in the COM module, then perhaps there is no point trying bring it to the forefront at this time. However, I am definitely interested in your process of turning whatever I may come up with (PB) into a form compatible with TB. If the offer is still open, please tell me how to go about this.

I tried to tinker with Script BASIC a bit, but didn't really get anywhere. That is what I would expect until I have more time to dig into how it works. :-)

I'm sure I still have my copy of VB6 around here somewhere, but I'll have to install it into a virtual machine since it won't run on 64-bit Windows.

Now getting back to comparing VBA, PB and TB:

To set the background color of a group of cells:

VBA:

Range("G1:G3").Select
With Selection.Interior
.ColorIndex = 38
.Pattern = xSolid
End With

This is a fairly straightforward translation to PB:

Let vrange = "G1:G3"
vtext = "38"
OBJECT LET oExcelWorkSheet.Range(vrange).Interior.ColorIndex = vText
vtext = "xlSolid"
OBJECT LET oExcelWorkSheet.Range(vrange).Interior.Pattern = Text

I just haven't caught on to how to translate the VBA to TB syntax (or SB, for that matter).

On the other hand, I have made a little progress with TB. Now I can read the row, column and content of the active cell. Baby steps. :-)

Thanks to everyone for their help up to this point. :-)

Jerry

John Spikowski

21-07-2014, 00:19

Thanks Jerry for the progress report.

I'm sure I still have my copy of VB6 around here somewhere, but I'll have to install it into a virtual machine since it won't run on 64-bit Windows.

I'm running VB6 (Visual Studio 6) on my copy of Windows 7 64 bit without issue. In fact I'm trying to get current Windows 7 theming to work with it.

VB6 example themed and runing on Windows 7 64 bit
http://www.vbforums.com/attachment.php?attachmentid=108113&d=1386626689

If there is any interest in Script BASIC, it would be great if you join the Script BASIC forum (send me an e-mail support@scriptbasic.org) and connect with Dave on any issues you have.

Gerald Sutherland

21-07-2014, 03:23

Perhaps VB6 just has a 16-bit installer (not uncommon). I did find an article that claimed it could be done ... but their instructions would not work for me (I am also running Windows 7 64-bit).

Anyway, wishing everyone goodnight.

Jerry

John Spikowski

21-07-2014, 03:33

VB6 is a 32 bit application and will be supported (not upgraded) by Microsoft for some time. (as long as 32 bit applications are supported)

See if the article helps.

How to install Visual Studio 6 on Windows 7 professional 64bit (http://www.codeproject.com/Tips/408806/How-to-install-Visual-Studio-on-Windows-profes)

I installed Visual Studio 6 Professional on Windows 7 Ultimate 32-bit and it is working perfectly.

There are several things to remember:

1) don't use the installation wizard. use the "setup.exe" and right click it and run it as administrator to start the installation.

2) the service packs are cumulative & that means the latest service pack has all the fixes of the previous ones combined. sp4, sp5 wouldn't install because of the "mdac 2.5 thingy" but sp6 went on without a problem. remember to use the right click context menu and run the sp6 service pack installer.exe as an administrator for the installation or it won't work.

3) once the program is installed go into the installed Visual Studio 6 folders in the root (C:\) directory & migrate shortcuts to the visual basic 6.exe, visual c++.exe, visual studio 6.exe, etc. to your desktop and use the right click and "run as an administrator" methodology on the shortcuts to start whatever Visual Studio 6 domain you wish to work with.

4) in short run all installer.exes and startup.exes as an administrator using the right click context menu or Windows 7 will block these programs from accessing the Windows 7 registry.

5) running programs in Windows 7 is all about permissions & some programs like Visual Studio 6 Professional need to be installed and run as system administrators.

Petr Schreiber

21-07-2014, 07:29

Hi Gerald,

thank you for the example usage, this gave me an idea ;) Stay tuned!

Petr

Gerald Sutherland

21-07-2014, 16:26

John,

I realize that VB6 itself is 32-bit, but I recalled that installing it in Windows 7 64-bit was problematic. I was only suggesting the possibility that the "installer" might have been 16-bit. I have seen this before with other software. However, checking my old notes, it appears the main problem was with the JAVA installation.

I will have to look at your article give it another try. :-)

Petr,

I am sure you are better equipped to figure this out than I am. ;-) It would be great if you found the entrance. ;-)

Thanks!
Jerry

Petr Schreiber

21-07-2014, 19:26

Hi Gerald,

if you like the VBS syntax for Excel, why don't you combine the power of both :)?

This example shows how simple is to pass variable values from ThinBASIC to VBS.

Uses "OS", "File"

' -- Some variable
Long valueOfA = 42

' -- VBA script stored as multiline string
String myScript = "
MsgBox("ThinBasic sent Me: " & $valueOfA)

Dim var

var = $valueOfA * 2

MsgBox("I doubled it: " & $valueOfA)
"

' -- VBA script executed
VBA_Execute(myScript, TRUE)

' -- Function for running VBA scripts, use TRUE as second parameter, if you want to expand $variables
Function VBA_Execute(script As String, Optional expand As Long)

String tempFile = APP_SourcePath + "temp.vbs"
If expand Then
script = Expand$(script)
End If

FILE_Save(tempFile, script)
OS_Shell("cmd /c cscript "+ tempFile, %OS_WNDSTYLE_MINIMIZED, %OS_SHELL_SYNC)
FILE_Kill(tempFile)

End Function

Petr

Gerald Sutherland

21-07-2014, 21:29

Thanks Petr,

Actually, I have never used VBS ...I have been using VBA (Excel macros) to find the details of what I need to translate into PB. I could be wrong, but I assume there are differences between VBS and VBA, but perhaps not enough to be a problem. I will certainly look into what you have posted. ;-)

Part of my problem is that I do not know what environment my projects will move to ... so I don't if they will have Excel (or which) version, which is one of the reasons I am avoiding VB.NET since you have to link in the appropriate object libraries and interop assemblies.

So, I'm trying to keep the projects tied to something (compiler interpreter) that I know can be provided to support those projects. So, I am trying to stay as generic as possible. And that is why thinBasic came to mind. I hope to know more about the new environment before the end, but you never know. ;-)

I'll look into your solution, but I haven't given up TB yet. ;-)

Jerry

Gerald Sutherland

21-07-2014, 21:29

Oops! I guess you meant VBA. ;-)

Gerald Sutherland

21-07-2014, 22:46

Petr,

Please correct me if I am wrong, but while having and Excel file open ... and then saving a short script to an external VBS file (from within TB), then shelling to that script file ... would have no affect within Excel itself since is an external script? It sounds like I'd have to write the entire application in VBS (open the Excel file, etc.) then shell to it.

As I mentioned, I have never used VBS and for the most part I have only used VBA as a reference for the functions I need to translate to PB.

So, I can see TB acting as a menu (or controller) for the scripts, but I guess I can't imagine being able to mix the TB COM statements with the VB scripts. Unless there is a way to run those scripts as MACROS within Excel itself.

Just my guess.
Jerry

John Spikowski

22-07-2014, 03:52

Jerry,

Can you post / attach some code (PowerBASIC maybe?) so we can try to help out? That's what makes the BASIC community interesting is there multiple ways to accomplish the same results. In the end everyone learns new tricks.

Gerald Sutherland

22-07-2014, 16:35

John,

I can probably cobble together a piece of code that demonstrates some of the PB COM syntax and results, but I can't post anything that exposes any of my project (proprietary to the company). Anyway, give me a little time and I'll try to illustrate a few Excel things.

Basically, the company I work for is being shut down by the corporate office. The system I use is not compatible with theirs (one reason for the re-write). I am trying to find out more details of what will be required ... and when. It seems I will have less time than I had expected. I talked to the head of our IT department and he suggested that I do the re-write in PB, comment the heck out of it, hand over the EXE and source code (not the compiler) ... then it's THEIR problem from that point on if they need something new. With that in mind, I'm thinking that pursuing TB COM much farther is not going to be a viable option. I'm sure it works just fine ... if you know how to use it, but I'm definitely not at that point. ;-)

So, give me a little time and I'll come up with some example PB code. :-)

Jerry

Gerald Sutherland

22-07-2014, 18:29

I have taken one of the PB Excel examples and modified it with a few items that are generic rather than directly associated with my project. In doing so, it occurred to me that apparently there are different interface with COM. These use the IDBIND (Dispatch) Interface. I wonder if TB uses something different and that might be why I am having so much trouble adapting to it? Just wondering. :-)

Anyway, I am attaching a Zip file of the source code, EXE and resultant XLS file.

If you decide to look further into this, please only do so for the benefit of TB and it's users, not on my account (as things are still swinging in the wind as far as what I may be required/allowed to do at my end).

Thank you!
Jerry

Petr Schreiber

22-07-2014, 19:50

Hi Gerald,

I am looking into alternative solution - it might end up as big win or... fail :D But the good news we will know this night.
I have 19:50 here, I will post something before local midnight.

Petr

John Spikowski

22-07-2014, 20:55

Go Petr!

Don't worry, you got Mike and I covering your back if what you have cooking doesn't pan out.

Gerald Sutherland

22-07-2014, 21:25

I seem to be having problems with the forum ... I sign in, then try to post and attach a file ... and it says that I am not logged in. :-(

John Spikowski

22-07-2014, 21:56

Stale cookies. :D

Gerald Sutherland

22-07-2014, 22:43

The pantry has been scoured and I'll try it again. If this works ... this zip has the include file as well ...

Looks good this time ... I think. :-)

Petr Schreiber

22-07-2014, 22:53

Hi Gerald,

I am close, but I would need further assistance from Eros on the SDK. This is just a basic demo:

uses "Excel"

Dim xApp As Excel_Application
xApp = New Excel_Application

MsgBox 0, "The version is: " + xApp.Version

MsgBox 0, "Make it visible"

xApp.Visible = 1

MsgBox 0, "Make it invisible"

xApp.Visible = 0

Long createdWorkbook = xApp.WorkBooks.Add()

MsgBox 0, "Object pointer to WorkBook is " + createdWorkbook

' -- Not sure how to return object reference,
' -- I can Get just object pointer, but ThinBASIC requires
' -- object to be initialized with NEW at the moment, grr

xApp.ActiveWindow.Close()
xApp.Quit()

What do you think about syntax? I consider it pretty cool :D

The SDK for ThinBASIC is pretty amazing, I could get the compound object syntax working, kind of. But the functionality I would need from Eros would be at least the ability to:

assign reference to object variable directly (currently only possible with NEW)

you can see I return object pointer to Workbook already

Good night,
Petr

Gerald Sutherland

22-07-2014, 23:45

Petr,

That looks interesting ... and a lot more friendly from my point of view. :-)

However, you are working at a level I'll never be able to understand. :-)
And again ... don't spend your entire life trying to fix something up for me. :-)

Just a little addition for the sample I posted ... to freeze panes:

DIM vTrue AS VARIANT

' set freezepanes at row 2
vTrue = %True
vRange = "A2"
OBJECT CALL oExcelWorkSheet.Range(vRange).SELECT
OBJECT LET oExcelApp.ActiveWindow.FreezePanes = vTrue

Jerry

mike lobanovsky

23-07-2014, 05:46

What do you think about syntax? I consider it pretty cool :D ... I could get the compound object syntax working

Yes Petr,

That's pretty cool indeed. In fact that's exactly what I was hinting at in my messages. Are you reading the TLB's?

Gerald Sutherland

23-07-2014, 16:21

By the way, sorry about the haphazard Excel file in my sample. I was just trying to demo the PB Excel commands and was throwing them into almost any empty cell ... no plan for what the total outcome looked like. :-)

Jerry

John Spikowski

23-07-2014, 17:47

Here is the Script BASIC submission to the Jerry Excel Code Challenge. I didn't complete all the cell manipulations or add in the error notification Jerry had in his PowerBASIC Excel example but it will give you an idea how SB COM works.

http://files.allbasic.info/ScriptBasic/SBCOM/sbexcel.png

import com.inc

filename = "c:\\SB22\\sbcom\\excel\\warehouse.xls"
oExcelApp = CreateObject("Excel.Application")
oWorkBook = CallByName(oExcelApp, "Workbooks", vbGet)
oExcelWorkbook = CallByName(oWorkBook, "Add")
oExcelSheet = CallByName(oExcelWorkbook, "Worksheets", vbGet, 1)
oRange = CallByName(oExcelSheet, "Range", vbGet, "G3")
CallByName(oRange, "Value", vbLet, "123")
ReleaseObject(oRange)
oRange = CallByName(oExcelSheet, "Range", vbGet, "B1:B5")
oInterior = CallByName(oRange, "Interior", vbGet)
CallByName(oInterior, "ColorIndex", vbLet, "38")
CallByName(oInterior, "Pattern", vbLet, "xlSolid")
ReleaseObject(oRange)
ReleaseObject(oInterior)
CallByName(oExcelWorkbook, "SaveAs", vbMethod, filename)
CallByName(oExcelWorkbook, "Close")
CallByName(oExcelApp, "Quit")
ReleaseObject(oExcelSheet)
ReleaseObject(oExcelWorkbook)
ReleaseObject(oWorkBook)
ReleaseObject(oExcelApp)

Gerald Sutherland

23-07-2014, 18:37

Thanks John,

That's what I needed ... an example that did more than just open a file and drop some values into it. ;-) The cell manipulation is important to me because when I do my data processing or SQL stuff ... I color flag the items that need human attention because they appear to be in error ... and the type of error are coded in different colors. And so forth. ;-)

If there were examples more like that for the TB COM, I'd probably do a lot better. :-)

Thanks again!
Jerry

Gerald Sutherland

23-07-2014, 23:02

I think I'm getting closer to where my TB COM problem is. Obviously, it's that I don't understand the TB syntax ... partly at least because there are so few references. However ...

The example script fills random cells with this routine (excerpt)":

vParam(1) = nRow
vParam(2) = nCol
vParam(3) = (some number)
COM_SETPROPERTY (pXlSheet, "Cells", 3, vParam)

Okay, this sets the value of the cell at (nRow, nCol) with the value supplied in vParam(3).

I can set up something similar by using

vParam(1) = "D5:D8"
vParam(2) = "test"
COM_SETPROPERTY (pXlSheet, "Range", 2, Vparam)

This places "test" in cells D5, D6, D7 and D8.

So far, so good. But what I am missing is that it seems to default to the ".value" parameter. How do I get it to do anything else?

I have tried working ".Select", etc. into the second parameter of COM_SETPROPERTY and I always get an error. The same holds true is I try to put SELECT in the vParam array.

So, I guess my real question is how to change this to SELECT or ACTIVATE a cell. So far, I can only change the value (any cell) or read the value from Cell(1,1) (since I haven't figured out how to select another cell). :-)

Seems like it should be easy, but I'm just not getting it ... yet. :-)

Back to my cave. :-)
Jerry

John Spikowski

24-07-2014, 19:20

Here is the final submission to Jerry's code challenge. I was unable to show applying format to the sheet as Jerry's code assumes the sheet existed and opened to have an existing activesheet. This example is creating a new worksheet.

http://files.allbasic.info/ScriptBasic/SBCOM/sbcom_excel_final.png

' Jerry's Excel Example - Script BASIC COM

IMPORT com.inc

CONST XlHAlign_xlHAlignCenter = -4108
CONST XlBorderWeight_xlMedium = -4138

' create Excel worksheet
filename = "c:\\SB22\\sbcom\\excel\\warehouse.xls"
oExcelApp = CreateObject("Excel.Application")
oWorkBook = CallByName(oExcelApp, "Workbooks", vbGet)
oExcelWorkbook = CallByName(oWorkBook, "Add")
oExcelSheet = CallByName(oExcelWorkbook, "Worksheets", vbGet, 1)

' change interior color of cells "B1:B5" rose (solid)
oRange = CallByName(oExcelSheet, "Range", vbGet, "B1:B5")
oInterior = CallByName(oRange, "Interior", vbGet)
CallByName oInterior, "ColorIndex", vbLet, "38"
CallByName oInterior, "Pattern", vbLet, "xlSolid"
ReleaseObject oRange
ReleaseObject oInterior

' put data in cell G3
oRange = CallByName(oExcelSheet, "Range", vbGet, "G3")
CallByName oRange, "Value", vbLet, "123"

' center the data in cell G3
CallByName oRange, "HorizontalAlignment", vbLet, XlHAlign_xlHAlignCenter

' Set the font attribute to BOLD in G3
oFont = CallByName(oRange, "Font", vbGet)
CallByName oFont, "Bold", vbLet, TRUE

' Change font in G3 to purple
CallByName oFont, "Color", vbLet, 0xFF00FF

' Change font in G3 to 20 pt Courier New
CallByName oFont, "Name", vbLet, "Courier New"
CallByName oFont, "Size", vbLet, 20

' Place BOLD border around cell G3
CallByName oRange, "BorderAround", vbMethod, 1, XlBorderWeight_xlMedium, 3
ReleaseObject oFont
ReleaseObject oRange

' Add long string to cell E2, short number to C1
oRange = CallByName(oExcelSheet, "Range", vbGet, "C1")
CallByName oRange, "Value", vbLet, 2
ReleaseObject oRange
oRange = CallByName(oExcelSheet, "Range", vbGet, "E2")
CallByName oRange, "Value", vbLet, "Testing long string"
ReleaseObject oRange

' Save worksheet and release Excel worksheet memory
CallByName oExcelWorkbook, "SaveAs", vbMethod, filename
CallByName oExcelWorkbook, "Close"
CallByName oExcelApp, "Quit"
ReleaseObject oExcelSheet
ReleaseObject oExcelWorkbook
ReleaseObject oWorkBook
ReleaseObject oExcelApp

Petr Schreiber

24-07-2014, 21:10

Hi Gerald,

the change I need Eros to do would be beneficial not just for this module, but for many others - road worth a little dev pain :D
The syntax would be more 21st century...

I will contact Eros and talk about it to him, stay tuned (but of course, feel free experimenting, if you are in time pressure).

Petr

EDIT: Just wrote to Eros, will wait for his feedback. Imagine it - garbage collected, intuitive dotted syntax...

mike lobanovsky

24-07-2014, 21:28

Petr,

Dim xApp As Excel_Application

Is your new COM methodology trying to read TLB's (type libraries) or does it use simple include files to create statements like that?

Petr Schreiber

24-07-2014, 21:33

Hi Mike,

it is much simpler. If Eros will have time to develop what I need, I will make the module most probably available at GitHub.
It is developed in PB for Windows 10.04 as of now.

Petr

Gerald Sutherland

24-07-2014, 21:38

John,

That looks pretty good. ;-)

I wanted to work on that and see what I could do ... but ... I can't get Script Basic to install without error yet (even using "Run as Administrator"). Apparently, it is having problems writing to the registry. This might be due to the strict security settings at work, so I'll have to try it at home.

Petr,

I think anything that would enhance thinBasic is a good idea. ;-) And I would look forward to a different approach. As it is, I've been given other assignments so I'll kind of have to set aside this endeavor for now (although I may still tinker with it as I have time). I am not in a rush ... just doing a little exploration at this time.

To further illustrate what I think I am seeing ... I'm going to compare an Excel command structure with the equivalent TB command:

' ---------------------------------------------------
' Excel:
' Worksheets(1).Cells(1, 1).Value = 24
' ---------------------------------------------------
vParam(1) = 1 'row
vParam(2) = 1 ' col
vParam(3) = 24 ' cell value
COM_SETPROPERTY(pXlSheet, "Cells", 3, vParam)

In both I see the worksheet, the cell row and column and the cell value to set. What appears to be missing from the TB command is the ".Value" parameter. It seems that ".Value" is the default and I'm not seeing a way to override it (yet, anyway). So, my guess is that the COM module may have been originally created for filling the cells and not for more fancier operations. But as I said, that's just a guess. The designer might be the only person who knows for sure.

I will be keeping my eye on this, but for now I need to take care of a few other things before I can free up enough time to hammer on it again. ;-)

Jerry

mike lobanovsky

24-07-2014, 21:39

Thanks for the info Petr,

I hope I'll be able to grasp the concept though I'm not a PB user. TurboBasic was the last BZ product I laid my hands on. :)

Petr Schreiber

24-07-2014, 21:52

Gerald,

maybe you should retrieve reference to Range itself and then you can cast something on it - like John shows in his code.
So instead of SetProperty you get Range first (that is how it is in the interface of Excel anyway, RANGE object returned) and then call method on it.

I am not on PC with Excel now, so cannot try, but could work. Something like retrieving reference to Workbook/Worksheets...

Petr

Petr Schreiber

24-07-2014, 22:00

Hi Mike,

the concept, if interesting at all, would be more from thinBasic module creation stand point. As for COM, just a little magic possible with PB.

Petr

John Spikowski

24-07-2014, 23:37

As for COM, just a little magic possible with PB.

That well is known to have run dry. You should look at alternatives to expand the functionality of TB. Just saying ...

John Spikowski

24-07-2014, 23:45

That looks pretty good. ;-)

I wanted to work on that and see what I could do ... but ... I can't get Script Basic to install without error yet (even using "Run as Administrator"). Apparently, it is having problems writing to the registry. This might be due to the strict security settings at work, so I'll have to try it at home.

You should probbably join the Script BASIC forum (send an e-mail to support@scriptbasic.org) where Dave would be more helpful on a real world effort with SB COM. I'm in learning mode as well.

Gerald Sutherland

24-07-2014, 23:53

Petr,

You're probably right. I have tried that approach and all I get is errors, so I'm definitely not doing it right. ;-) I am a relative newcomer to Excel COM and I guess I'm a bit spoiled by the fact that I could actually do something like this with PB (unfortunately, perhaps without understanding the details of HOW it works). All I need to do is get a small success or two in TB and then maybe I'll see the pattern I need to follow. :-)

John,

I will probably sign up this weekend. I'm just hoping to get SB installed and running before I jump in the pool. :-)

Thanks!
Jerry

John Spikowski

25-07-2014, 00:27

I will probably sign up this weekend. I'm just hoping to get SB installed and running before I jump in the pool. :-)

If you need a hand either e-mail me or catch me on the #AllBASIC IRC.

Petr Schreiber

31-07-2014, 22:07

Hi!,

just a little update - Eros is making impressive progress on the Excel module. He took my base and did his legendary magic again to make it even cooler:

Uses "Excel"

Function TBMain() As Long
Dim xApp As Excel_Application
Dim xWorkBook As Excel_Workbook
Dim xSheet As Excel_Worksheet
Dim x, y As Long

xApp = New Excel_Application

MsgBox 0, "Excel version is: " + xApp.Version

xApp.Visible = 1

'---Pass Excel Instance to create a Workbook
xWorkBook = New Excel_WorkBook(xApp)

'---Pass Workbook instance to create a sheet
xSheet = New Excel_Worksheet(xWorkBook)

'---Write some text
For y = 1 To 50
For x = 1 To 5
xSheet.Cells(y, x) = "Cell " & Chr$(x + 64) & Format$(y)
Next
xSheet.Cells(Y, X) = y + y / 10
Next

MsgBox 0, "OK, test done. thinBasic will now close."

'xApp.ActiveWindow.Close()
xApp.Quit
End Function

You can download updated module in the attachement, stay tuned...

Petr

John Spikowski

01-08-2014, 02:21

Looks great Petr!

Nice to see Eros back in the saddle again. :comp4:

John Spikowski

01-08-2014, 08:42

Would have been nice to see the source to thinBasic_Excel.dll. :neutral:

Gerald Sutherland

01-08-2014, 16:04

Looks interesting, Petr. :-) I'll be curious to see where you guys go with this. :-)

Jerry

jack

01-08-2014, 18:40

interesting indeed, very easy syntax.

Petr Schreiber

02-08-2014, 15:23

Eros is very generous to share the code of Excel module at GitHub... This one, and maybe more will follow, who knows ;)
https://github.com/ErosOlmi/ThinBASIC_On_GitHub/tree/master/Lib/thinBasic_Excel

Petr

John Spikowski

02-08-2014, 19:16

Thanks Erso for posting source to your COM / Excel effort. It's a good PB COM tutorial in itself. What version of PB are you using?

Petr Schreiber

02-08-2014, 19:22

Hi John,

as you can see from the compiler log file, it is PowerBASIC for Windows, v10:
https://github.com/ErosOlmi/ThinBASIC_On_GitHub/blob/master/Lib/thinBasic_Excel/thinBasic_Excel.log

I use the same compiler for the StringBuilder.

Petr

ErosOlmi

06-08-2014, 09:39

Few steps further into a usable library.

In order to test below example you need to install thinBasic 1.9.13.0 (see top of the page notice about how to download it)

Attached to this post a script example and the new library.
Fo those willing to see them, library sources can be found at: https://github.com/ErosOlmi/ThinBASIC_On_GitHub

To use the library you have 2 possible ways:

put thinBasic_Excel.DLL into \thinBasic\Mod\ directory (standard thinBasic module directory)
keep thinBasic_Excel.DLL in the same directory of the test script (ThinBASIC Core Engine will always try to load a module first looking into executed script directory. See doc at http://www.thinbasic.com/public/products/thinBasic/help/html/index.html?uses.htm )

Here the source code of the example you will find into the attached ZIP file:

#MINVERSION 1.9.13.0

Uses "Excel"

Function TBMain() As Long
Dim xApp As Excel_Application
Dim xWorkBook1 As Excel_Workbook
Dim xWorkBook2 As Excel_Workbook
Dim xSheet1 As Excel_Worksheet
Dim xSheet2 As Excel_Worksheet
Dim x, y As Long

'---Create a new Excel Application COM Server instance
xApp = New Excel_Application

'---Get back Excel Version
MsgBox 0, "Excel version is: " + xApp.Version

'---Set Visible to TRUE so we can see what's going on
xApp.Visible = %TRUE

'---Create 2 Excel workbooks
xWorkBook1 = xApp.WorkBooks.Add
xWorkBook2 = xApp.WorkBooks.Add
MsgBox 0, "Number of open workbooks: " & xApp.WorkBooks.Count

'---New sheets objects can be created in 2 ways
'---Here we just get the activesheet
xSheet1 = xWorkBook1.Activesheet
'---And here we create a new sheet using Add method
xSheet2 = xWorkBook1.Worksheets.Add

'---We want activate "Sheet1"
'---To be able to select a sheet in a workbook, workbook must be the activa one
xWorkbook1.Activate
xWorkbook1.Worksheets(2).Select
'---It is possible to pass also sheet name instead of sheet index
'xWorkbook1.Worksheets("Sheet1").Select
'---Or it is possible to use sheet directly (if instantiated) using sheet.activate
'xsheet1.Activate

'---Change Sheet name (here Set and Get of a Sheet Name)
xSheet1.Name = "thinBasic Test 1"
xSheet2.Name = "thinBasic Test 2"
MsgBox 0, _
"In Workbook1 there are " & xWorkBook1.Worksheets.Count & " sheets" & $CRLF &
"Worksheet name of sheet 1 is: " & xSheet1.Name & $CRLF &
"Worksheet name of sheet 2 is: " & xSheet2.Name & $CRLF &
""

'---Write some text into Excel sheet
MsgBox 0, "Now we will fill some cells"
For y = 1 To 50
For x = 1 To 5
xSheet1.Cells(y, x).Value = "Cell " & Chr$(x + 64) & Format$(y)
xSheet2.Cells(y, x).Value = "Cell " & Chr$(x + 64) & Format$(y)
Next
x = 6
xSheet1.Cells(Y, X) = y + y / 10
xSheet2.Cells(Y, X) = y + y / 10
x = 7
xSheet1.Cells(Y, X) = Format$(Rnd(-1000,1000), "$#,.00")
xSheet2.Cells(Y, X) = Format$(Rnd(-1000,1000), "$#,.00")
Next

'---Read some text back
MsgBox 0, _
"Reading back Cell 2,2 of Sheet 1 = " & xSheet1.Cells(2,2).Value & $CRLF &
"Reading back Cell 2,2 of Sheet 2 = " & xSheet1.Cells(2,2).Value & $CRLF &
""

'xSheet1.PrintPreview

'---Tells Excel application to not show alerts (in this case alert about file already exists before saving)
xApp.DisplayAlerts = %FALSE
'---Save file name
xWorkBook1.SaveAs(APP_ScriptFullName & "_1.xlsx")
xWorkBook2.SaveAs(APP_ScriptFullName & "_2.xlsx")
'---Reset DisplayAlerts
xApp.DisplayAlerts = %TRUE

'---Close Excel Application
xApp.Quit

MsgBox 0, "OK, test done. thinBasic will now close."

End Function

John Spikowski

07-08-2014, 05:24

Looking good Eros!

Are you able to address cells with the range object yet? (Jerry's requirement)

ErosOlmi

07-08-2014, 08:53

Not yet but will be the next class I will add to ThinBASIC Excel module

Range dispatch interface is one of the most complex interfaces exposed by Excel automation but now that I've already developed something and studied Excel COM Server a bit, I'm confident it will not be that complex. I've already seen how to implement it. And more: this module will serve also to me at work in order to substitute old Biff module

Ciao
Eros

John Spikowski

07-08-2014, 10:51

This doesn't need to be a mystery. Dave's SB COM Windows C source is free to use without restriction. No use recreating the wheel.

ErosOlmi

09-08-2014, 10:13

OK, last update before leaving for 2 weeks holiday.

I've added Excel_Range class and re-factored some part of the source code. I will implement more Excel_Range with cells formatting by September.

Full sources can be found at GitHub at https://github.com/ErosOlmi/ThinBASIC_On_GitHub
If I will have time I will start writing help in next days, but not sure (family! :) ).

Attached the ThinBASIC_Excel.DLL library. Just put the library and the example script into a directory to test it.
Here few lines of a script in order to show how to use.

Have fun.
Eros

#MINVERSION 1.9.13.0

Uses "Excel"
Uses "File"
Uses "Console"

Randomize Timer

'--------------------------------------------------------------------
Function Out(ByVal sMessage As String, Optional lLevel As Long, ByVal Wait As Long)
'--------------------------------------------------------------------
' Output some message
'--------------------------------------------------------------------
PrintL Time$, String$(lLevel * 2, " ") & sMessage

If Wait = %TRUE Then
PrintL "---Press a key to continue---"
WaitKey
End If

End Function

'--------------------------------------------------------------------
Function TBMain() As Long
'--------------------------------------------------------------------
Dim sXlsFileName As String
Dim xApp As Excel_Application
Dim xWorkBook As Excel_Workbook
Dim xSheet As Excel_Worksheet
Dim x, y As Long

sXlsFileName = APP_ScriptFullName & ".xlsx"

'---Create a new Excel Application COM Server instance
xApp = New Excel_Application

'---Set Visible to TRUE so we can see what's going on
xApp.Visible = %TRUE

'---Create Excel workbook
If FILE_Exists(sXlsFileName) Then
Out("Opening file: " & sXlsFileName)
xWorkBook = xApp.WorkBooks.Open(sXlsFileName)
Else
Out("Creating a new empty workbook")
xWorkBook = xApp.WorkBooks.Add
End If

Out("Workbook name is: " & xWorkBook.Name)
Out("Workbook full name is: " & xWorkBook.FullName)

'---Get the activesheet
xSheet = xApp.Activesheet
Out("Active sheet name is actually: " & xSheet.Name)

'---Change Sheet name (here Set and Get of a Sheet Name)
xSheet.Name = "thinBasic_Test_" & Replace$(Timer, ".", "_")
Out("Changed sheet name to: " & xSheet.Name)

'---Write some text into Excel sheet
Out("Start filling some cells. Timer start: " & Timer)
For y = 1 To 100
For x = 1 To 5
xSheet.Cells(y, x).Value = "Cell " & Chr$(x + Asc("A") - 1) & Format$(y)
Next
x = 6
xSheet.Cells(Y, X) = y + y / Rnd(1, 10)
x = 7
xSheet.Cells(Y, X) = Format$(Rnd(-1000, 1000), "$#,.00")
Next
Out("End filling some cells. Timer End: " & Timer)

'---Working with sheet.range
Dim sRange As String
Dim xRange As Excel_Range
sRange = "A1:B3;D1:F10"

out("Working with range: " & sRange)
xRange = xSheet.Range(sRange)
xRange.Select
xRange.Value = "X"
Sleep 1000
out("Range address " & xRange.Address)
xRange.Clear

out("Clearing A:Z")
xRange = xSheet.Range("A:Z")
xRange.Clear
xSheet.Range("A1").Select

out("Making some easy calculation")
xSheet.Cells(1, 1) = "Few random number"
For y = 1 To 20
xSheet.Cells(Y, 2) = Rnd(10, 2000)
Next
xSheet.Cells(22, 1) = "Total"
xRange = xSheet.Range("B22")
xRange.Formula = "=sum(B1:B20)"

xRange = xSheet.Range("D1:D20")
xRange.Formula = "=Sin(B1)"
xRange = xSheet.Range("D22")
xRange.Formula = "=sum(D1:D20)"
Sleep 3000

'---Tells Excel application to not show alerts (in this case alert about file already exists before saving)
out("Checking if file must be saved")
If xWorkBook.Saved = %FALSE Then
out("Tells Excel not to bother with alerts")
xApp.DisplayAlerts = %FALSE
If FILE_Exists(sXlsFileName) Then
out("File already exists so just save")
xWorkBook.Save
Else
out("File does not exists so saving file to: " & sXlsFileName)
xWorkBook.SaveAs(sXlsFileName)
End If
out("Restoring standard alerts")
xApp.DisplayAlerts = %TRUE
End If

out("Closing active window")
xApp.ActiveWindow.Close

out("Quitting Excel")
xApp.Quit

out("OK, test done. thinBasic will now close.", 0, %TRUE)

End Function

John Spikowski

18-08-2014, 20:54

Eros,

Your new Excel COM interface looks great!

Is the plan to use this as a way to forge a path towards generic COM in the language?

ErosOlmi

18-08-2014, 21:32

Hi John,

no, for the moment this is just the starting of a new thinBasic module dedicated to Excel automation.
I will try to add as many features as possible.

Here attached a new update.
I've additionally implemented Excel_Range, now you can change background color in different ways.
I will add more options and full help when I will be back from holidays.

Full sources can be found at GitHub at https://github.com/ErosOlmi/ThinBASIC_On_GitHub

Attached the ThinBASIC_Excel.DLL library. Just put the library and the example script into a directory to test it.
Here few lines of a script in order to show how to use.

Ciao
Eros

#MINVERSION 1.9.13.0

Uses "Excel"
Uses "File"
Uses "Console"

Randomize Timer

'--------------------------------------------------------------------
Function Out(ByVal sMessage As String, Optional lLevel As Long, ByVal Wait As Long)
'--------------------------------------------------------------------
' Output some message
'--------------------------------------------------------------------
PrintL Time$, String$(lLevel * 2, " ") & sMessage

If Wait = %TRUE Then
PrintL "---Press a key to continue---"
WaitKey
End If

End Function

'--------------------------------------------------------------------
Function TBMain() As Long
'--------------------------------------------------------------------
Dim sXlsFileName As String
Dim xApp As Excel_Application
Dim xWorkBook As Excel_Workbook
Dim xSheet As Excel_Worksheet
Dim xRange As Excel_Range
Dim x, y As Long

sXlsFileName = APP_ScriptFullName & ".xlsx"

'---Create a new Excel Application COM Server instance
xApp = New Excel_Application

'---Set Visible to TRUE so we can see what's going on
xApp.Visible = %TRUE

'---Create Excel workbook
If FILE_Exists(sXlsFileName) Then
Out("Opening file: " & sXlsFileName)
xWorkBook = xApp.WorkBooks.Open(sXlsFileName)
Else
Out("Creating a new empty workbook")
xWorkBook = xApp.WorkBooks.Add
End If

Out("Workbook name is: " & xWorkBook.Name)
Out("Workbook full name is: " & xWorkBook.FullName)

'---Get the activesheet
xSheet = xApp.Activesheet
Out("Active sheet name is actually: " & xSheet.Name)

'---Change Sheet name (here Set and Get of a Sheet Name)
xSheet.Name = "thinBasic_Test_" & Replace$(Timer, ".", "_")
Out("Changed sheet name to: " & xSheet.Name)

'---Write some text into Excel sheet
Out("Start filling some cells. Timer start: " & Timer)
For y = 1 To 100
For x = 1 To 5
xSheet.Cells(y, x).Value = "Cell " & Chr$(x + Asc("A") - 1) & Format$(y)
Next
x = 6
xSheet.Cells(Y, X).Value = y + y / Rnd(1, 10)
x = 7
xSheet.Cells(Y, X).Value = Format$(Rnd(-1000, 1000), "$#,.00")
Next
Out("End filling some cells. Timer End: " & Timer)

'---Working with sheet.range
Dim sRange As String
sRange = "A1:B3;D1:F10"

out("Working with range: " & sRange)
xRange = xSheet.Range(sRange)
xRange.Select
xRange.Value = "X"
Sleep 1000
out("Range address " & xRange.Address)
xRange.Clear

out("Clearing A:Z")
xRange = xSheet.Range("A:Z")
xRange.Clear
xSheet.Range("A1").Select

out("Making some easy calculation")
xSheet.Cells(1, 1) = "Few random numbers"
For y = 1 To 20
xSheet.Cells(Y, 2) = Rnd(10, 2000)
Next
xSheet.Cells(22, 1) = "Total"
xRange = xSheet.Range("B22")
xRange.Formula = "=Sum(B1:B20)"

xRange = xSheet.Range("D1:D20")
xRange.Formula = "=Sin(B1)"
xRange.HorizontalAlignment = %XlHAlign_xlHAlignCenter
xRange.ColumnWidth = 25

xRange = xSheet.Range("D22")
xRange.Formula = "=Sum(D1:D20)"

out("Change some cells color background")
xSheet.Cells(1, 8).Value = "Changing some randomized color"
Randomize Timer

Long ColorIndex
For y = 2 To 100
For x = 1 To 10
sRange = Chr$(Asc("G") + x) & y & ":" & Chr$(Asc("G") + x) & y
xRange = xSheet.Range(sRange)

'---Using ColorIndex property
'xRange.Interior.ColorIndex = ColorIndex
'xRange.Value = ColorIndex
'Incr ColorIndex

'---Using color property
xRange.Interior.Color = Rgb(Rnd(0, 255), Rnd(0, 255), Rnd(0, 255))
'xRange.Interior.Color = Rgb(x * Y, x * y, Rnd(0, 255))
Next
Next

out("Sleeping some time in order to let see what's on video")
Sleep 5000

out("Clear color example area ...")
xRange = xSheet.Range("H:Z")
xRange.Clear

'---Tells Excel application to not show alerts (in this case alert about file already exists before saving)
out("Checking if file must be saved")
If xWorkBook.Saved = %FALSE Then
out("Tells Excel not to bother with alerts")
xApp.DisplayAlerts = %FALSE
If FILE_Exists(sXlsFileName) Then
out("File already exists so just save")
xWorkBook.Save
Else
out("File does not exists so saving file to: " & sXlsFileName)
xWorkBook.SaveAs(sXlsFileName)
End If
out("Restoring standard alerts")
xApp.DisplayAlerts = %TRUE
End If

out("Closing active window")
xApp.ActiveWindow.Close

out("Quitting Excel")
xApp.Quit

out("OK, test done. thinBasic will now close.", 0, %TRUE)

End Function

ErosOlmi

19-08-2014, 21:55

Here attached a new update.
Excel_Range has now Font property that can be used to get/set font properties of cells in Range.
See example below.

Full sources can be found at GitHub at https://github.com/ErosOlmi/ThinBASIC_On_GitHub

Attached the ThinBASIC_Excel.DLL library. Just put the library and the example script into a directory to test it.
Here few lines of a script in order to show how to use.

Ciao
Eros

#MINVERSION 1.9.13.0

Uses "Excel"
Uses "File"
Uses "Console"

Randomize Timer

'--------------------------------------------------------------------
Function Out(ByVal sMessage As String, Optional lLevel As Long, ByVal Wait As Long)
'--------------------------------------------------------------------
' Output some message
'--------------------------------------------------------------------
PrintL Time$, String$(lLevel * 2, " ") & sMessage

If Wait = %TRUE Then
PrintL "---Press a key to continue---"
WaitKey
End If

End Function

'--------------------------------------------------------------------
Function TBMain() As Long
'--------------------------------------------------------------------
Dim sXlsFileName As String
Dim xApp As Excel_Application
Dim xWorkBook As Excel_Workbook
Dim xSheet As Excel_Worksheet
Dim xRange As Excel_Range
Dim x, y As Long

sXlsFileName = APP_ScriptFullName & ".xlsx"

'---Create a new Excel Application COM Server instance
xApp = New Excel_Application

'---Set Visible to TRUE so we can see what's going on
xApp.Visible = %TRUE

'---Create Excel workbook
If FILE_Exists(sXlsFileName) Then
Out("Opening file: " & sXlsFileName)
xWorkBook = xApp.WorkBooks.Open(sXlsFileName)
Else
Out("Creating a new empty workbook")
xWorkBook = xApp.WorkBooks.Add
End If

Out("Workbook name is: " & xWorkBook.Name)
Out("Workbook full name is: " & xWorkBook.FullName)

'---Get the activesheet
xSheet = xApp.Activesheet
Out("Active sheet name is actually: " & xSheet.Name)

'---Change Sheet name (here Set and Get of a Sheet Name)
xSheet.Name = "thinBasic_Test_" & Replace$(Timer, ".", "_")
Out("Changed sheet name to: " & xSheet.Name)

'---Write some text into Excel sheet
Out("Start filling some cells. Timer start: " & Timer)
For y = 1 To 100
For x = 1 To 5
xSheet.Cells(y, x).Value = "Cell " & Chr$(x + Asc("A") - 1) & Format$(y)
Next
x = 6
xSheet.Cells(Y, X).Value = y + y / Rnd(1, 10)
x = 7
xSheet.Cells(Y, X).Value = Format$(Rnd(-1000, 1000), "$#,.00")
Next
Out("End filling some cells. Timer End: " & Timer)

'---Working with sheet.range
Dim sRange As String
sRange = "A1:B3;D1:F10"

out("Working with range: " & sRange)
xRange = xSheet.Range(sRange)
xRange.Select
xRange.Value = "X"
Sleep 1000
out("Range address " & xRange.Address)
xRange.Clear

out("Clearing A:Z")
xRange = xSheet.Range("A:Z")
xRange.Clear
xSheet.Range("A1").Select

out("Making some easy calculation")
xSheet.Cells(1, 1) = "Few random numbers"
For y = 1 To 20
xSheet.Cells(Y, 2) = Rnd(10, 2000)
Next
xSheet.Cells(22, 1) = "Total"
xRange = xSheet.Range("B22")
xRange.Formula = "=Sum(B1:B20)"

xRange = xSheet.Range("D1:D20")
xRange.Formula = "=Sin(B1)"
xRange.HorizontalAlignment = %XlHAlign_xlHAlignCenter
xRange.ColumnWidth = 25

xRange = xSheet.Range("D22")
xRange.Formula = "=Sum(D1:D20)"

'---Change some font properties
xRange.Font.Name = "Arial"
xRange.Font.Size = 14
xRange.Font.Bold = %TRUE
xRange.Font.Italic = %TRUE
xRange.Font.Underline = %XlUnderlineStyle_xlUnderlineStyleSingle
xRange.Font.Color = Rgb(255, 0, 0)
xRange.Interior.Color = Rgb(255, 255, 0)
Out("Current font style is: " & xRange.Font.FontStyle)

out("Change some cells color background")
xSheet.Cells(1, 8).Value = "Changing some randomized color"
Randomize Timer

Long ColorIndex
For y = 2 To 100
For x = 1 To 10
sRange = Chr$(Asc("G") + x) & y & ":" & Chr$(Asc("G") + x) & y
xRange = xSheet.Range(sRange)

'---Using ColorIndex property
'xRange.Interior.ColorIndex = ColorIndex
'xRange.Value = ColorIndex
'Incr ColorIndex

'---Using color property
xRange.Interior.Color = Rgb(Rnd(0, 255), Rnd(0, 255), Rnd(0, 255))
'xRange.Interior.Color = Rgb(x * Y, x * y, Rnd(0, 255))
Next
Next

out("Sleeping some time in order to let see what's on video")
Sleep 5000

out("Clear color example area ...")
xRange = xSheet.Range("H:Z")
xRange.Clear

'---Tells Excel application to not show alerts (in this case alert about file already exists before saving)
out("Checking if file must be saved")
If xWorkBook.Saved = %FALSE Then
out("Tells Excel not to bother with alerts")
xApp.DisplayAlerts = %FALSE
If FILE_Exists(sXlsFileName) Then
out("File already exists so just save")
xWorkBook.Save
Else
out("File does not exists so saving file to: " & sXlsFileName)
xWorkBook.SaveAs(sXlsFileName)
End If
out("Restoring standard alerts")
xApp.DisplayAlerts = %TRUE
End If

out("Closing active window")
xApp.ActiveWindow.Close

out("Quitting Excel")
xApp.Quit

out("OK, test done. thinBasic will now close.", 0, %TRUE)

End Function