Read Excel Line, Then Move to Next Row w/ Changing Variables

[bballjoe12]

I have an excel file that will always contain 5 columns. Row 1 will always contain headers. The thing that will change are the # rows. What I am trying to figure out is how do I get my message box (which for an example, I am okay with using a Message Box for now just to get it working) to display the values in the 1st row, then it would move to the next row and display the values in the next row. If my Excel spreadsheet contains 50 rows, I know there is a way for my Message Box to change each time so I don't have to manually assign my variables. Here is my code:

FileSelectFile, Path, , Open Workbook, All Excel Files (*.xl; *.xlsx; *.xlsm; *.xls)
if ErrorLevel = 1
Return
SplitPath, Path, ExcelFileName

Xl := ComObjCreate("Excel.Application") ;create a handle to a new excel application
Xl.Workbooks.Open(Path) ;open file
Xl.Visible := True

Num_Rows(PXL)
{
Return, PXL.Application.ActiveSheet.UsedRange.Rows(PXL.Application.ActiveSheet.UsedRange.Rows.Offset.Count-1).Row
}
UsedRowsNum := % Num_Rows(XL)

Loop, %UsedRowsNum% ; loop through # of rows starting on row 2
{
a%a_index% := Xl.Range("A" . a_index).Offset(1,0).Value ;get data from column A
b%a_index% := Xl.Range("B" . a_index).Offset(1,0).Value ;get data from column B
c%a_index% := Xl.Range("C" . a_index).Offset(1,0).Value ;get data from column C
d%a_index% := Xl.Range("D" . a_index).Offset(1,0).Value ;get data from column D
e%a_index% := Xl.Range("E" . a_index).Offset(1,0).Value ;get data from column E
}

MsgBox %a1%|%b1%|%c1%|%d1%|%e1%


My message box that I currently have as an example in the above code does show what I have in row 1 but I may have 40 rows one time and the next time I use my Excel file, I could have 10 rows. How do I get my message box to display what I will have in each subsequent row, with each row displaying its own contents in its own message box?

[flyingDman]

The simplest form would be something like this:

Code: Select all

file2open := "C:\Users\xxxx\Documents\Spreadsheets\test 123.xlsx"		
XL := ComObjCreate( "Excel.Application" )
XL.Workbooks.Open(file2open)

XL.sheets(1).Usedrange.copy
Loop, Parse, clipboard, `r, `n
	msgbox % a_loopfield

To skip the first row add something in the loop like this: if a_index =1, continue

[bballjoe12]

Would there be a way to increase my variable # each time, such as....

MsgBox, %a1%|%b1%|%c1%|%d1%|%e1%
MsgBox, %a2%|%b2%|%c2%|%d2%|%e2%
MsgBox, %a3%|%b3%|%c3%|%d3%|%e3%

and so on, according to the # of rows I have? I tried the below code but the message box just showed blank:

Loop, %UsedRowsNum%
{
RowByRow:=a%a_index%|b%a_index%|c%a_index%|d%a_index%|e%a_index%|
MsgBox %RowByRow%
}

[bballjoe12]

I actually got it figured out! It's kind of ugly, but hey it works!

FileSelectFile, Path, , Open Workbook, All Excel Files (*.xl; *.xlsx; *.xlsm; *.xls)
if ErrorLevel = 1
Return
SplitPath, Path, ExcelFileName

Xl := ComObjCreate("Excel.Application") ;create a handle to a new excel application
Xl.Workbooks.Open(Path) ;open file
Xl.Visible := True

Num_Rows(PXL)
{
Return, PXL.Application.ActiveSheet.UsedRange.Rows(PXL.Application.ActiveSheet.UsedRange.Rows.Offset.Count-1).Row
}
UsedRowsNum := % Num_Rows(XL)

Loop, %UsedRowsNum% ; loop through # of rows starting on row 2
{
a%a_index% := Xl.Range("A" . a_index).Offset(1,0).Text ;get data from column A
b%a_index% := Xl.Range("B" . a_index).Offset(1,0).Text ;get data from column B
c%a_index% := Xl.Range("C" . a_index).Offset(1,0).Text ;get data from column C
d%a_index% := Xl.Range("D" . a_index).Offset(1,0).Text ;get data from column D
e%a_index% := Xl.Range("E" . a_index).Offset(1,0).Text ;get data from column E
}

Loop, %UsedRowsNum%
{
ColA:=a%a_index%
ColB:=b%a_index%
ColC:=c%a_index%
ColD:=d%a_index%
ColE:=e%a_index%
RowValuesLineByLine = %ColA%%ColB%%ColC%%ColD%%ColE%
MsgBox %RowValuesLineByLine%
}


Thank you for your help, though!

[flyingDman]

This might be a little cleaner. It also puts all cells in an associative array which is a good practice and allows all kinds of manipulations. BTW the total count of rows in the used range is xl.Activesheet.UsedRange.rows.count.

Code: Select all

file2open := "C:\Users\xxxx\Documents\Spreadsheets\test 123.xlsx"		
arr := {}
XL := ComObjCreate( "Excel.Application" )
XL.Workbooks.Open(file2open)

For c In xl.ActiveSheet.UsedRange.cells
	arr[c.address(0,0)] := c.Value

loop, % xl.Activesheet.UsedRange.rows.count
	msgbox % arr["a" a_index] arr["b" a_index] arr["c" a_index] arr["d" a_index] arr["e" a_index]

msgbox % "the content of cell B3 is " arr["b3"]

[flyingDman]

Or:

Code: Select all

file2open := "C:\Users\xxxx\Documents\Spreadsheets\test 123.xlsx"		
arr := {}
XL := ComObjCreate( "Excel.Application" )
XL.Workbooks.Open(file2open)

For c In xl.ActiveSheet.UsedRange.cells
	varA := c.address(0,0), %varA% := c.Value	

loop, % xl.Activesheet.UsedRange.rows.count
	msgbox % a%a_index% " " b%a_index% " " c%a_index% " " d%a_index% " " e%a_index% 

msgbox % "the content of cell B3 is " b3

[aifritz]

or:

Code: Select all

file2open := "C:\Users\xxxx\Documents\Spreadsheets\test 123.xlsx"
xl := ComObjCreate( "Excel.Application" )
xl.Workbooks.Open(file2open)
xl.Visible := True

For r In xl.ActiveSheet.UsedRange.rows ;loop all rows
  {
    content := ""
    For c In xl.ActiveSheet.UsedRange.columns ;loop all columns
      {
        If (r.row = 1) ;get header from frist row, delimited with tab
          header .= xl.cells(r.row, c.column).value "`t"
        else ;get content, delimited with tab
          content .= xl.cells(r.row, c.column).value "`t"
      }
    If (r.row > 1) ;show content, when header was determined
      msgbox, % Trim(header, "`t") "`n" Trim(content, "`t")
  }
xl := ""
return

Edit: Added xl.Visible := True, xl :=""

[Datapoint]

Both of you: 1) create com objects 2) don't make them visible 3) don't close them 4) don't clear global com vars.

[Odlanir]

Or, with any number of columns

Code: Select all

file2open := a_scriptdir "\Test999.xls"		
XL := ComObjCreate( "Excel.Application" )
xl.displayalerts := false
XL.Workbooks.Open(file2open)
loop, % xl.Activesheet.UsedRange.rows.count {
   if ( a_index = 1 )
      continue      
   XL.rows[a_index].copy
   msgbox % RegExReplace(Clipboard, "`t")
}   
xl.quit
xl := ""
ExitApp

*esc::
   xl.quit
   ExitApp

[aifritz]

Thank you Datapoint for your hint

Here a version, showing the data in a ListView:

Code: Select all

setbatchlines, -1
file2open := "C:\Users\xxxx\Documents\Spreadsheets\test 123.xlsx"
xl := OpenExcelFile(file2open) ;if excel file is already opened just get handle and activate window

For r In xl.ActiveSheet.UsedRange.rows ;loop all rows
  {
    content := {}
    For c In xl.ActiveSheet.UsedRange.columns ;loop all columns
      If (r.row = 1) ;get header from frist row, delimited with | for LV
         header .= xl.cells(r.row, c.column).text "|"
      else ;get content
         content.Push(xl.cells(r.row, c.column).text)

    If (r.row = 2) ;show content in Listview, when header was determined
      {
        Gui, +Lastfound
        GuiHWND := WinExist()   ;Retrieves the unique ID number (HWND/handle) of a window to control process flow
        Gui, Font,s10, Arial
        Gui, Add, ListView, w10 vVLV +hwndHLV C0000FF Grid r1, %header%  ;create listview with header
        LV_Add("", content*) ;put content to listview first time
        Gui, Add, Button, w200      y80 gNextRow +default, Show next row
        Gui, Add, Button, w200 x300 y80 gCancel, Cancel

        ;https://autohotkey.com/board/topic/88667-adjust-listview-area-to-total-of-column-widths/
        LVM_GETITEMRECT := 0x100E
        Loop, % xl.ActiveSheet.UsedRange.columns.count ;adjust column widths
           LV_ModifyCol(A_Index, "AutoHdr")
        VarSetCapacity(RECT, 16, 0)
        SendMessage, LVM_GETITEMRECT, 0, &RECT, , ahk_id %HLV%
        W := NumGet(RECT, 8, "Int")  ; 4 needs to be adjusted if the ListView has a vertical scroll
        GuiControl, Move, VLV, w%W%
        Gui, Show, Autosize

        WinWaitClose, ahk_id %GuiHWND%        ; Waiting for Gui to close
      }
    else if (r.row > 2)
      {
        LV_Modify("",, content*) ;change content of existing Listview
        Loop, % xl.ActiveSheet.UsedRange.columns.count
           LV_ModifyCol(A_Index, "AutoHdr")
        Gui, Show, Autosize
        WinWaitClose, ahk_id %GuiHWND%        ; Waiting for Gui to close
      }
  }
