Appending Many Excel Files Together

[ViciousXUSMC]

So I am doing a custom inventory script that creates an excel document with 4 columns and sends a copy of the excel file over to a network location.

What I end up with is a few hundred excel files each representing a single computer.  My goal is to append all of those documents together into a single document so that I can turn the data into a table for sorting and review.

I know there are a few ways to skin this cat, just not sure the easiest and best.

I was trying using com objects based on an old thread I found by searching but I fail on my Line 10 when I try to declare the $master sheet I get not an object.

I am open to any solution, and interested in the com method as well since I am learning that stuff on the side.

My Broken Script
 

#Include <File.au3>
#Include <Array.au3>
#Include <FileConstants.au3>

$aFiles = _FileListToArray("\\vpsfs1\pstinfo\pst info\", "*.xls", $FLTA_FILES, TRUE)

;_ArrayDisplay($aFiles)

$oEX = ObjCreate("","excel.Application")
$master = $oEX.Workbooks.Open("\\vpsfs1\pstinfo\master.xlsx")

For $i = 1 to $aFiles[0]
$another = $oEX.Workbooks.Open($aFiles[$i])
$another.Activesheet.UsedRegion.Copy
$next = StringSplit($master.ActiveSheet.UsedRange.Address,"$")
$master.Activesheet.Range("a" & Activesheet.usedrange.rows.count + 1).Select
$master.Activesheet.Paste
Next

 

[Danyfirex]

use Excel UDF read to array and append all arrays.

 

Saludos

[water]

I second that. The UDF might be a bit slower but you get better error handling

[ViciousXUSMC]

Working on it  I should only use ExcelOpen() once and ExcelClose() at the end after my read loop correct?  Or will I need to open/close for each iteration of the loop?

Having a few issues, even when I put everything in the loop, this only gives me a result on the first pass.  Once I get it working then I need to figure out the best way to append this to an array.

#Include <File.au3>
#Include <Array.au3>
#Include <FileConstants.au3>
#Include <Excel.au3>

$aFiles = _FileListToArray(@ScriptDir, "*.xls", $FLTA_FILES, TRUE)
;_ArrayDisplay($aFiles)

For $i = 1 to $aFiles[0]
Local $oExcel = _Excel_Open(False)
Local $oWorkbook = _Excel_BookOpen($oExcel, $aFiles[$i])
Local $aResult = _Excel_RangeRead($oWorkbook, DEFAULT, DEFAULT)
_Excel_Close($oExcel, False, True)
_ArrayDisplay($aResult)
Next

 

[Danyfirex]

This work For me.

#Include <File.au3>
#Include <Array.au3>
#Include <FileConstants.au3>
#Include <Excel.au3>

$aFiles = _FileListToArray(@ScriptDir, "*.xls",$FLTA_FILES, TRUE)


Local $oExcel = _Excel_Open(False)

For $i = 1 to $aFiles[0]
Local $oWorkbook = _Excel_BookOpen($oExcel, $aFiles[$i])
Local $aResult = _Excel_RangeRead($oWorkbook, DEFAULT, DEFAULT)
_ArrayDisplay($aResult)
Next

_Excel_Close($oExcel, False, True)

close book if is needed with_Excel_BookClose

Saludos

Edited December 10, 2015 by Danyfirex
Edited

[water]

You should move _Excel_Open/_Excel_Close to the begin/end of the script (untested):

#Include <File.au3>
#Include <Array.au3>
#Include <FileConstants.au3>
#Include <Excel.au3>

$aFiles = _FileListToArray(@ScriptDir, "*.xls", $FLTA_FILES, TRUE)
Global $oWorkbook, $aResult
Global $oExcel = _Excel_Open(False)
$oMaster = _Excel_BookOpen("\\vpsfs1\pstinfo\master.xlsx")
For $i = 1 to $aFiles[0]
    $oWorkbook = _Excel_BookOpen($oExcel, $aFiles[$i])
    $aResult = _Excel_RangeRead($oWorkbook)
    _Excel_RangeWrite($oMaster, Default, $aResult, "A" & $oMaster.ActiveSheet.UsedRange.Rows.Count + 1)
    _Excel_BookClose($oWorkBook, False)
Next
_Excel_Close($oExcel, False, True)

 

Edited December 10, 2015 by water

[ViciousXUSMC]

Yes I had it that way first Water & Danny it was not working so I moved in in the loop for testing.

I think my issue maybe Excel related or something, at first my Excel_Open was not even working until I went and messed with Trust Center settings.

Edit: Also tried adding Book Close.

 

So it seems my issue needs to be investigated and its not because I am being dumb. 
 

Edited December 10, 2015 by ViciousXUSMC

[water]

Could you please insert a COM error handler to your script so we get detailed error information? Please check the helpfile for ObjEvent.

[ViciousXUSMC]

I fixed it, I had to turn off protected mode for these Type 2 files that are created from the ArrayToXLS UDF on the forums.

I imagine when it goes to close/save the first file it messes up the rest in the loop.

 

This looks good, thanks guys!

#Include <File.au3>
#Include <Array.au3>
#Include <FileConstants.au3>
#Include <Excel.au3>

$aFiles = _FileListToArray(@ScriptDir, "*.xls",$FLTA_FILES, TRUE)
Local $oExcel = _Excel_Open(False)
Local $aFinalResults[0][4]

For $i = 1 to $aFiles[0]
Local $oWorkbook = _Excel_BookOpen($oExcel, $aFiles[$i])
Local $aResult = _Excel_RangeRead($oWorkbook)
;_ArrayDisplay($aResult)
_ArrayConcatenate($aFinalResults, $aResult)
Next

_ArrayDisplay($aFinalResults)
_Excel_Close($oExcel, False, True)

 

Edited December 10, 2015 by ViciousXUSMC

[water]

Do not forget to close the workbooks you opened again. Else you will end up with severel hundred of open workbooks eating up your memory.

[ViciousXUSMC]

Oh that forced excel close at the end will not close all the workbooks?

[water]

It will. But why have hundreds of worksbooks open which you no longer need?