Excel memory overflow?

[Bassiehetkoekje]

I made an AHK script that does the following:

• Open an empty Excel sheet
• One by one, read a bunch of excel files from a chosen folder and copy the contents of Cell A1
• Write copied content in the original sheet

I use

Code: Select all

Loop, Files, % Path "\Invoice*.xlsx"
{
	WB := ComObjGet(A_LoopFileFullPath)
	; do stuff
}

to get values from the excel files to read.

The script works as expected, but after about 300 files Excel stops reacting and complains about insufficient memory. The actual message is this:

There isn't enough memory to complete this action.
Try using less data or closing other applications.
To increase memory availability, consider:
- Using a 64-bit version of Microsoft Excel.
- Adding memory to your device

[boiler]

Try this:

Code: Select all

Loop, Files, % Path "\Invoice*.xlsx"
{
	WB := ComObjGet(A_LoopFileFullPath)
	; do stuff
	WB.Close()
}

[flyingDman]

To read data from a large number of excel files, ADODB is significantly faster than ComObjGet. In test ADODB was ~ 16.5 times faster than ComObjGet (6.3 sec vs 103.5 sec).
I created a function - getXcells() - to extract data from Excel sheets using ADODB. You will need to install the Microsoft Database Engine, a free utility which can be found here: https://www.microsoft.com/en-us/download/details.aspx?id=54920 if not already installed on your PC.
The function:

Code: Select all

getXcells(datasource, sheet := "sheet1") 
	{
	global colcnt, rowcnt
	arr := [], rowcnt := 0
	objConnection := ComObjCreate("ADODB.Connection"), 	objRecordSet := ComObjCreate("ADODB.Recordset")
	try objConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" . dataSource . "; Extended Properties='Excel 12.0 xml;HDR=no;IMEX=1';")
	catch
		{
		msgbox,48,, Error! Data could not be retrieved, 2
		return
		}
	try objRecordset.Open("Select * FROM [" Sheet "$]", objConnection, 3, 3, 1)   ; adOpenStatic = 3 , adLockOptimistic = 3 , adCmdText = 1
	catch
		{
		msgbox,48,, Error! %Sheet% does not exist, 2
		return
		}
	pFields := objRecordset.Fields
	while !objRecordset.EOF
		{
		row := [], ++rowcnt
		Loop, % colcnt := pFields.Count
			row[A_Index] := pFields.Item(A_Index-1).value
		arr.push(row)
		objRecordset.MoveNext
		}
	objRecordSet.Close()
	objConnection.Close()
	objRecordSet := ""
	objConnection := ""	
	return arr
	}

To get the content of cell A1 out of each of the files in the loop, you can use this:

Code: Select all

Loop, Files, % Path "\Invoice*.xlsx"
	lst .= getXcells(A_LoopFileFullPath, "sheet1").1.1 "`n"
msgbox % lst

[Bassiehetkoekje]

Thanks so much for your replies @boiler and @flyingDman . I actually pressed 'post' accidentally, but it seems like that was enough info to answer my unasked question

WB.Close() worked! Now I feel silly... I was assuming that there would be garbage collection every time I re-initialized the WB variable. But Excel itself kept everything in memory it seems.

I didn't implement @flyingDman's solution yet, because I just wanted to get it working without too much hassle and it's fast enough for my purposes (1 min). However, I'm intrigued by the suggested speed difference so I'll try it out later