Excel-like Grid COM object

[smahdi83]

Hi,

I need an object that resembles Excel. I already have the perfect object to do that except that it's too unstable. If I can get it to function properly, it would be perfect! Here's the code maybe you can help. You will see that when you try to edit the cells, the object either gets misplaced or dissapears. How can I fix that?

You need to have Office Web Components installed but that comes with MS Office.

Thanks in advance,

expandcollapse popup

#include <GUIConstants.au3>

$Form2 = GUICreate("Form2", 793, 541, 294, 196, BitOR($WS_MAXIMIZEBOX,$WS_MINIMIZEBOX,$WS_SYSMENU,$WS_CAPTION,$WS_POPUP,$WS_POPUPWINDOW,$WS_GROUP,$WS_TABSTOP,$WS_BORDER,$WS_CLIPSIBLINGS))
$Tab1 = GUICtrlCreateTab(8, 16, 777, 513)
GUICtrlSetResizing(-1, $GUI_DOCKAUTO)
$TabSheet1 = GUICtrlCreateTabItem("TabSheet1")
GUICtrlCreateTabItem("")
GUISetState(@SW_SHOW)
Local $oExcel1

;Declare objects
$oExcel1 = ObjCreate("OWC11.spreadsheet"); Office 2003  

If not IsObj($oExcel1) Then
    $oExcel1 = ObjCreate("OWC10.spreadsheet");Default to Office XP
EndIf
If not IsObj($oExcel1) Then
    $oExcel1 = ObjCreate("OWC00.spreadsheet"); Office 2000
EndIf

Local $oExcel2

;Declare objects
$oExcel2 = ObjCreate("OWC11.spreadsheet");Office 2003 

If not IsObj($oExcel2) Then
    $oExcel2 = ObjCreate("OWC10.spreadsheet");Default to Office XP   
EndIf
IF not IsObj($oExcel2) Then
    $oExcel2 = ObjCreate("OWC00.spreadsheet");ffice 2000
EndIf

$allocation_bulk_reguacc_excel = GUICtrlCreateObj($oExcel1, 100, 75, 220, 420)
GUICtrlSetStyle($allocation_bulk_reguacc_excel, $WS_VISIBLE)
GUICtrlSetResizing ($allocation_bulk_reguacc_excel, $GUI_DOCKAUTO)
$allocation_bulk_regiacc_excel = GUICtrlCreateObj($oExcel2, 400, 75, 220, 420)
GUICtrlSetStyle($allocation_bulk_regiacc_excel, $WS_VISIBLE)
GUICtrlSetResizing ($allocation_bulk_regiacc_excel, $GUI_DOCKAUTO)
GUISetState(@SW_MAXIMIZE)
While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit

    EndSwitch
WEnd

Edited October 26, 2007 by smahdi83

[DjDeep00]

I had the same problem with one of my scripts...Luckily I didnt really need the tab control...so i got rid of it...Not sure if you really need the tab control....because it works fine without it....

Try this also...

$allocation_bulk_reguacc_excel = GUICtrlCreateObj($oExcel1, 100, 75, 220, 420)

GUICtrlSetState(-1,$GUI_ONTOP)

Edited October 26, 2007 by DjDeep00

[kjactive]

Hallo Smahdi83

I think that your trouble with embedded excel component resize / move after the maximize is because you use the excel compoments directly, some components like the MSOffice can be used dirrectly in some manner but was original written as subcomponents in mind - if so then you has to use a form component and attach your excel or what ever component to this form and control things like size and location though this form.

Global $oForm = ObjCreate("Forms.Form.1")
Global $oFormEvt = ObjEvent($oForm,"Form_")
With $oForm
    .caption = ""
    .Designmode = -1
    .ShowToolBox = $Showtools
    .ShowGridDots = -1
    .GridX = 0
    .BorderStyle = 0; 1 border
    .MousePointer = 0; integer 0 - 15
    .ScrollBars = 3; 0 - 3
    .SpecialEffect = 3; 0 - 3
    .PictureSizeMode = 0; 0 clip, 1 strech, 3 Zoom
    .PictureAlignment = 0;  0 top left, 1 top right, 2 center, 3 bottom left, 4 bottom right. 
