Excel loop using the last empty row Topic is solved

[jaccotjuhhh]

I have a funtion where a certain XML will be 'imported' in a XLSX.
Stripped down it looks like this:

Code: Select all

XmlProcessor(){
xmlp := ComObject("MSXML2.DOMDocument.3.0")
xmlp.async := false
xmlp.load(ontvangstxml)

xmle := ComObject("Excel.Application")
xmle.Workbooks.Open(A_WorkingDir "\Administratie.xlsx")
xmle.visible := true
WinActivate ,"Administratie"

for ea in xmlp.getElementsByTagName("RequestDto")
	{
	xmle.Columns("A").Find("*",,,,,2).Offset(1,0).value := ea.getElementsByTagName("1").item(0).text
	xmle.Columns("B").Find("*",,,,,2).Offset(1,0).value := ea.getElementsByTagName("2").item(0).text
	xmle.Columns("C").Find("*",,,,,2).Offset(1,0).value := ea.getElementsByTagName("3").item(0).text
	xmle.Columns("D").Find("*",,,,,2).Offset(1,0).value := ea.getElementsByTagName("4").item(0).text
	xmle.Columns("E").Find("*",,,,,2).Offset(1,0).value := ea.getElementsByTagName("5").item(0).text
	xmle.Columns("F").Find("*",,,,,2).Offset(1,0).value := ea.getElementsByTagName("6").item(0).text
	xmle.Columns("G").Find("*",,,,,2).Offset(1,0).value := ea.getElementsByTagName("7").item(0).text
	xmle.Columns("H").Find("*",,,,,2).Offset(1,0).value := ea.getElementsByTagName("8").item(0).text
	xmle.Columns("I").Find("*",,,,,2).Offset(1,0).value := ea.getElementsByTagName("9").item(0).text

	}
	xmle.activeworkbook.save
	xmle.quit
}
}

It is a very (very) big XML. So lots of data to be imported to the xlsx.
Im thinking here that using

Code: Select all

xmle.Columns("E").Find

may not be the most power-efficient command to use in this kind of loop.
When i run the script, my youthful computer starts to have a sweat.

i thought of moving the Find command outside the For loop. And having it select that cell. To then have the For loop, offset the selected cell at each loop.

It's just that i do not have the knowhow to get that going.

[mcl]

It is a very (very) big XML. So lots of data to be imported to the xlsx.
Im thinking here that using xmle.Columns("E").Find may not be the most power-efficient command to use in this kind of loop.

For bulk insert operation use safearrays, to avoid unnecessary redraws/recalculations on each cell change. Something like that (not tested):

Code: Select all

; read data into ahk array
myarr := []
columncount := 0
for ea in xmlp.getElementsByTagName("RequestDto") {
	datarow := []
	datarow.Push(ea.getElementsByTagName("1").item(0).text)
	...
	datarow.Push(ea.getElementsByTagName("9").item(0).text)
	columncount := Max(columncount, datarow.Length)
	myarr.Push(datarow)
}

; create safearray of the same size
mysafearr := ComObjArray( VT_VARIANT:=12, myarr.Length, columncount )
For rown, datarow In myarr {
	Loop columncount {
		mysafearr[rown-1, A_Index-1] := datarow[A_Index]
	}
}

; paste all data in one call
xmle.Columns("A").Find("*",,,,,2).Offset(1,0).Resize( myarr.Length, columncount ).Value := mysafearr

You may also take a look at following Excel methods and objects:
DisplayAlerts / Interactive / ScreenUpdating / Calculation / UsedRange

[flyingDman]

I agree with @mcl that for large data dumps into Excel gathering your information into a safearray is the preferred method. But depending on your skill level, it can be a bit complicated. The main reason for the use of a safearray is speed. Each COM call is slow and therefore entering data one cell at a time (i.e. one COM call at a time) will take too long (possibly many minutes). A Safearray limits the excel COM calls to 1.
An alternative might be to create a TSV dataset (Tab Seperated Values) and paste it in one dump into Excel. Once the TSV has been created, there is also just one COM call to make but it involves the clipboard (using safearray does not* use the clipboard).
This is the principle:

