Excel Spreadsheet in a Control

[DougH]

I have a question regarding embedding objects in a control. I am working on an application that uses the Monthly Calendar control and some input boxes.

I also need to display a range of cells from an existing spreadsheet into a control on the existing window. I know I can create another windows and embed the excel spreadsheet into it, but I do not want to create a new window. I am thinking of creating a large label or listbox and embed the Excel spreadheet inside it.

Is this even possible with AutoIT?

Thanks in advance for your help.

[PsaltyDS]

I have a question regarding embedding objects in a control. I am working on an application that uses the Monthly Calendar control and some input boxes.

I also need to display a range of cells from an existing spreadsheet into a control on the existing window. I know I can create another windows and embed the excel spreadsheet into it, but I do not want to create a new window. I am thinking of creating a large label or listbox and embed the Excel spreadheet inside it.

Is this even possible with AutoIT?

Thanks in advance for your help.

Use Locodarwin's _ExcelCOM_UDF.au3 and read the cells you want into an AutoIt array, then write them from the array to a ListView control.

[strate]

I have a question regarding embedding objects in a control. I am working on an application that uses the Monthly Calendar control and some input boxes.

I also need to display a range of cells from an existing spreadsheet into a control on the existing window. I know I can create another windows and embed the excel spreadsheet into it, but I do not want to create a new window. I am thinking of creating a large label or listbox and embed the Excel spreadheet inside it.

Is this even possible with AutoIT?

Thanks in advance for your help.

This will not work in Office07 but it is what I've used in previous versions.

expandcollapse popup

#include <GUIConstants.au3>
;
; Embedding an Excel document inside an AutoIt GUI
;
; Limitations:
;
; 1. Integrating the GUI Menu with the Objects Menu does not work.
;   (they have seperate menu bars)
;

; Initialize my error handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

;~ $FileName=@ScriptDir & "\Worksheet.xls"
$FileName = 'C:\Documents and Settings\Administrator\My Documents\Book1.xls'

if not FileExists($FileName) then
  Msgbox (0,"Excel File Test","Can't run this test, because it requires an Excel file in "& $FileName)
  Exit
endif

$oExcelDoc = ObjGet($FileName) ; Get an Excel Object from an existing filename

if IsObj($oExcelDoc) then
    GUICreate ( "Embedded ActiveX Test", 640, 580, (@DesktopWidth-640)/2, (@DesktopHeight-580)/2 , $WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPCHILDREN )
    $GUI_ActiveX = GUICtrlCreateObj( $oExcelDoc, 1, 95 , 400 , 300 )
    
    With $oExcelDoc
        .CommandBars("Full Screen").Visible = False
        .CommandBars("Standard").Enabled = False
        .CommandBars("Formatting").Enabled = False
        .CommandBars("Worksheet Menu Bar").Enabled = False
        .Application.DisplayFormulaBar = False
    EndWith
    
    GUISetState ()      ;Show GUI
    
   ; GUI Message loop
    While 1
        $msg = GUIGetMsg()
        Select
            Case $msg = $GUI_EVENT_CLOSE;or $msg = $GUI_FileExit
                ExitLoop
        EndSelect
    Wend

    GUIDelete ()

   ; Don't forget to close your workbook, otherwise Excel will stay in memory after the script exits !
    $oExcelDoc.Close (0)       ; Close the Excel workbook - Save prompt will not open
    
EndIf


Exit

; This is my custom error handler
Func MyErrFunc()
  $HexNumber=hex($oMyError.number,8)
  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"       & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & $HexNumber              & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )
  SetError(1) ; to check for after this function returns
Endfunc

[PsaltyDS]

This will create a spreadsheet of test data, read the data into a 2D array, and display it.

Creating the GUI and putting the data in a ListView is left as an exercise for the student (as is testing with Office07):

#include <ExcelCOM_UDF.au3>
#include <array.au3> ; For _ArrayDisplay() only

; Create a sample spreadsheet (could read a file instead)
$oExcel = _ExcelBookNew()
For $x = 1 To 10
    For $y = 1 To 10
        _ExcelWriteCell($oExcel, $x & "_" & $y, $x, $y)
    Next
Next

; Read data to 2D array, one row at a time
Global $avData[8][8]
For $x = 2 To 9
    $avTemp = _ExcelReadArray($oExcel, $x, 2, 8, 0, 0)
    For $y = 0 To 7
        $avData[$x - 2][$y] = $avTemp[$y]
    Next
Next

_ArrayDisplay($avData, "Debug: $avData")

[DougH]

Thanks for all your responses. Looking through the UDF did help me.

I have not yet used the new UDF, but have gotten the excel spreadsheet to display in a control. What I have now is the menu and icons from the Excel application overlaying on my form. Is there any way of suppressing this from loading?