PureBasic Forums - English

Posted: **Fri Mar 20, 2009 11:15 pm**

Hi,

automating Excel as a standalone application is made easy with tools like COMate and PureDispHelper etc.

Embedding an Excel worksheet in an application is easy with a web gadget. (A web gadget doubles as an OLE document container.)

However, embedding an Excel worksheet in an application and then being able to control that worksheet is not so easy! A job for COMate or PureDispHelper!

The following code uses a webgadget to host an OLE document (an Excel worksheet in this case) and uses COMate to retrieve the 'document' object so that the document can be automated etc. It can easily be adapted to host Word documents etc.

There does seem to be a bug with the web gadget which does not repaint itself properly (at least on my tests with XP and Vista) when used to house an OLE server. My workaround is via the window's callback which is otherwise not required. I shall post in the bug section.

Code: Select all

;/////////////////////////////////////////////////////////////////////////////////
;***COMate***  COM automation through iDispatch.
;*===========
;*
;*Automate an Excel workbook embedded within a web-gadget demo by Stephen Rodriguez.
;*This demo uses a web gadget as an OLE document container amd shows how to retrieve the
;*document object (as a COMate object) of the active document (in this case the Excel workbook object).
;*
;*An apparent bug in the WebGadget has forced the use of the window's callback shown below.
;/////////////////////////////////////////////////////////////////////////////////


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

Enumeration
  #WebGadget = 0
  #Button1
EndEnumeration

Define.COMateObject obWebGadget, obWorkbook, obExcelApp

XLS$ =  "c:\Pure basic - 4.3\COMate\Basic demos\test.xls"  ;Change for an Excel worksheet of your choice!


;The callback is needed to repaint the webgadget to combat problems; a bug with the PB webgadget?
Procedure.i Callback(hWnd, uMsg, wParam, lParam)
  Result = #PB_ProcessPureBasicEvents
  If uMsg = #WM_PAINT
    RedrawWindow_(GadgetID(#WebGadget), 0, 0, #RDW_INVALIDATE|#RDW_ALLCHILDREN)
  EndIf  
  ProcedureReturn Result
EndProcedure


If OpenWindow(0, 0, 0, 600, 600, "Embed Excel workbook in Webgadget",  #PB_Window_SystemMenu|#PB_Window_ScreenCentered)
  WebGadget(#WebGadget, 0, 0, WindowWidth(0),WindowHeight(0)-100,  XLS$) 
  ButtonGadget(#Button1, 500, GadgetHeight(#WebGadget) + 40, 80, 20, "Color row 1")
  SetWindowCallback(@Callback())

  ;Wrap the IWebBrowser2 interface within a COMate object.
    obWebGadget = COMate_WrapCOMObject(GetWindowLong_(GadgetID(#WebGadget), #GWL_USERDATA)) 
    If obWebGadget = 0
      MessageRequester("COMate - Embed Excel workbook demo", "Couldn't create the web gadget object!")
      End
    EndIf
  ;Wait until the web browser has completed loading the page.
    status=-1
    While Status 
      While WindowEvent():Delay(1):Wend 
      Status = obWebGadget\GetIntegerProperty("Busy") 
    Wend 

  ;Retrieve the workbook document object (loaded into the web gadget).
    obWorkbook = obWebGadget\GetObjectProperty("Document")
  ;And now the underlying Excel application object.
    If obWorkbook
      obExcelApp = obWorkbook\GetObjectProperty("Application")
    EndIf

  Repeat 
    Event = WaitWindowEvent(); 

    Select Event 
      Case #PB_Event_Gadget 
        Select EventGadget() 
          Case #Button1
            If obExcelApp
              obExcelApp\SetProperty("Range('1:1')\Font\ColorIndex = 5")
            EndIf
        EndSelect 
      Case #PB_Event_CloseWindow 
        Break 
    EndSelect 
  ForEver 

  If obWorkbook
    obWorkbook\Release()
  EndIf

  If obExcelApp
    obExcelApp\Release()
  EndIf

  obWebGadget\Release()
EndIf

PureBasic Forums - English

Embed (and automate) an excel worksheet in an application

Embed (and automate) an excel worksheet in an application