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

Get help with using AutoHotkey and its commands and hotkeys
bballjoe12
Posts: 113
Joined: 20 Aug 2017, 09:11

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

29 Jun 2019, 21:37

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?
User avatar
flyingDman
Posts: 291
Joined: 29 Sep 2013, 19:01
Location: Burbank, California

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

29 Jun 2019, 21:53

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
Posts: 113
Joined: 20 Aug 2017, 09:11

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

29 Jun 2019, 22:28

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
Posts: 113
Joined: 20 Aug 2017, 09:11

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

29 Jun 2019, 23:24

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!
User avatar
flyingDman
Posts: 291
Joined: 29 Sep 2013, 19:01
Location: Burbank, California

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

29 Jun 2019, 23:38

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"]

User avatar
flyingDman
Posts: 291
Joined: 29 Sep 2013, 19:01
Location: Burbank, California

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

30 Jun 2019, 00:27

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
Posts: 145
Joined: 29 Jul 2018, 11:30
Location: Germany

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

30 Jun 2019, 02:24

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 :=""
Last edited by aifritz on 30 Jun 2019, 09:32, edited 1 time in total.
User avatar
Datapoint
Posts: 68
Joined: 18 Mar 2018, 17:06

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

30 Jun 2019, 02:27

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
Posts: 525
Joined: 20 Oct 2016, 08:20

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

30 Jun 2019, 02:53

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
____________________________________________________________________________
Windows 10 Pro 64 bit - Autohotkey v1.1.30.01 64-bit Unicode
aifritz
Posts: 145
Joined: 29 Jul 2018, 11:30
Location: Germany

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

30 Jun 2019, 06:32

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
Last edited by aifritz on 30 Jun 2019, 11:26, edited 2 times in total.
bballjoe12
Posts: 113
Joined: 20 Aug 2017, 09:11

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

30 Jun 2019, 10:13

Thank you all for your help! All great information!
User avatar
flyingDman
Posts: 291
Joined: 29 Sep 2013, 19:01
Location: Burbank, California

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

30 Jun 2019, 12:07

Datapoint wrote:
30 Jun 2019, 02:27
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.
User avatar
Datapoint
Posts: 68
Joined: 18 Mar 2018, 17:06

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

30 Jun 2019, 22:43

flyingDman wrote:
30 Jun 2019, 12:07
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

Return to “Ask For Help”

Who is online

Users browsing this forum: aifritz, Flipeador, Sir Teddy the First and 54 guests