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?
Read Excel Line, Then Move to Next Row w/ Changing Variables
-
- Posts: 114
- Joined: 20 Aug 2017, 09:11
- flyingDman
- Posts: 2832
- Joined: 29 Sep 2013, 19:01
Re: Read Excel Line, Then Move to Next Row w/ Changing Variables
The simplest form would be something like this:
To skip the first row add something in the loop like this: if a_index =1, continue
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
14.3 & 1.3.7
-
- Posts: 114
- Joined: 20 Aug 2017, 09:11
Re: Read Excel Line, Then Move to Next Row w/ Changing Variables
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%
}
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%
}
-
- Posts: 114
- Joined: 20 Aug 2017, 09:11
Re: Read Excel Line, Then Move to Next Row w/ Changing Variables
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!
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
- Posts: 2832
- Joined: 29 Sep 2013, 19:01
Re: Read Excel Line, Then Move to Next Row w/ Changing Variables
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"]
14.3 & 1.3.7
- flyingDman
- Posts: 2832
- Joined: 29 Sep 2013, 19:01
Re: Read Excel Line, Then Move to Next Row w/ Changing Variables
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
14.3 & 1.3.7
Re: Read Excel Line, Then Move to Next Row w/ Changing Variables
or:
Edit: Added xl.Visible := True, xl :=""
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
Last edited by aifritz on 30 Jun 2019, 09:32, edited 1 time in total.
Re: Read Excel Line, Then Move to Next Row w/ Changing Variables
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.
Re: Read Excel Line, Then Move to Next Row w/ Changing Variables
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
Windows 10 Pro 64 bit - Autohotkey v1.1.30.01 64-bit Unicode
Re: Read Excel Line, Then Move to Next Row w/ Changing Variables
Thank you Datapoint for your hint
Here a version, showing the data in a ListView:
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
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
}
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.
-
- Posts: 114
- Joined: 20 Aug 2017, 09:11
Re: Read Excel Line, Then Move to Next Row w/ Changing Variables
Thank you all for your help! All great information!
- flyingDman
- Posts: 2832
- Joined: 29 Sep 2013, 19:01
Re: Read Excel Line, Then Move to Next Row w/ Changing Variables
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
14.3 & 1.3.7
Re: Read Excel Line, Then Move to Next Row w/ Changing Variables
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...flyingDman wrote: ↑30 Jun 2019, 12:07The 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.
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
Who is online
Users browsing this forum: Bing [Bot], Google [Bot], Skrell and 105 guests