COMate - control COM objects via automation - OBSOLETE!

CNESM · Post by **CNESM** » Wed Mar 18, 2009 9:22 pm

Can you add a macro to an Excel workbook through COMate?

Yes, in vba you have the possibility to create with vba a vba makro by realtime. So you can create a macro. In VBA it goes like this:

Code: Select all

ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule.Lines(1, 1) "'This is a comment"

To translate this to use with comate should not be the problem ... But i dont work with the OnAction property. I want to check the commenbar button in my purebasic program like the old purebasic way in a loop.

As I say, if you wish to catch some events then I would guess that you'll have to load the worksheet into a web gadget or an instance of IE. You can certainly catch events from an IE instance as I've done this myself.

This looks very complicate to do this with a web gadget but maybe its the only way to do this .... damn

i found out some interessting thing. Maybe its a bug, but maybe its a new point to fix the problem:

Code: Select all

XIncludeFile "C:\Desktop\COMate_Residents.pbi" 
XIncludeFile "C:\Desktop\COMate.pbi" 

Global objExcel.COMateObject
objCommandBars.COMateObject
objCommandBarsButton1.COMateObject

Global Quit

Procedure myEventCallback(object.COMateObject, eventName.s, parameterCount)
 If eventName.s="Click"
  objExcel\Release()
  End
 EndIf
EndProcedure

objExcel=COMate_GetObject("", "Excel.Application")
If objExcel=0
  objExcel=COMate_CreateObject("Excel.Application")
EndIf 

If objExcel

  If objExcel\SetProperty("Visible = #True") = #S_OK

    objExcel\Invoke("CommandBars('Test')\Delete")
    objCommandBars=objExcel\GetObjectProperty("CommandBars\Add('Test', 1, 0, 0)") ;1=Position , 0=MenuBar , 0=Temporary 
    
       If objCommandBars
          objCommandBars\SetProperty("Left = 0")
          objCommandBars\SetProperty("Visible = #False")
     
          objCommandBarsButton1=objExcel\GetObjectProperty("CommandBars('Test')\Controls\Add(1)")
          If objCommandBarsButton1
            objCommandBarsButton1\SetProperty("Style = 11") ; 11=msoButtonIconAndCaptionBelow
            objCommandBarsButton1\SetProperty("FaceId = 548")
            objCommandBarsButton1\SetProperty("Caption = 'Test'")
            objCommandBarsButton1\SetProperty("TooltipText = 'Test'")
            objCommandBarsButton1\SetProperty("BeginGroup = #True")
            ;objCommandBarsButton1\SetProperty("OnAction = 'MyMacro'")
            
              objExcel\SetProperty("CommandBars('Test')\Visible = #True")
              HRESULT=objCommandBarsButton1\SetEventHandler(@myEventCallback())
                
              If HRESULT
              Else
                ;Debug "error5"
              EndIf
            
          Else
            Debug "error3"
          EndIf
    

       Else
        Debug "error2"
        EndIf
     
   Else
    Debug "error7"
   EndIf

  
Else
  Debug "error1"
EndIf

MessageRequester("","Don't close this msgbox, please try the button!")

objExcel\Release()

This example seems to work, but when i use a simple loop it doesnt work! See the following example:

Code: Select all

XIncludeFile "C:\Desktop\COMate_Residents.pbi" 
XIncludeFile "C:\Desktop\COMate.pbi" 

Global objExcel.COMateObject
objCommandBars.COMateObject
objCommandBarsButton1.COMateObject

Global Quit

Procedure myEventCallback(object.COMateObject, eventName.s, parameterCount)
Debug object.COMateObject
 If eventName.s="Click"
  objExcel\Release()
  End
 EndIf
EndProcedure

objExcel=COMate_GetObject("", "Excel.Application")
If objExcel=0
  objExcel=COMate_CreateObject("Excel.Application")
EndIf 

If objExcel

  If objExcel\SetProperty("Visible = #True") = #S_OK

    objExcel\Invoke("CommandBars('Test')\Delete")
    objCommandBars=objExcel\GetObjectProperty("CommandBars\Add('Test', 1, 0, 0)") ;1=Position , 0=MenuBar , 0=Temporary 
    
       If objCommandBars
          objCommandBars\SetProperty("Left = 0")
          objCommandBars\SetProperty("Visible = #False")
     
          objCommandBarsButton1=objExcel\GetObjectProperty("CommandBars('Test')\Controls\Add(1)")
          If objCommandBarsButton1
            objCommandBarsButton1\SetProperty("Style = 11") ; 11=msoButtonIconAndCaptionBelow
            objCommandBarsButton1\SetProperty("FaceId = 548")
            objCommandBarsButton1\SetProperty("Caption = 'Test'")
            objCommandBarsButton1\SetProperty("TooltipText = 'Test'")
            objCommandBarsButton1\SetProperty("BeginGroup = #True")
            ;objCommandBarsButton1\SetProperty("OnAction = 'MyMacro'")
            
              objExcel\SetProperty("CommandBars('Test')\Visible = #True")
              HRESULT=objCommandBarsButton1\SetEventHandler(@myEventCallback())
                
              If HRESULT
              Else
                ;Debug "error5"
              EndIf
            
          Else
            Debug "error3"
          EndIf
    

       Else
        Debug "error2"
        EndIf
     
   Else
    Debug "error7"
   EndIf

  
Else
  Debug "error1"
EndIf

Repeat
ForEver

;The same with while and wend:
;
;While A=1
;Wend

A little confuse to me ....

srod · Post by **srod** » Wed Mar 18, 2009 9:44 pm

Very interesting!!!

I tried that yesterday, but closed the messagerequester before hitting the button!!!

Good find there.

Let me have a look at both examples.

srod · Post by **srod** » Wed Mar 18, 2009 9:55 pm

Well blow me down! I wondered yesterday if we should open a window and process events for this to work!!!

All you need to is enter a message loop in order to receive events from the Excel object(s). With your message requester example, a modal message loop is entered. With your second demo, you have no message loop which is why you are not receiving events from the command bar objects.

In your second example, open a hidden window and change your loop to something like :

Code: Select all

Repeat 
  WindowEvent() : Delay(1)
ForEver

Then it all works!!! Indeed you can catch events from the Excel object itself if you want!

Interesting... but kind of obvious I guess!

**EDIT : you realise that you are using an old version of COMate? You should download the latest version in which you will need to use :

Code: Select all

