Excel Grid in AutoIT

[ptrex]

@NumberDaemon

Sorry, but I don't understand your question ?

What exactly is the problem ?

regards,

ptrex

[Eigensheep]

I think I've worked it out now, but the problem is that I can only use functions such as $oExcel.Cells(r,c).Value and not $oExcel.WorksheetFunction.Sum(range) to edit the spreadsheet. Is this because the object is started as an OWC spreadsheet and not an Excel worksheet?

Don't get me wrong though, it's a great script.

[ptrex]

@NumberDeamon

According to me thare is no problem using Ranges as well a calculations in the OWA spreadsheet.

Some examples

; Calculate the cels B4 & B5
   $oExcel.cells(20,1).value = "=B4+B5"
   MsgBox (0,"",$oExcel.cells(20,1).value)
   
   ; Set a variable to cell B5 on Sheet1.
   $rngRandomNum = $oExcel.Worksheets("Sheet1").Range("B22")
   ; Insert a formula into cell B5.
   $rngRandomNum.Formula = "=5*RAND()"

Regards

ptrex

[DaRam]

Just to clarify, this activex control (OWC) is not Outlook Web Control (as posted earlier) but "Office Web Control".

1. You can load CSV data:

$OWC_SS.CSVData = "Comma Seperated Values"

2. Load Data from an ADO Recordset:

$OWC_SS.Range("A2").ParseText adoRs.GetString(2, #Rows (-1 or Skip for all), "Column Delimiter", "Row Delimiter", NullExpression), TextQualifier (Col. Delim)

2. Change the Toolbars

$OWC_SS.TitleBar.Font.Size = Number

$OWC_SS.TitleBar.Font.Color = ColorValue

$OWC_SS.TitleBar.Interior.Color = ColorValue

$OWC_SS.TitleBar.Caption = "Custom Caption"

$OWC_SS.DisplayColHeaders = False ; Default True

$OWC_SS.DisplayRowHeaders = False ; Default True

3. Format Individual Cells

$OWC_SS.Range("A1").Font.Bold = True

$OWC_SS.Range("A1").Interior.Color = "crimson" ; <- Use HTML Color Names !

$OWC_SS.Range("A1").Font.Color = 16777215 ; White

$OWC_SS.Range("A1").Format = "Number"

$OWC_SS.Range("A1").HAlignment = 1 ; Align Left, 2=Center, 3 = Right

4. Export the Displayed Data

$OWC_SS.ActiveSheet.Export "Drive_Path_FileName.XLS", 0 ; (0 = ssExportActionNone, 1 = ssExportActionOpenInExcel)

Note that the exported file which can be loaded into excel is actually a web page !

And much more. As another poster wrote, check the Help file (Under C:\Program Files\Common Files\Microsoft Shared\Web Components\<version#>\)

[ptrex]

@DaRam

Thanks for the contribution !!

Office Web Control

Actually it is Office Web Components

Regards,

ptrex

[DaRam]

Actually it is Office Web Components

Regards,

ptrex

Now I feel like an idiot.

Thanks for catching that, I did wanted to stress that you do not get the OWC libraries when you install Outlook, it is part of MS Office.

[S0789300]

GuiCreate("Excel Object", 802, 590,(@DesktopWidth-802)/2, (@DesktopHeight-590)/2 , $WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPSIBLINGS)

GuiCreate("Excel Object", 802, 590,(@DesktopWidth-802)/2, (@DesktopHeight-590)/2 , ^ ERROR

[S0789300]

Im assuming my OCW is messed - ill reinstall. Does anyone have a screenshot of this working. I would like to see it before I integrate this into my script.

Rgds

[Michel Claveau]

Can you access the object as a regular COM object? I don't seem to be able to use it for anything.

Hi!

Since Office-2007, Microsoft do not give OWC.

And MS not guarantee the "run OK", for this old componant, after 2010.

Choose your risks...

[robertocm]

Many Thanks for describing this technique.

Here an example using ADODB to extract data from a MS Access .mdb file and placing the results into the Excel OWC Spreadsheet object.

expandcollapse popup

#include "Array.au3"
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>

$hGui = GUICreate("Form OWC ADO Access", 1000, 600, -1, -1, $WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPCHILDREN )

Global $KeyW = GUICtrlCreateInput("", 3,2, 140, 30)
GUICtrlSetBkColor(-1, 0xDDFFDD)
Global $ActiveCellVal = GUICtrlCreateButton("Active Cell Value", 170, 2, 120, 30)
;MEMO
Global $iMemo
$iMemo = GUICtrlCreateEdit("", 290, 2, 600, 30)
GUICtrlSetBkColor(-1, 0xDDFFDD)
GUICtrlSetFont(-1, 10)

GUICtrlSetState($KeyW, $GUI_FOCUS)

$Obj1 = ObjCreate("OWC11.Spreadsheet")
;$Obj1.visible=1
$Obj1_ctrl = GUICtrlCreateObj($Obj1,2, 46, 994,600)

With $Obj1
   .DisplayToolbar = 0
   .AutoFit=0
   .ActiveWindow.DisplayColumnHeadings = True
   .ActiveWindow.DisplayRowHeadings = True
   .DisplayWorkbookTabs = False
   .DisplayGridlines = -1
   .DisplayHorizontalScrollBar = 0 ;-1
   .DisplayTitleBar = 0
   .DisplayVerticalScrollBar = 0 ;-1
   .EnableAutoCalculate = 0 ;-1
   .EnableEvents=-1
   .MoveAfterReturn=-1
   .MoveAfterReturnDirection=0
   .RightToLeft=0
   .ViewableRange="A1:F50"
   ;.Range("B1:C10").HorizontalAlignment = .Constants.xlHAlignCenter
   .Range("B1:C10").NumberFormat = "@"
   .Columns("A").ColumnWidth = 60
   .Columns("B:C").ColumnWidth = 36
   ;$Obj1.visible=1
EndWith

GUISetState(@SW_SHOW)

GUIRegisterMsg($WM_COMMAND, "WM_COMMAND")

While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
         Case $GUI_EVENT_CLOSE
            Exit
         Case $KeyW
            OnKeyW()
         Case $ActiveCellVal
            MemoWrite($Obj1.ActiveCell.Value)
    EndSwitch
WEnd

; --------------- Functions ---------------
Func OnKeyW()
Global $sSQL
Global $iCursorType = 0  ;0 adOpenForwardOnly, 1 adOpenKeyset
Global $iLockType = 3 ;1 adLockReadOnly, 3 adLockOptimistic
Global $iOptions = 1 ; Options, 1 Evaluates as a textual definition of a command or stored procedure call
Global $cn = ObjCreate("ADODB.Connection") ; Create a connection object
Global $rst = ObjCreate("ADODB.Recordset") ; Create a recordset object
Global $sFilename = @ScriptDir & "\MyAccessFile.mdb"
Global $cString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $sFilename & ";Jet OLEDB:Database Password=mypassword;" ;Mode=Read
;Global $cString = 'DRIVER={Microsoft Access Driver (*.mdb)};Dbq=' & $sFilename & ';uid=;pwd=mypassword;'
$cn.Open($cString) ; Open the connection

Local $sAnswer = GUICtrlRead($KeyW)
GUICtrlSetData($KeyW, "")

$tblname = "MyTableName"
$w = " WHERE MyFieldName LIKE '%" & $sAnswer & "%'"
$sSQL = "SELECT * FROM " & $tblname & $w
$rst.Open($sSQL, $cn, $iCursorType, $iLockType, $iOptions) ;Issue the SQL query

With $Obj1
  ;first option, using the CopyFromRecordset method of the Range object
  ;.ActiveSheet.Cells.copyFromRecordset($rst)
  ;.ActiveSheet.Cells.copyFromRecordset($rst, 50) ;MaxRows parameter limits returned records
  ;.range("A1:F5").CopyFromRecordset($rst) ;also establish a limit, because the range takes only the first 5 records from the recorset

  .ActiveSheet.Cells.ClearContents

  ;A better option is to create an array from recordset, and then fill a range from that array
  ;This seems more flexible to keep column widths or to add additional information in other areas of the spreadsheet
  Local $sArray[] = ["An", "example", "of", "first", "row", "values"]
  .Range("A1:F1").Value = $sArray

  With $rst
    For $i = 0 To .Fields.Count - 1
    $Obj1.Cells(2, $i + 1) = .Fields($i).Name   ;copy field names to the first row
    Next
       #cs  commented because GetRows is easier
       Local $rstArray[1000][6]
       $f = 0
       While Not .EOF ;looping through the recordset until End-Of-File (EOF) is reached
         $rstArray[$f][0]=.Fields(0).Value
         $rstArray[$f][1]=.Fields(1).Value
         $rstArray[$f][2]=.Fields(2).Value
         $rstArray[$f][3]=.Fields(3).Value
         $rstArray[$f][4]=.Fields(4).Value
         $rstArray[$f][5]=.Fields(5).Value
         .MoveNext ; Move To the Next record
         $f = $f + 1
         ReDim $rstArray[$f][6]
       WEnd
       #ce
  EndWith
  Local $rstArray = $rst.GetRows()
  $rst.Close ; Close the recordset
  $RecCount = UBound($rstArray)
  _ArrayTranspose($rstArray)
  .Range("A2:F2").Font.Bold = True
  .Range("A3:F" & $RecCount + 2).value = $rstArray ;+2 because we start 2 rows (+2) from the row 1 ('A1')

Endwith

$rst.Close ; Close the recordset
$rst = 0 ; Release the recordset object
$cn.Close ; Close the connection
$cn = 0 ; Release the connection object

EndFunc

Func MemoWrite($sMessage)
   GUICtrlSetData($iMemo, $sMessage, "")
EndFunc

 

[R0G]

How do i set focus to the OWC11.Spreadsheet.11 object?

 $oExcel.Worksheets($oExcel.ActiveSheet.Name).Range("E1").End(-4121).Offset(1, 0).Select

i use the following code to select the last empty cell in column e. This creates a light blue line around the selected cell (Object is still not selected)

ControlFocus("My Program","","ATL:54011D181")

This successfully sets focus to the object using ClassnameNN.

The problem is that the Class, ClassnameNN and Advanced Mode all change.

ControlGetHandle("My Program","",$oExcel) = Nothing

_WinAPI_GetClassName($GUI_ActiveX) = Nothing

Global $oExcel = ObjCreate("OWC11.spreadsheet"); Office 2003

Global $GUI_ActiveX = GUICtrlCreateObj ($oExcel, 265, 217 , 863 , 376)

This is how i create the OWC Object.

Additional Info:

The name of the Object: Spreadsheet
Description string of the Object: Microsoft Office Spreadsheet 11.0
The ProgID of the Object: OWC11.Spreadsheet.11
CLSID of the object's coclass: {0002E559-0000-0000-C000-000000000046}
IID of the object's interface: {CBFDF42F-DB84-428E-91B4-E0D369C0057A}

Edited August 18, 2016 by R0G

[R0G]

On 8/17/2016 at 8:36 PM, R0G said:

How do i set focus to the OWC11.Spreadsheet.11 object?

 $oExcel.Worksheets($oExcel.ActiveSheet.Name).Range("E1").End(-4121).Offset(1, 0).Select

i use the following code to select the last empty cell in column e. This creates a light blue line around the selected cell (Object is still not selected)

ControlFocus("My Program","","ATL:54011D181")

This successfully sets focus to the object using ClassnameNN.

The problem is that the Class, ClassnameNN and Advanced Mode all change.

ControlGetHandle("My Program","",$oExcel) = Nothing

_WinAPI_GetClassName($GUI_ActiveX) = Nothing

Global $oExcel = ObjCreate("OWC11.spreadsheet"); Office 2003

Global $GUI_ActiveX = GUICtrlCreateObj ($oExcel, 265, 217 , 863 , 376)

This is how i create the OWC Object.

Additional Info:

The name of the Object: Spreadsheet
Description string of the Object: Microsoft Office Spreadsheet 11.0
The ProgID of the Object: OWC11.Spreadsheet.11
CLSID of the object's coclass: {0002E559-0000-0000-C000-000000000046}
IID of the object's interface: {CBFDF42F-DB84-428E-91B4-E0D369C0057A}

For people interested in a way to get focus/ClassnameNN from an object that it's Class, ClassnameNN or Advanced mode change use the following code:

Func Find_Focus()
    $Mouse_POS = MouseGetPos()                                  ;get mouse position
    $POS = WinGetPos("Your GUI Here")                           ;get GUI position
    $X = $POS[0] + ($POS[2] /2)                                 ;object X position
    $Y = $POS[1] + ($POS[3] /2)                                 ;object Y position
    MouseClick("Left",$X,$Y,1,0)                                ;move mouse to object position and left click on object
    $Spreadsheet_Control = ControlGetFocus("Your GUI Here")     ;get object focus
    MouseMove($Mouse_POS[0],$Mouse_POS[1],0)                    ;Restore mouse position
EndFunc

 

Edited September 6, 2016 by R0G