Results 1 to 4 of 4

Thread: COM with Excel - I will fail without support.....

  1. #1

    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 !

    best regards

    Christian
    PS: To make one thing clear - I really like thinBasic - it's more about my missing knowledge....
    Attached Files Attached Files

  2. #2
    Super Moderator Petr Schreiber's Avatar
    Join Date
    Aug 2005
    Location
    Brno - Czech Republic
    Posts
    7,128
    Rep Power
    732

    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:
     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
    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

  3. #3

    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
    Attached Files Attached Files

  4. #4
    Super Moderator Petr Schreiber's Avatar
    Join Date
    Aug 2005
    Location
    Brno - Czech Republic
    Posts
    7,128
    Rep Power
    732

    Re: COM with Excel - I will fail without support.....

    Hi Chris,

    I guess GetIndexedProperty could serve ... but not sure how

    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
    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

Similar Threads

  1. MS Excel as 3D engine :D
    By ErosOlmi in forum Development
    Replies: 3
    Last Post: 13-03-2008, 01:09

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
  •