EndWith

This is how to set up the form component PROGID 'Forms.Form.1' from the FM20.DLL library, this is an old one from Microsoft and there are new and better ones around...

Global $oButton1 = $oForm.Add("OWC.Spreadsheet.9","OWC.Spreadsheet",1)
$oButtonEvt1 = ObjEvent($oButton1,"Button1_")
With $oButton1
    .caption = "SpreadSheet"
    .Left = 240
    .Top = 8
    .Height = 220
    .Width = 170
EndWith

This how to add your excel component to the form component - now you can control size / location and other basic matters though the form but the actual spreadsheets matters is still handled as in your script

There is three types of component major groups - Standalones ( like WMPlayer.OCX ), SubComponents ( like Forms.SpinButton.1 ) and backgrounds ( control without intuitions or is in directly correspond with a main component and can't be used without ( like MSWebDVD.MSDVDAdm.1 )

1.The standalones has offen its own intuition and can't be correctly buildin to a autoit3 window as they are independed components...

2.SubComponents offen has to have other components attached and offen has to be controlled by a form of some sort...

3.Background components - is offen vital and always part of a major system, not always needed to use this particulare main component, some is auto opened when needed and other has to be manual opened and closed...

I noticed in your script that you don't free your components at exit point - you must do that with

$component = 0

Hopefully this can get cast some light into your project...

kjactive

Edited October 28, 2007 by kjactive

[randallc]

Hi,

Thanks for that!

Can you make it esay and show how to put it in his script too.?.... make it a functioning example?...

Best, Randall

[kjactive]

Hallo Randallc

Okay I did a small fast example, this is just an example but I've included some vital parts related to components handle to let you get a grib on how to deal with embedded activex componets like included in forms - this is how all MSOffice components like to be treated!

The example script starts with declare a form component - the main driver so to speek, open a window and attach the speadsheet components to the form components, there is no limit to the amound of components attached to one form ( that I know off anyway )...

The form components size is set in the GUICtrlCreateObj and this is the actually component used, the spreadsheet is add to this but has its own size set in the add process...

Note: that I attach an event process to both components as this is a vital part to manny / nearly all components handle but not always nessesary, well in this case I think! anyway - Only two example event functions attached, one mouse location attached to the form component and one dblmouse click attached to the spreadsheet but this displays the line, cell numbers and the contense if any - a vital one I think and there is a lot of these available to MSOffice componets but this is microsoft so documentation is not that smart and move around in their masive database system - but look and your'll find I think, It's a long time since I last worked with these components...

Remember to free components from memory, in this example there is only one....

expandcollapse popup

; =======================================================================
; Embedded Form Component example by Kåre Johansson 28.10.07
; Use the form component PROGID 'Forms.Form(1)' from the FM20.DLL library
; and OWC.Spreadsheet(9) from the MSOWC.DLL
; =======================================================================

#include <GUIConstants.au3>

Global $MainW,$Obj,$oForm,$oFormEvt; intuition variables
Global $oExcel,$oExcelEvt

$oForm = ObjCreate("Forms.Form.1"); create the form component
$oFormEvt = ObjEvent($oForm,"Form_"); attach an event handle

With $oForm; Form Component pool
    .caption = "SpreadSheet Example"; title
    .BorderStyle = 1; 0 no border
    .MousePointer = 0; integer 0 - 15
    .ScrollBars = 0; 0 - 3
    .SpecialEffect = 3; 0 - 3; border style
EndWith
$MainW = GUICreate("Embedded Form Component example", 600, 600)
$Obj = GUICtrlCreateObj($oForm,0,0,400,400)

Global $oExcel = $oForm.Add("OWC.Spreadsheet","OWC.Spreadsheet",1)
Global $oExcelEvt = ObjEvent($oExcel,"Excel_")
With $oExcel; add excel Component pool to the form component
    .Left = 5
    .Top = 10
    .Height = 250
    .Width = 200
EndWith
GUISetState(); show the window

While 1
   $msg = GUIGetMsg(1)
   Select
    Case $msg[0] = 0; Catch all uninitiated msgs ticks to speed up handle process
    Case $msg[1] = $MainW
        Select
        Case $msg[0] = $GUI_EVENT_CLOSE
            ExitLoop
        EndSelect
    EndSelect
Wend
$oForm = 0; VITAL - Free your form component memory
Exit

;==================================================================; form event trick
;Form_MouseMove: Event Mouse X and Y location
; Comp Control, shift control, X, Y / no return
;==================================================================
Func Form_MouseMove($Button,$Shift,$X,$Y)
    ConsoleWrite('Form Mouse event DX: ' & Round($X,-1)  & '  DY: ' & Round($Y,-1) & @CRLF)
EndFunc

;==================================================================; Excel event trick
;Excel_DblClick: Event Mouse DblClick
; Comp Line and Column And cell contense/ no return
;==================================================================
Func Excel_DblClick($EventInfo)
Local $iLine, $iCol, $sCell
    $iLine = $EventInfo.Range.Row
    $iCol = $EventInfo.Range.Column
    ConsoleWrite("Excel Line: " & $iLine & " Column: " & $iCol & @CRLF)
    With $oExcel; Excel component pool
        $sCell = .ActiveCell.Value; get the Cell contense
        If $sCell <> '' then ConsoleWrite("Excel Cell Contense: " & $sCell & @CRLF); display the cell contense if any
    EndWith
EndFunc

a small warning to all - be aware of the virus and backdoor posibilities, as this is a real danger with some activex componets...

Hopefully this will cast some light into your componet projects

kjactive

Edited October 28, 2007 by kjactive

[randallc]

Magic,

Thanks, i will look further..

randall

[randallc]

Magic,

Thanks, i will look further..

randall

PS; still does not address the original problem of "losingit" on a tab?...

[kjactive]

Hallo Randallc

I was referenced to the first topics where Smahdi83 having trouble with resizeing - But I don't quiet get what's your problem 'tab?' autoit3 or component tabs and how they are missbehaveing , could you provide me a small example to visual show me what's wrong and I'll look into it if posible...

kjactive

[randallc]

Hallo Randallc

I was referenced to the first topics where Smahdi83 having trouble with resizeing - But I don't quiet get what's your problem 'tab?' autoit3 or component tabs and how they are missbehaveing , could you provide me a small example to visual show me what's wrong and I'll look into it if posible...

kjactive

hi,

Just look at the script in post #1; it is making the form on a tab and you can see the problem, as described, when attempmting to edit; nothing to do with "resizing", I believe?

best, randall

[smahdi83]

hi,

Just look at the script in post #1; it is making the form on a tab and you can see the problem, as described, when attempmting to edit; nothing to do with "resizing", I believe?

best, randall

Thanks a lot guys for your help. It is much appreciated.

[kjactive]

Well if the trouble is what I see then it's a combination of two things, the size of the component and the spot the tab is placed

expandcollapse popup

; =======================================================================
; Embedded Form Component example by Kåre Johansson 28.10.07
; Use the form component PROGID 'Forms.Form(1)' from the FM20.DLL library
; and OWC.Spreadsheet(9) from the MSOWC.DLL
; =======================================================================

#include <GUIConstants.au3>

Global $MainW,$Obj,$oForm,$oFormEvt; intuition variables
Global $oExcel,$oExcelEvt

$oForm = ObjCreate("Forms.Form.1"); create the form component
$oFormEvt = ObjEvent($oForm,"Form_"); attach an event handle

With $oForm; Form Component pool
    .caption = "SpreadSheet Example" ; title
    .BorderStyle = 1; 0 no border
    .MousePointer = 0 ; integer 0 - 15
    .ScrollBars = 0; 0 - 3
    .SpecialEffect = 3; 0 - 3; border style
EndWith
$MainW = GUICreate("Embedded Form Component example", 650, 510,-1,-1,$WS_MAXIMIZEBOX)
$Tab1 = GUICtrlCreateTab(8, 16, 600, 450)
$TabSheet1 = GUICtrlCreateTabItem("TabSheet1")

$Obj = GUICtrlCreateObj($oForm,15,50,580,400)
Global $oExcel = $oForm.Add("OWC.Spreadsheet","OWC.Spreadsheet",1)
Global $oExcelEvt = ObjEvent($oExcel,"Excel_")

With $oExcel; add excel Component pool to the form component
    .Left = 5
    .Top = 10
    .Height = 300
    .Width = 500
EndWith

GUICtrlSetResizing($Tab1, $GUI_DOCKAUTO)
GUICtrlCreateTabItem("this")
GUISetState(); show the window

While 1
   $msg = GUIGetMsg(1)
   Select
    Case $msg[0] = 0; Catch all uninitiated msgs ticks to speed up handle process
    Case $msg[1] = $MainW
        Select
        Case $msg[0] = $GUI_EVENT_CLOSE
            ExitLoop
        EndSelect
    EndSelect
Wend
$oForm = 0; VITAL - Free your form component memory
Exit

;==================================================================; form event trick
;Form_MouseMove: Event Mouse X and Y location
; Comp Control, shift control, X, Y / no return
;==================================================================
Func Form_MouseMove($Button,$Shift,$X,$Y)
    ConsoleWrite('Form Mouse event DX: ' & Round($X,-1)  & '  DY: ' & Round($Y,-1) & @CRLF)
EndFunc

;==================================================================; Excel event trick
;Excel_DblClick: Event Mouse DblClick
; Comp Line and Column And cell contense/ no return
;==================================================================
Func Excel_DblClick($EventInfo)
Local $iLine, $iCol, $sCell
    $iLine = $EventInfo.Range.Row
    $iCol = $EventInfo.Range.Column
    ConsoleWrite("Excel Line: " & $iLine & " Column: " & $iCol & @CRLF)
    With $oExcel; Excel component pool
        $sCell = .ActiveCell.Value; get the Cell contense
        If $sCell <> '' then ConsoleWrite("Excel Cell Contense: " & $sCell & @CRLF); display the cell contense if any
        
    EndWith
EndFunc

Check this script out and see if the trouble is gone...

Kjactive

Edited October 29, 2007 by kjactive

[randallc]

Hi,

Same problem on my machine, I fear; eg click on C7, "55", enter, OWC disappears?....

Best, randall

[smahdi83]

Ok if there's a way to place the object back to its original location as soon as it dislocates from where it's supposed to be, then this can be a workaround fix. Anyone has an idea on how to do that?

Thanks,

[kjactive]

Hallo all Excelleres

I don't see trouble any than when if I do an 'undo' in the spreadsheet component to the cell values and come to the last cell with a value and remove this the component flips out of focus - BUT the component is still there it just need an update, try tab to next tab and back and there is the spreadsheet....

It's a long time since I did anything with this components but I'm positive that the components has an update command, I just don't recall

but I do remember a function call $oExcel.ViewableRange = "A1:D5" this sets the working area - put it under dblclick event function now try to dblclick in a cell and the component disapears, use the autoit3 tabs to reinstall the component view and now the component update area is reduced - if this function is commented out the component view do not disapears - just to show you that this is a large component with lots of different functions that totally change settings...

Setting the undo off $oExcel.EnableUndo = False

Remove the toolbar $oExcel.DisplayToolbar = False

Manual do the undo $oExcel.Undo ( there's an .beginundo / .endundo ) too, ( No visual trouble ), this tells me that, as the toolbar is an subobject this is where to look fo the trouble, maybe one has to initiate this manual but I don't really know...

I should mention that there's a lot of subobjects to control different parts of the main process like SpreadSheetEventInfo ec...

I droppped the compleatly reference to the OWC rev 9 component on the net at link:

http://www.fritidshjemmet.com/Temp/MSOWC_9.chm

Start under SpreadSheet, hopefully this can cast some light to the project - Good luck

kjactive

Edited October 30, 2007 by kjactive

[smahdi83]

Hallo all Excelleres

I don't see trouble any than when if I do an 'undo' in the spreadsheet component to the cell values and come to the last cell with a value and remove this the component flips out of focus - BUT the component is still there it just need an update, try tab to next tab and back and there is the spreadsheet....

It's a long time since I did anything with this components but I'm positive that the components has an update command, I just don't recall

but I do remember a function call $oExcel.ViewableRange = "A1:D5" this sets the working area - put it under dblclick event function now try to dblclick in a cell and the component disapears, use the autoit3 tabs to reinstall the component view and now the component update area is reduced - if this function is commented out the component view do not disapears - just to show you that this is a large component with lots of different functions that totally change settings...

Setting the undo off $oExcel.EnableUndo = False

Remove the toolbar $oExcel.DisplayToolbar = False

Manual do the undo $oExcel.Undo ( there's an .beginundo / .endundo ) too, ( No visual trouble ), this tells me that, as the toolbar is an subobject this is where to look fo the trouble, maybe one has to initiate this manual but I don't really know...

I should mention that there's a lot of subobjects to control different parts of the main process like SpreadSheetEventInfo ec...

I droppped the compleatly reference to the OWC rev 9 component on the net at link:

http://www.fritidshjemmet.com/Temp/MSOWC_9.chm

Start under SpreadSheet, hopefully this can cast some light to the project - Good luck

kjactive

Ok thanks alot. but as a last note, can u tell me how I can disable the context menu of the OWC object. Thanks,

[kjactive]

Hallo Smahdi83

That's one of the hot spots as I recall, I just look it up ones more and there's not a command buildin to avoid the context menu.

The trouble is that a context menu has a high priority and blocks msg returns when visual, only keyboardstrokes has higher value here and ESC and SHift + 10 has interface to the context menu - a walk around would be to send a ESC to the application but as it blocks for msg that's not an option available...

Here's some events you can try:

Func Excel_Mousedown($EventInfo) 
    If $EventInfo.Button = 2 Then 
        $EventInfo.ReturnValue = False 
        Consolewrite("Right click menu invoked." & @CRLF)
    Endif
    Send("{ESC}")
EndFunc

Didn't work for me but!

Func Excel_Change($EventInfo)
    $oExcel.DisplayGridlines = False
    $oExcel.DisplayGridlines = True
EndFunc

a walk around that would update the editarea at every change like when done some edit from toolbar

and there is anouther vital event but a little harder to use

Func Excel_BeforeCommand($EventInfo) 
    If $EventInfo.Command = $Copy Then 
       Consolewrite("Copy is not allowed." & @CRLF)
        $EventInfo.ReturnValue = False 
    EndIf 
EndFunc

Related value related to the previous .Command is

$Calculate

$InsertRows

$InsertColumns

$DeleteRows

$DeleteColumns

$Cut

$Copy

$Paste

$Export

$Undo

$SortAscending

$SortDescending

$Find

$Clear

$AutoFilter

$Properties

$Help

You have to find the variable values somewhere in the topics and initiate these in startup before the component, read more at link:

http://msdn2.microsoft.com/en-us/library/A...office.11).aspx

Ofcourse one option more on a walk around tour would be to disable the right mousekey - best of luck...

kjactive