objCommandBarsButton1\SetEventHandler(#COMate_CatchAllEvents, @myEventCallback())

CNESM · Post by **CNESM** » Thu Mar 19, 2009 5:20 pm

Hi,

that make sense:

Code: Select all

Repeat 
  WindowEvent() : Delay(1) 
ForEver

A simple loop like while and wend doesnt catch messages because the event catcher miss. With the WindowEvent() its works fine! Thanks scrod. It think you and me learn some interesting thing about event handle with this therm

Mybe it would help other people, when you add this knowledge to the COMate help file, maybe with a simple example.

Enclosed the full example:

Code: Select all

XIncludeFile "C:\Desktop\COMate_Residents.pbi" 
XIncludeFile "C:\Desktop\COMate.pbi" 

Global objExcel.COMateObject
objCommandBars.COMateObject
objCommandBarsButton1.COMateObject

Global Quit

Procedure myEventCallback(object.COMateObject, eventName.s, parameterCount)
 If eventName.s="Click"
  objExcel\Release()
  End
 EndIf
EndProcedure

objExcel=COMate_GetObject("", "Excel.Application")
If objExcel=0
  objExcel=COMate_CreateObject("Excel.Application")
EndIf 

If objExcel

  If objExcel\SetProperty("Visible = #True") = #S_OK

    objExcel\Invoke("CommandBars('Test')\Delete")
    objCommandBars=objExcel\GetObjectProperty("CommandBars\Add('Test', 1, 0, 0)") ;1=Position , 0=MenuBar , 0=Temporary 
    
       If objCommandBars
          objCommandBars\SetProperty("Left = 0")
          objCommandBars\SetProperty("Visible = #False")
     
          objCommandBarsButton1=objExcel\GetObjectProperty("CommandBars('Test')\Controls\Add(1)")
          If objCommandBarsButton1
            objCommandBarsButton1\SetProperty("Style = 11") ; 11=msoButtonIconAndCaptionBelow
            objCommandBarsButton1\SetProperty("FaceId = 548")
            objCommandBarsButton1\SetProperty("Caption = 'Test'")
            objCommandBarsButton1\SetProperty("TooltipText = 'Test'")
            objCommandBarsButton1\SetProperty("BeginGroup = #True")
            ;objCommandBarsButton1\SetProperty("OnAction = 'MyMacro'")
            
              objExcel\SetProperty("CommandBars('Test')\Visible = #True")
              HRESULT=objCommandBarsButton1\SetEventHandler(#COMate_CatchAllEvents,@myEventCallback())
                
              If HRESULT
              Else
                ;Debug "error5"
              EndIf
            
          Else
            Debug "error3"
          EndIf
    

       Else
        Debug "error2"
        EndIf
     
   Else
    Debug "error7"
   EndIf

  
Else
  Debug "error1"
EndIf

OpenWindow(0,0,0,0,0,"Test",#PB_Window_Invisible)

Repeat 
  WindowEvent() : Delay(1) 
ForEver

I downloadet the new version, thanks and thanks for your help. I appreciate that!

Edit: I have one followup question. How can i detemine when the user close the opened excel application? When the user use the objCommandBarsButton1, i can release the Excel.Application and close my program correctly, but when the user close the excel.application, my program still running and i had to "kill" my program by hand in purebasic, but this doesn't work in the final version because there will be no visible program window. So i must find out, when the user close my Excel.Application, witch i have started with "CreateObject" or catched with "GetObject". So i can close my program correctly and release all COMate objects. I should have to check every second if the Excel.Application are still valid. Is that possible to check out if the objExcel are still running or already been closed?

srod · Post by **srod** » Thu Mar 19, 2009 7:13 pm

The only way that comes to mind right now is to check the visible property :

Code: Select all

objExcel\GetIntegerProperty("Visible")

Perhaps set up a timer to check this. If the user closes the Excel window then your objExcel object will still be valid so you can retrieve this property without worry.

There must be a better way however!

You can certainly trap an event when a workbook has been closed. This won't help though if there is no workbook open.

SFSxOI · Post by **SFSxOI** » Thu Mar 19, 2009 9:00 pm

Should not simply trapping the message that the workbook has closed be enough? Sounds like he's planning to release the objects when the workbook closes anyway and in that case a new instance of the application would be needed for further workbooks, so he doesn't need to worry about if a workbook is not open? Or did I read what he wrote wrong?

srod · Post by **srod** » Fri Mar 20, 2009 11:37 am

Closing a workbook does not prevent the user from opening a new workbook under the same instance of Excel. Besides, the user may well have opened additional workbooks in the meantime.

As you say, it may well be that the close workbook notification is enough for CNESM's purposes however.

CNESM · Post by **CNESM** » Fri Mar 20, 2009 1:36 pm

I want to check out if my instance "objExcel" is still running or if the user had close the whole excel application that i had create with "CreateObject". I doesn't need to know if a workbook is still open. Its enough for me to know if my excel application is still running. By the way: I will check if a workbook is open because with this feedback i activate/deactive some buttons of the commendbar.

The given example from you, scrod, works fine for Excel:

Code: Select all

objExcel\GetIntegerProperty("Visible")

But i think that it would be great when there will be a COMate function to check out if the Object are still valid. Further it make sense when there will be a function like thsi given function for container:

Code: Select all

GetContainerhWnd

, but to get the handle of a CreateObject to work better with pb. This is only remark.

srod · Post by **srod** » Fri Mar 20, 2009 1:43 pm

I think you missed the point here. If the user closes the Excel window, the instance of your objExcel object is still valid because it has not been released! This is purely under the control of your program; not Excel and most definitely not COMate!

The point is that such a function as you describe would be useless because it would return a value of #True until you yourself released the underlying object! I'll say it again, the user closing the Excel window does not release the object variable which your program created - all hell could break loose if it did!

CNESM · Post by **CNESM** » Fri Mar 20, 2009 1:53 pm

The point is that such a function as you describe would be useless because it would return a value of #True until you yourself released the underlying object!

I understand what you say, scrod, but I think it would be usefull when you build this function independente of COMmate, maybe to give back the handle of the running application or something like this. It will save the user for bad program crashs. You can update the CreateObject function with a optional return value, that include the object handle. With this handle the user can check if the Createobject it still running.

I tested the given example:

Code: Select all

objExcel\GetIntegerProperty("Visible")

But this function doesn't work fine in every situation. The function give back a #False too, when the user open the OpenFilerequester of Excel or another excel internal dialog ... damn

srod · Post by **srod** » Fri Mar 20, 2009 2:00 pm

I understand what you say, scrod, but I think it would be usefull when you build this function independente of COMmate, maybe to give back the handle of the running application or something like this. It will save the user for bad program crashs. You can update the CreateObject function with a optional return value, that include the object handle. With this handle the user can check if the Createobject it still running.

I do not see how this is possible. When asked to create an object instance, COMate is not in a position to really know anything about the object in question (not without rummaging through type libraries and the like). It does not even know (or care) if it is creating an in-process or out-of-process server etc.

SFSxOI · Post by **SFSxOI** » Fri Mar 20, 2009 4:09 pm

You could possibly use a WMI solution using the WMI SWbemServices.ExecNotificationQuery > http://msdn.microsoft.com/en-us/library/aa393864.aspx and poll it for events from your workbook and take action based on that. Maybe ?

Whoops! didn't think - COMate doesn't have such a query (does it?)

or you could get the PID when the workbook is created (theres code somewhere in the for to get the PID) and then use the ProcessId of the Win32_Process class to at least tell you if the PID (the workbook) is still running by polling and if it isn't then release your objects ?

CNESM · Post by **CNESM** » Sat Mar 21, 2009 11:35 am

Hi,

WMI only give back one instants not unconditional my started application:

Code: Select all

strAppName.s=objExcel\GetStringProperty("Caption")

objWMI=COMate_GetObject("winmgmts:{impersonationLevel=impersonate}!root\cimv2")
objErg=objWMI\GetIntegerProperty("ExecQuery('Select * from win32_process where name='" + strAppName.s + "'')")
Debug objErg

WMI catch the first instance with the searched name.

Yesterday, after finishing one another project, i had one idea: Is it possible to get with "GetObject" all running applications of excel and to set the commendbar to alle running instance? Than the WMI solution would be good for this type of solution, but i think this would not be run, to get different objects with "Getobject", mybe in a simple loop and to catch all objects with "GetFirstObject" and "GetNextObject" for example.

srod · Post by **srod** » Sat Mar 21, 2009 11:43 am

Can you try the following code which is, as yet, incomplete. It embeds an entire copy of Excel within a container gadget in such a way that the system menu has been disabled and the user has to use the close button on your own window to close the application etc. This of course solves your problem!

I have only tested with Office 2003 and XP / Vista. I am unsure whether it will work properly with Office 2007.

As I say the code is incomplete, but we can at least see if we are on the right tracks here?

Code: Select all

;/////////////////////////////////////////////////////////////////////////////////
;***COMate***  COM automation through iDispatch.
;*===========
;*
;*Excel demo.
;/////////////////////////////////////////////////////////////////////////////////

IncludePath "..\"
XIncludeFile "COMate.pbi"

;Set the following constant to a string identifier which will be used to identify the instance of Excel.
;This must be unique amongst all possible instances.
#ExcelCaption$ = "COMate_EmbedExcel"

EnableExplicit

Global gOldProc

Define excelObject.COMateObject, eventID, excelhWnd


Enumeration
  #ExcelContainer
EndEnumeration


;/////////////////////////////////////////////////////////////////////////////////
;The following is called repeatedly in order to locate the Excel window.
Procedure.i enumWindowProc(hWnd, *excelhWnd.INTEGER)
  Protected title$, hMenu, i
  title$ = Space(100)
  GetWindowText_(hWnd, @title$, 100)
  If title$ = #ExcelCaption$ And IsWindowVisible_(hWnd) = 0
    *excelhWnd\i = hWnd
    ProcedureReturn 0
  EndIf
  ProcedureReturn 1
EndProcedure
;/////////////////////////////////////////////////////////////////////////////////


;/////////////////////////////////////////////////////////////////////////////////
Procedure.i LoadExcel(hWndContainer)
  Protected excelObject.COMateObject, excelhWnd, hMenu, i, rc.RECT
  Protected borderWidth, borderHeight, captionHeight, statusHeight
  excelObject = COMate_CreateObject("Excel.Application")
  If excelObject
    excelObject\SetProperty("Caption = '" + #ExcelCaption$ + "'")
    EnumWindows_(@enumWindowProc(), @excelhWnd)
    If excelhWnd
      excelObject\SetProperty("Visible=#True")
      SetGadgetData(#ExcelContainer, excelhWnd)
      ;Disable all system menu items.
        hMenu = GetSystemMenu_(excelhWnd, #False)
        For i = GetMenuItemCount_(hMenu)-1 To 0 Step -1
          RemoveMenu_(hMenu, i, #MF_REMOVE|#MF_BYPOSITION) 
        Next
      ;Place the window inside the container and reposition / resize it.
        SetParent_(excelhWnd, GadgetID(0))
        SendMessage_(hWndContainer, #WM_SIZE, 0, 0)


      ProcedureReturn excelObject
    Else
      excelObject\Release()
    EndIf
  EndIf
EndProcedure
;/////////////////////////////////////////////////////////////////////////////////


;The callback is needed to repaint the Excel window to combat problems; a bug with the PB webgadget?
Procedure.i containerCallback(hWnd, uMsg, wParam, lParam)
  Protected result, excelhWnd, rc.RECT
  Protected borderWidth, borderHeight, captionHeight, statusHeight
  Select uMsg
    Case #WM_SIZE
      excelhWnd = GetGadgetData(#ExcelContainer)
      GetClientRect_(GadgetID(#ExcelContainer), rc)
      borderWidth = GetSystemMetrics_(#SM_CXFRAME)
      borderHeight = GetSystemMetrics_(#SM_CYFRAME)
      captionHeight = GetSystemMetrics_(#SM_CYCAPTION)
      statusHeight = GetSystemMetrics_(#SM_CYSMCAPTION)
      MoveWindow_(excelhWnd, -borderWidth, -borderHeight-captionHeight, rc\right-rc\left+2*borderWidth, rc\bottom-rc\top+2*borderHeight+captionHeight+statusHeight, 1)

    Case #WM_PAINT
      result = CallWindowProc_(gOldProc, hWnd, uMsg, wParam, lParam)
      excelhWnd = GetGadgetData(#ExcelContainer)
      RedrawWindow_(excelhWnd, 0, 0, #RDW_INVALIDATE|#RDW_FRAME|#RDW_ALLCHILDREN)
    Case #WM_ERASEBKGND
      result = 1
    Default
      result = CallWindowProc_(gOldProc, hWnd, uMsg, wParam, lParam)
  EndSelect
  ProcedureReturn Result
EndProcedure


If OpenWindow(0, 0, 0, 800, 600, "Embed Excel application", #PB_Window_SystemMenu | #PB_Window_ScreenCentered | #PB_Window_SizeGadget | #PB_Window_MaximizeGadget)
  ;Create a container to house the Excel window. Ideally this container should be subclassed and all erasing prevented etc.
    ContainerGadget(#ExcelContainer, 250, 0, 0, 0, #PB_Container_BorderLess)
    gOldProc = SetWindowLong_(GadgetID(#ExcelContainer), #GWL_WNDPROC, @containerCallback())

  ;Load Excel into our container.
    excelObject = LoadExcel(GadgetID(#ExcelContainer))
    If excelObject = 0
      MessageRequester("COMate - Embed Excel application demo", "There was a problem loading Excel!")
      End
    EndIf



  Repeat
    eventID = WaitWindowEvent()
    Select eventID
      Case #WM_SIZE
        ResizeGadget(#ExcelContainer, #PB_Ignore, 0, WindowWidth(0)-250, WindowHeight(0))
    
    EndSelect
  Until eventID = #PB_Event_CloseWindow
  HideWindow(0, 1)  ;Avoids flicker when releasing the Excel objects.

  ;Avoid crashes.
    excelhWnd = GetGadgetData(#ExcelContainer)
    ShowWindow_(excelhWnd, #SW_HIDE)
    SetParent_(excelhWnd, 0)
  ;Tidy up.
    excelObject\Invoke("Quit()")
    excelObject\Release()
EndIf

End

**EDIT : problem with the above code. Select a cell, move the window off screen and back again and then try to select other cells!!! Having trouble fixing this. If anyone can find a fix then that would be very useful!

I'll keep searching...

ts-soft · Post by **ts-soft** » Sat Mar 21, 2009 12:24 pm