Page 1 of 8 123 ... LastLast
Results 1 to 10 of 78

Thread: Excel COM question...

  1. #1

    Excel COM question...

    Hello all,

    Please forgive what is sure to be a dumb question, but I'm not sure where to start.

    Taking an excerpt from the "Excel_RandomCellFill.tBasic" sample script and modifying it a bit, I get this:

    vParam(1) = 2
    vParam(2) = 2
    vParam(3) = 3
    COM_SETPROPERTY(pXlSheet, "Cells", 3, vParam)

    fills cell 2,2 (B2) with the number 3 as expected.

    My question is basically, how would I modify this snippet to use the 3 in vParam(3) to set the interior.colorindex of the cell to red?

    I've tried replacing "Cells" with all sorts of variations of what I believe are properties (based on an file) but with no joy.

    Perhaps extending my question to cover more subjects, I might ask if there is a resource where I can find the methods and properties as seen by thinBasic?

    I'm guessing the answer is simple, but I'm just not seeing it and don't know where to start looking.

    Thanks for your time,

  2. #2
    Perhaps I should be more general. :-)

    The only example script I see simply sets the contents of an Excel cell. I'd like to look beyond that into cell formatting, etc. but I don't see any examples of that sort of thing.

    So, I'm just looking for a few examples/hints to get me started on the path I must follow. :-)


  3. #3
    Super Moderator Petr Schreiber's Avatar
    Join Date
    Aug 2005
    Brno - Czech Republic
    Blog Entries
    Rep Power
    Hi Gerald,

    thank you for taking thinBasic on a COM ride Here are few useful links for you:

    I am not a Excel COM guru myself, but these should put you on the right track...

    Learn 3D graphics with ThinBASIC, learn TBGL!
    Windows 7 64bit - Intel Core 2 Duo T6600 @ 2.2GHz - 4 GB RAM - NVIDIA GeForce G210M 512MB
    Windows 8 64bit - Intel Core i5-3350P @ 3.1GHz - 8 GB RAM - NVIDIA GeForce GT640 3GB

  4. #4
    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.

    '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?"
    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)
    print "Saving document as:", filename, "\n"
    CallByName(oExcelWorkbook, "SaveAs", vbMethod, filename)
    CallByName(oExcelWorkbook, "Close")
    CallByName(oExcelApp, "Quit")
    print "Releasing objects from memory...\n"
    print "Script complete!\n"
    Last edited by John Spikowski; 17-07-2014 at 21:11.
    ScriptBasic Project Manager
    Project Site

  5. #5
    @ 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! ;-)

  6. #6
    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.

    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


    - 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.
    Last edited by John Spikowski; 18-07-2014 at 01:10.
    ScriptBasic Project Manager
    Project Site

  7. #7
    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. :-)

  8. #8
    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.
    ScriptBasic Project Manager
    Project Site

  9. #9
    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! ;-)

  10. #10
    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.
    ScriptBasic Project Manager
    Project Site

Page 1 of 8 123 ... LastLast

Similar Threads

  1. Question of the day ;)
    By Petr Schreiber in forum thinBasic General
    Replies: 8
    Last Post: 23-08-2010, 19:58
  2. gdi question
    By Lionheart008 in forum UI (User Interface)
    Replies: 6
    Last Post: 07-12-2009, 19:31
  3. Replies: 3
    Last Post: 31-03-2009, 23:16
  4. MS Excel as 3D engine :D
    By ErosOlmi in forum Development
    Replies: 3
    Last Post: 13-03-2008, 01:09
  5. Equivalent VBA Excel code
    By marcel in forum COM
    Replies: 12
    Last Post: 08-11-2007, 16:18

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts