COM & ObjGet

[Timo]

I've tried and tried, but no success:

I have the file c:\temp\test.xls and I want to read from it some cells in an autoit array using ObjGet.

Can someone please post an example how to manage this?

The examples from the autoit help work fine, but I can't get data from a special file.

Thanks,

Timo

[PerryRaptor]

These posts should help...I'm a fan of Excel and made a slight modification that has little affect. I save my Excel workbooks with the file extension *.CSV allowing and easy transition to AutoIT comma-delimited multidimensional arrays.

http://www.autoitscript.com/forum/index.ph...&hl=perryraptor

http://www.autoitscript.com/forum/index.ph...&hl=perryraptor

[Timo]

Ok. You do it the ohter way. Without COM.

But you split your columns with "," or ";".

But I have cells which contain a "," and a ";".

So your script fails.

I think the only right way to do it is with COM...

But how?

[PerryRaptor]

Go through the posts again. Someone offered a COM example.

Another choice is to save your Excel Workbook as a tab-delimited mutli-dimensional array, *.TXT. replace code delimiter with "Chr(9)"

[Timo]

Good idea.

Worth thinking about it. Much better than the ,-delimiter or the ;-delimiter.

Hm...

[SvenP]

Good idea.

Worth thinking about it. Much better than the ,-delimiter or the ;-delimiter.

Hm...

<{POST_SNAPBACK}>

You could try something like this:

$FileName="C:\Temp\Test.xls"

if not FileExists($FileName) then
    Msgbox (0,"Excel File Test","Can't Open, because you didn't create the Excel file "& $FileName)
    Exit
endif

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

if IsObj($oExcelDoc) then

    $oDocument=$oExcelDoc.Worksheets(1);  Default worksheet
    $aArray=$odocument.range("A1:B3").value; Get values from given range 

    Msgbox (0,"","Number of dimensions: " & UBound($aArray,0) & @CRLF & " Size of first dimension: " & Ubound($aArray,1) & " Size of second dimension: " & Ubound($aArray,2))

; Note: you can't use a FOR..IN loop for a multi-dimensional array!
else
    Msgbox (0,"Excel File Test","Error: Could not open "& $FileName & " as an Excel Object.")
endif

Regards,

-Sven

[Timo]

Hi Sven,

that looks not bad. But sometimes I get an error from Excel.

I found a KiXtart-Script:

Break ON
 
$sMyDocument="d:\temp\test.xls"
 
; Create Excel application
$oExcel=CreateObject("Excel.Application")
 
; Uncomment the next line to make it visible
; $oExcel.Visible = 1
 
; Load the demo document
$oWorkbook=$oExcel.Workbooks.Open($sMyDocument)
 
; Get the active worksheet
$oWorksheet=$oWorkbook.ActiveSheet
 
; Iterate the cells in column A, stopping on the first blank cell
For Each $oCell in $oWorksheet.Columns("A:H").Cells
    $iCounter=$iCounter+1
    $sText=$oCell.Text
    If $sText="" Goto "CellLoopDone" EndIf
    $iCounter " " $sText ?  
Next
:CellLoopDone
 
; Clean up
$oExcel.Quit()
$oExcel=0

get $w
  
Exit 0

I will put the code in AutoIt and then it should work.

[Timo]

Hello PerryRaptor,

after a lot of tries I want to use your *.csv-version.

I followed your links, but all the scripts have the same problem which I cannot fix:

- if ; or , is used in a cell (e.g. test1;test2), Excel converts it to "test1;test2"

- if the cell contains something like "test", Excel converts it to """test"""

How do you manage this?

Do you have an idea how to change the script examples to make them work?

[PerryRaptor]

Did you try saving the Excel worksheet as a *.TXT file? Afterwards adjusting the two lines that StringSplit() on a comma " , " with Chr(9) ?

I didn't like tabbed-delimited version for my needs; and empty cell had been skipped over causing the data in that row to shift to the left.

Does a particular column have a uniform construct using comma's? May be we could add additional columns; breaking on each comma and correcting it afterwards.

;-----combine three cells into one-----

$Array_Parts = $Array[xxx][3] & "," & $Array[xxx][4] & "," & $Array[xxx][5]