Cancel:
  xl := content := "" ;I think, when exitapp follows, these vars must not be resetted
  exitapp
return

NextRow:
   Gui, Hide
return

;Open Excel with given file or just activate if file is already open
;returns a handle to xl application
OpenExcelFile(file2open)
  {
    SetTitleMatchMode, 2
    SplitPath, file2open,,,,xlWinTitle
    if WinExist(xlWinTitle)
      {
        WinActivate, %xlWinTitle%
        Sleep 50
   	  ControlSend, Excel71, {esc}, ahk_class XLMAIN ;ensure that F2 modus isn't active
        Sleep 50
        xl := ComObjActive("Excel.Application")
      }
    else
      {
        xl := ComObjCreate("Excel.Application")
        xl.Workbooks.Open(file2open)
      }
    xl.Visible := True
    WinActivate, %xlWinTitle%
    return xl
  }

Edit:
Changed xl.cells(r.row, c.column).value to xl.cells(r.row, c.column).text, otherwise format in Listview isn't the same as in Excel

[bballjoe12]

Thank you all for your help! All great information!

[flyingDman]

Both of you: 1) create com objects 2) don't make them visible 3) don't close them 4) don't clear global com vars.

Thank you for reminding us. I usually only include in the posted code that what is pertinent to the issue at hand and skip opening and closing lines. It should be well understood that these are not full scripts.
The one thing that puzzles me is, why we would need to make them visible (I presume stating "Xl.Visible := True" ?) . Unnecessary and often unwanted in my view.
It seems, to those that forget to clean up after themselves, that rebooting the solution. Before doing that, i suggest to run a simple script like this

Code: Select all

Runwait, taskkill /im excel.exe /f,, Hide

That should kill any and all instances of Excel. Although this is a more forceful approach than "process, close" (which would need to put in a loop until errorlevel = 0), I use it all them time with no issues to report.

[Datapoint]

The one thing that puzzles me is, why we would need to make them visible (I presume stating "Xl.Visible := True" ?) . Unnecessary and often unwanted in my view.

Yeah, code posted in answers in the Ask For Help forums should be assumed to be part of a larger script and the suggestions should be narrowly focused on the subject of the topic at hand. So, it shouldn't be required to write a standalone script including all the best practices every time you post code on the forums. On the other hand...

Yes, Xl.Visible := True is often unwanted, but if we post code that does not include it then other users who read this thread can get themselves into trouble. For instance, if we create an excel com object and add a workbook and then close the script, then excel will stay open in the background. You can kill the process with Task Manager, or by other means, but most people, especially new users, will not check the Task Manager. It's fairly common to see posts about problems related to this, for example: https://www.autohotkey.com/boards/viewtopic.php?p=282335#p282335

If the excel process stays open then it can impact the use of ComObjActive because ComObjActive does not work well if there is more than one process.

If we use global variables that have the same name in other subroutines then we may need to consider that. It's just easier if you know that the var is unique. That's why most of the com stuff I do is not global; it returns and then clears everything when it's done.

If all of the references to com objects are not cleared then the process cannot close.

I often suggest (and I've seen others do the same) that users should make the excel application visible when testing code, and they should only make it invisible once they are sure their code always quits excel successfully. (even if the script crashes) Think about it this way: If you make excel visible, does the window disappear when the script ends? There is no difference when excel is invisible, except that you can't close the window because it is invisible, and you may not even know that excel is still running because the window isn't shown.

Often during testing you will encounter errors that force the script to quit before the script can close excel. The excel process can stay open in the background.

*Edited