Code: Select all

FrstRow := NwRow := xl.Sheets(1).UsedRange.Rows.Count + 1   ; assumes headers are in row 1
a_clipboard := ""
loop 100000													; for ea in xmlp.getElementsByTagName("RequestDto")
	{
	loop 9													; 9 columns	
		tmp .= a_index "`t"            						; tmp .= ea.getElementsByTagName(a_index).item(0).text "`t"
	tmp .= "`n"	
	++NwRow
	}	
a_clipboard := tmp
xl.activesheet.range("a" FrstRow).pastespecial(-4104)		; inserts the TSV on the new row in column "A"
a_clipboard := tmp := ""

(this takes 750 ms. for 100,000 rows)

[andymbody]

FlyingDMan... You seem to have a very good grasp of using COM with excel. Do you have a resource for this that you would be willing to share? A detailed reference of the commands that can be used to manipulate Excel via COM? I've looked for such a reference and books, but unable to find this.

Thanks!
Andy

[flyingDman]

Some references:

viewtopic.php?f=7&t=8978
https://autohotkey.com/board/topic/69033-basic-ahk-l-com-tutorial-for-excel/
https://learn.microsoft.com/en-us/office/vba/api/overview/excel
https://docs.microsoft.com/en-us/office/vba/api/overview/excel/object-model
https://learn.microsoft.com/en-us/office/vba/api/excel(enumerations)
https://github.com/ahkon/MS-Office-COM-Basics/tree/master/Examples/Excel
viewtopic.php?f=6&t=77#p495

[flyingDman]

Re: the use of Columns("A").Find("*",,,,,2). This will get you the last row in a range but I would have used xl.activesheet.usedrange.Find("*",,,,1,2).row just in case the columns do not have the same length. For simplicity i used xl.Sheets(1).UsedRange.Rows.Count.

[andymbody]

Some references:

Wow... thank you... I'm glad I asked. I guess I didn't do a very good job in my search. Thank you very much!

[flyingDman]

@jaccotjuhhh
Could you try to import the XML directly into Excel using the following?:

Code: Select all

xl := ComObject("excel.application")
xl.visible := true
wrkbk := xl.workbooks.openXML(A_ScriptDir "\testxml.xml",,2)    ; change path to that of your file

A test based on a simple .xml revealed that that works well. Press OK when prompted by Excel and it will import it correctly (you may want to check the box not to see that message box again). Your XML might be more complicated.

Test XML

Code: Select all

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
<s:Body>
<Item>
<code1>123</code1>
<code2>abc</code2>
</Item>     
<Item>
<code1>456</code1>
<code2>def</code2>
</Item>     
<Item>
<code1>789</code1>
<code2>ghi</code2>
</Item>     
<Item>
<code1>789</code1>
<code2>ghi</code2>
</Item>     
</s:Body>
</s:Envelope>

image.png (10.29 KiB) Viewed 380 times

The script also handles this file: https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms762271(v=vs.85)

[jaccotjuhhh]

Thank you guys for the advice!

[flyingDman]

So importing the file directly into Excel using openXML didn't work?

[jaccotjuhhh]

@flyingDman sadly wasn't optimal as there are many other values in each RequestDto that aren't needed.
each RequestDto is going to be appended in an already populized excel table, so sorting the right value to the right column is also an issue.
For max clarification: the "1" "2" etc values are obfuscated

[flyingDman]

Those are not very convincing arguments. Deleting unnecessary columns and copying data between sheets is the easy part. xl.workbooks.openXML takes care of the parsing of the XML file and the creation of a dataset in excel format and replaces therefore many lines of code. IMHO a clearly superior method of dealing with transfer XML data to Excel.