Page 5 of 8 FirstFirst ... 34567 ... LastLast
Results 41 to 50 of 78

Thread: Excel COM question...

  1. #41
    Super Moderator Petr Schreiber's Avatar
    Join Date
    Aug 2005
    Location
    Brno - Czech Republic
    Posts
    7,128
    Rep Power
    732
    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

    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
    Attached Files Attached Files
    Last edited by Petr Schreiber; 22-07-2014 at 22:55.
    Learn 3D graphics with ThinBASIC, learn TBGL!
    Windows 10 64bit - Intel Core i5-3350P @ 3.1GHz - 16 GB RAM - NVIDIA GeForce GTX 1050 Ti 4GB

  2. #42
    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

  3. #43
    Quote Originally Posted by Petr Schreiber View Post
    What do you think about syntax? I consider it pretty cool ... 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?
    Last edited by mike lobanovsky; 23-07-2014 at 10:46.
    Mike
    (3.6GHz i5 Core Quad w/ 16GB RAM, nVidia GTX 1060Ti w/ 6GB VRAM, x64 Windows 7 Ultimate Sp1)

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

  5. #45
    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.



    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)
    
    ScriptBasic Project Manager
    Project Site
    support@scriptbasic.org

  6. #46
    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
    Last edited by Gerald Sutherland; 23-07-2014 at 18:38.

  7. #47
    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
    Last edited by Gerald Sutherland; 24-07-2014 at 16:18.

  8. #48
    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.



    ' 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
    
    Last edited by John Spikowski; 25-07-2014 at 00:11.
    ScriptBasic Project Manager
    Project Site
    support@scriptbasic.org

  9. #49
    Super Moderator Petr Schreiber's Avatar
    Join Date
    Aug 2005
    Location
    Brno - Czech Republic
    Posts
    7,128
    Rep Power
    732
    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
    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...
    Last edited by Petr Schreiber; 24-07-2014 at 21:31.
    Learn 3D graphics with ThinBASIC, learn TBGL!
    Windows 10 64bit - Intel Core i5-3350P @ 3.1GHz - 16 GB RAM - NVIDIA GeForce GTX 1050 Ti 4GB

  10. #50
    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?
    Last edited by mike lobanovsky; 24-07-2014 at 21:34.
    Mike
    (3.6GHz i5 Core Quad w/ 16GB RAM, nVidia GTX 1060Ti w/ 6GB VRAM, x64 Windows 7 Ultimate Sp1)

Page 5 of 8 FirstFirst ... 34567 ... 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

Members who have read this thread: 0

There are no members to list at the moment.

Posting Permissions

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