Sign in to follow this  
Followers 0
DougH

Excel Spreadsheet in a Control

5 posts in this topic

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.

Share this post


Link to post
Share on other sites



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.

:P


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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.

#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

INI TreeViewA bus station is where a bus stops, a train station is where a train stops. Onmy desk I have a work station...

Share this post


Link to post
Share on other sites

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

:P


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0