Would like to read all rows in all sheets of Excel file via COM

[Costas]

All,

I experimented with AHK access to Excel via COM a couple years ago.

I now have been given a large Excel file that has many sheets.

I would like to read all rows in all sheets via AHK (COM).

Does anyone have an example of how to do this?

I just need to read the data, no updating.

Thanks,
Costas

[Nightwolf85]

What format would you like the data to be in? each cell its own variable? each row its own array or object containing 'columns' of data?

You could loop through all the sheets of a workbook by following a similar approach as below:

Code: Select all

Path := "PATH to Excel File" ; could make this a GUI, Input, or FileSelectFile
XL := ComObjCreate("Excel.Application")
;XL.Visible := False
XL_Workbook := XL.Workbooks.Open(Path)
Loop % XL_Workbook.Worksheets.Count()
{
    XL_WorkSheet := XL_Workbook.Worksheets(A_Index) ; I believe Excel indexes start at 1 so this should work to get all.
 ; Get all data from the worksheets.
}
XL_Workbook.Close()
XL.Quit()
XL := ""

Hope that gets you started, also you should check out the guides from this post.
https://autohotkey.com/boards/viewtopic.php?t=8978

[kon]

Code: Select all

Loop % XL_Workbook.Worksheets.Count()

A for-loop works here too.

for MySheet, in XL_Workbook.Worksheets

[Nightwolf85]

A for-loop works here too.

That is good to know, thank you.

Much of what I've learned about COM has been from reading your posts, Thank you!

[kon]

[Costas]

Nightwolf85,

Thanks for the help. You asked what format I would like the data to be in. I would like to see each cell in Col-A returned into a variable. I tried the code you supplied and it worked nicely to loop thru all of the sheets in the Excel file. Now I would like to see how to return the detail data (each row under column A) for each of the sheets.

Thanks Much,
Costas

[kon]

To loop through each non-blank cell in column A, see the second example from this post (The example with the for-loop). Once you have a reference to the cell in column A (The MyCell variable in the post I linked to), you can then get all the non-blank cells in the Row the same way you got the non-blank cells in the column; just use .End(xlLeft) instead of .End(xlUp).

MyCell.Row returns the row number of the cell. (Actually, 'MyCell' is a Range object, so all of these methods and properties are available for use.)

[Costas]

Thanks to both of you for the assistance. I now have a working script that I can use.

I appreciate the help.

Costas

[Guest]

I now have a working script that I can use.

Can you post the code please?

[Costas]

Dear Guest,

Here is my code to loop thru all sheets in a test Excel file and to retrieve data from each of the sheets.

Costas

;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Path = C:\Test.xlsx

XL := ComObjCreate("Excel.Application")

XL_Workbook := XL.Workbooks.Open(Path)

Loop % XL_Workbook.Worksheets.Count()
{
XL_WorkSheet := XL_Workbook.Worksheets(A_Index)
GoSub, ProcessOneSheet
}

XL_Workbook.Close()
XL.Quit()
XL := ""
msgbox EOJ`n`n Salute to IBM Mainframes
EXITAPP

;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

ProcessOneSheet:

MyRow := 1

LastRow := XL_Workbook.Sheets(A_Index).UsedRange.rows.count

Loop1:

MyRange = A%Myrow%

CellA1Value := XL_Workbook.Worksheets(A_Index).Range(MyRange).Value

msgbox Sheet Index = %A_Index% `n`nLastRow = %LastRow% `n`nMyRow = %MyRow% `n`nMyRange = %MyRange%`n`n`n`n Cell Value = %CellA1Value%

MyRow := MyRow + 1

If MyRow > %LastRow%
{
Return
}

GOTO, Loop1


;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[Guest]

Thanks for sharing Costas.
Another example (untested):

Code: Select all

Path := "C:\Test.xlsx"
XL := ComObjCreate("Excel.Application")
XL_Workbook := XL.Workbooks.Open(Path)

for XL_WorkSheet, in XL_Workbook.Worksheets
{
    SheetNumber := A_Index
    
    ;~ LastRow := XL_WorkSheet.UsedRange.rows.count
    CellA1 := XL_WorkSheet.Cells(1, 1)
    
    LastCell := XL_WorkSheet.Cells(xlApp.Rows.Count, 1).End(-4162)  ; xlUp = -4162
    
    ;~ for c, in XL_WorkSheet.Range("A1:A" LastRow)
    for c, in XL_WorkSheet.Range(CellA1, LastCell)
    {
        MsgBox, % "Sheet: "         XL_WorkSheet.Name   "`n"
                . "SheetNumber: "   SheetNumber         "`n"
                . "LastRow: "       LastRow             "`n"
                . "c.Row: "         c.Row               "`n"
                . "Cell Value: "    c.Value
    }
}
return