;-----Overwrite $Array[xxx][3] with the combined three cells-----

$Array[xxx][3] = $Array_Parts

[Timo]

With the tabbed-delimited version I have the same problems like you.

So we can forget it.

The script has to proof whether the delimiter ( is in "" or not.

When it is in "" the script must not break at this point.

So I don't understand your combination of three cells in one array.

Do you have an idea how to do this check?

[ShelbySue]

I think I can help you with the COM solution if you will give me a sample xls file and tell me what you are looking for. (You don't have to give me sensitive data, just some mumbo-jumbo to read and put into an array for you.)

With the tabbed-delimited version I have the same problems like you.

So we can forget it.

The script has to proof whether the delimiter ( is in "" or not.

When it is in "" the script must not break at this point.

So I don't understand your combination of three cells in one array.

Do you have an idea how to do this check?

<{POST_SNAPBACK}>

[Timo]

Hello ShelbySue,

this would be very nice.

The file test.zip contains an excel file (test.xls) with some adress datas.

What I want to do:

- read the data from Excel and put it in array so that I can show it in a listview or something else

- change the data with autoit and put it back to Excel in the same xls-file.

Do you think there is a way to do it?

test.zip

[PerryRaptor]

I opened your test.xls file renamed it to test_csv and saved it a comma-delimited file through Excel. The file name is now "test_csv.csv." Use the "Save As..." feature in Excel and choose the "Save as Type..." CSV (Comma Delimited).

expandcollapse popup

#include <GUIConstants.au3>
#include <File.au3>

$in_filename = "test_csv.csv"
Dim $lines,$Display, $NumCols
_FileReadToArray($in_filename, $lines)
$Columns = StringSplit($lines[1], ",")
$NumCols=$Columns[0]
Dim $array[ $lines[0] ][ $Columns[0] ]
For $i = 1 To $lines[0]
    $Columns = StringSplit($lines[$i], ",")
    If $Columns[0] = 1 Then Continueloop
    For $j = 1 To $Columns[0]
        $array[$i-1][$j-1] = $Columns[$j]
    Next
Next
$Rows=$Lines[0]-1
For $i = 1 To $lines[0]-1
;---------------------------------------------------------------------
;Display Entire CSV File converted to a multi-dimensional Array
;---------------------------------------------------------------------
  For $j = 1 To $NumCols
  $Display  = $Display&"array["&String($i-1)&"]["&String($j-1)&"]="&chr(9)&$array[$i-1][$j-1]&@CRLF
  Next
Next
MsgBox(0,"Entire test.csv file", $Display)
;---------------------------------------------------------------------
;Get the contents of Column A for every row and Add a "|" at the end 
;of each item so that GuiCtrlCreateCombo works properly
;---------------------------------------------------------------------
Dim $ColumnA, $AllColumnA, $H_cmbA, $H_ComboBox, $H_Return
$M = 0
While $m <= $Rows -1
    $ColumnA=$Array[$m][0] & "|"
    $m = $m + 1
    $AllColumnA=$AllColumnA & $ColumnA
Wend
;---------------------------------------------------------------------
;Display the contents of Column A in a GuiCtrlCreateCombo
;  Display Column B contents in an GuiCtrlCreateLabel
;  Display Column C contents in a GuiCtrlCreateLabel 
;---------------------------------------------------------------------
GUICreate("ComboBox View",600,100,-1,40)
$h_cmbA = GUICtrlCreateCombo("", 10,10,90)
GUICtrlSetData(-1,$AllColumnA)
GUISetState (@SW_SHOW)

While 1
    $msg = GUIGetMsg()
    Select
    case $msg = $GUI_EVENT_CLOSE
        ExitLoop
    case $msg = $h_cmbA
        GuiCtrlCreateLabel(_GUICtrlComboBoxIndex($h_cmbA),110,13,90,30)
        GuiCtrlCreateLabel($Array[$H_Return][2],200,13,130,30)
        GuiCtrlCreateLabel($Array[$H_Return][3],340,13,130,30)
        GuiCtrlCreateLabel($Array[$H_Return][4],480,13,130,30)
    endselect
Wend
;---------------------------------------------------------------------
;Function used with "Display the contents of Column A in a ComboBox"
;  Returns the row (aka Line Number) for the item selected
;  Returns the data contained in Columns B, C, and D
;---------------------------------------------------------------------
Func _GUICtrlComboBoxIndex($H_ComboBox)
    Dim $CB_GETCURSEL = 0x147
    $H_Return = GUICtrlSendMsg ( $H_ComboBox, $CB_GETCURSEL , 0, 0 )
    $H_ComboBox = $Array[$H_Return][1]
    Return $H_ComboBox
    Return $H_Return
EndFunc

[Timo]

But there is still the problem with a ',' or a ';' in a cell.

That´s why we tried it with COM....

[PerryRaptor]

your test example did not contain any " , " or " ; " characters

[Timo]

That´s right.

But you don´t know which data a user puts in when he uses the software.

So a script has always to run and not to fail when a user writes an "," i a cell.

[PerryRaptor]

How about not using Excel at all? Create a user input script that uses FileWriteLine() function and use a non-keyboard character such as Chr(175) as the delimiter.

[Timo]

Hi ShelbySue,

where are you?

You wanted to help us with COM.

Are you lying in the sun?

[SvenP]

Hi ShelbySue,

where are you?

You wanted to help us with COM.

Are you lying in the sun?

<{POST_SNAPBACK}>

I guess it takes ShelbySue long because there was a bug in AutoIt beta version 3.1.1.18 and older: When using Arrays in COM functions, the returned Array started from index 0. But to write an Array back, it had to start from index 1.

This was fixed in version 3.1.1.19, and here is an example script, using your TEST.XLS file in directory C:\TEMP:

expandcollapse popup

; ExcelDataTest.AU3
; 
; Retrieves information from an Excel sheet
; Modifies the data
; Returns the modified data in back in the sheet
;
; Requires AutoIt beta version 3.1.1.19 or higher !


$FileName="C:\temp\test.xls"    ; Change this to the path/name of your Excel sheet
$CellRange="A1:E3"              ; Change this to the range of cells you want to modify

if not FileExists($FileName) then; Just a check to be sure..
    Msgbox (0,"Excel Data Test","Error: Can't find file " & $FileName)
    Exit
endif

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

If (not @error) and IsObj($oExcelDoc) then      ; Check again if everything went well
                                                ; NOTE: $oExcelDoc is a "Workbook Object", not Excel itself!    
    $oDocument=$oExcelDoc.Worksheets(1)         ; We use the 'Default' worksheet
    
    $aArray=$odocument.range($CellRange).value  ; Retrieve the cell values from given range 

; The data should now be in the 2-dimensional array: $aArray
    
    If IsArray($aArray) and Ubound($aArray,0)>0 then; Check if we retrieved any data
        
    ; The next lines are just for debugging purposes, it puts all cell values into
    ; a string to display in a MsgBox.
    ; Note: you can't use a FOR..IN loop for a multi-dimensional array!
    
        $string=""
        for $x=0 to ubound($aArray,1)-1
            for $y=0 to ubound ($aArray,2)-1
                $string=$string & "(" & $x & "," & $y & ")=" & $aArray[$x][$y] & @CRLF
            Next
        Next
        Msgbox(0,"Excel Data Test","Debug information: Read Cell contents: " & @CRLF & $string)


    ; Now we modify the data
        $aArray[0][0]="This is cell A1"
        $aArray[1][1]="This is cell B2"
        
        $odocument.range($CellRange).value  = $aArray; Write the data back in one shot
        
        $oExcelDoc.Windows(1).Visible = True        ; Otherwise the worksheet window will be saved 'hidden'
        $oExcelDoc.Save                             ; Save the workbook

    Else
        Msgbox (0,"Excel Data Test","Error: Could not retrieve data from cell range: " & $CellRange)
    EndIf
    
    $oExcelDoc.saved=1          ; Prevent questions from excel to save the file
    $oExcelDoc.close            ; Get rid of Excel.
Else
    Msgbox (0,"Excel Data Test","Error: Could not open "& $FileName & " as an Excel Object.")
Endif

Regards,

-Sven

[Timo]

Wow.

That's it, Sven.

Fantastic, we have a host of possibilities...

I go programming...

Thanks a lot.