Jump to content
ViciousXUSMC

Appending Many Excel Files Together

Recommended Posts

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

 

Share this post


Link to post
Share on other sites
Danyfirex

use Excel UDF read to array and append all arrays.

 

Saludos

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

 

Share this post


Link to post
Share on other sites
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 by Danyfirex
Edited

Share this post


Link to post
Share on other sites
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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by ViciousXUSMC

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by ViciousXUSMC

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
ViciousXUSMC

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

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • nooneclose
      By nooneclose
      Hey. I'm working on a new project and was wondering if there is a better way to "update" my Column E array. 
      Here is my code: 
      Local $nI  = 0                                                            ;Creates a name index of 0: nI = Name index Local $nII = 1                                                            ;Creates a name index of 1 for second loop: nII = Name Index 2 For    $iN = 0 To $IndexRows Step 1                                       ;Checks the roster for any names that appear twice      For $iN2 = 0 To $IndexRows Step 1          if $d_Names[$nI] == $d_Names[$nII] And $d_Names[$nII] <> "" Then              Local $timeSheetName = _ArraySearch($e_Names, $d_Names[$nI], 0, 0, 0, 0, 1)              ;MsgBox($MB_SYSTEMMODAL, "Found it", $d_Names[$nI] & " In column E on Row " & $timeSheetName)              Local $eI  = $timeSheetName + 1              ;ConsoleWrite($timeSheetName & @CRLF)              ;ConsoleWrite($eI & @CRLF)              ;ConsoleWrite(@CRLF)              _Excel_RangeInsert($OpenWorkbook.ActiveSheet, "E" & $eI & ":F" & $eI, $xlShiftDown)                                                                          ;Inserts a empty cell in columns E and F.              _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $d_Names[$nII], "E" & $eI)                                                                         ;Fills the empty cell in columns E with the doubled name              $aArray_Index = 2                                           ;Array element counter              For $Index = 2 To $IndexRows Step 1                        ;Loops through every row in the Excel file unto no rows are found or a null row is found                  $Array_Value_E = _Excel_RangeRead($OpenWorkbook, Default, "E"&$Index)                  $e_names[$aArray_Index] = $Array_Value_E                ;While the code loops every value in column E is stored in the E array (updating the array)                  $aArray_Index += 1              Next              ExitLoop          EndIf      Next      $nI  += 1      $nII += 1 Next Basically, It checks a roster for people whose name appears twice then inserts a new "row" for that person because they work in two different departments.
      I have to find that name however in Column E if two appear in column D. My code works but I think it is not as efficient as it could be. 
      Any ideas on how to improve the "update" for my array?
      (once it finds the double names in Column D it then searches for that name by going name by name in the Column E array and once it finds it inserts a new row. However, the E array doesn't have that new row stored in it so I have to "update" the array to properly find the next name)
      Any and all tips would be greatly appreciated. 
       
      NOTE: Just assume I'm opening the excel file properly please do not add that code in, it only complicates your answer. 
    • smud
      By smud
      Currently, I'm working on a program that will display Dialog boxes with either Yes or No.
      For each dialog, I reward the user with X amount of Credits.
      I'm hoping to output the amount of credits to a cell in a column (there will be 20 different columns).
      It will only post to a row that is equal to today's date (first column). If no row exists yet with the current date, it will start a new row.
      Any suggestions?
      Thank you
    • nooneclose
      By nooneclose
      How do I properly convert this to Autoit? This is a VBA macro that I recorded in Excel.
       ActiveSheet.Outline.ShowLevels RowLevels:=2 I need this to close my subtotal once it is finished. 
      any help will be greatly appreciated. 
    • Skeletor
      By Skeletor
      Hi All,
      While creating a few excel spreadsheets using AutoIt, I came across something which to my limiting time to research the forums I don't anyone has mentioned. 
      The color pallettes are reversed. 
      Huge shock to me.
       
      I wanted to produce a red row but kept on getting blue. 
      Seems like 0xFF0000 was red on the charts but when running the script, I got blue. 
      I then played around with the colors, and after a few tries, I finally got Red. 
      Reversed the FF0000 and the result is 0000FF.
       
      So for Excel compared to Html
      0000FF (Red) - Excel
      0000FF (Blue) - Html

      FFFF00 (Cyan) - Excel
      FFFF00(Yellow) - Html
       
    • Jemboy
      By Jemboy
      Yesterday I had to make some little changes to an old Autoit program we use at my work.
      The program reads some data and convert it to Excel.
      Before writing the cell, it is changed to text and later on I slap the column with an autofitwidth.
      Furthermore weI execute a conditional format on the sheet, to make the data more readable.
      I quickly found out that because of the breaking changes Excel.udf had starting from AutoIt 3.3.12.0,
      a lot of things had to been changed.
      The changes I had to do, only took 10 minutes.
      After trying to adjust the script for over 5 hours, to get it working with the new Excel.udf, I gave up.
      I stopped changing the script, uninstalled the my Autoit and went looking for an older version.
      Luckilly I was able to find Autoit v3.3.8.1 (with corresponding Scite) in my software repository.
      Installing Autoit V3.3.8.1 and compiling the file, now took me  10 minutes .
       
      So why did I not get the old script working with the new Excel.udf?
      There are several reasons I failed getting the old script working with the new Excel UDF.
       I had some pressure from management to fix it ASAP (and got a little anxious)  Most all resources on the internet point to the working of the old EXcel.UDF And offcourse there were thosing "breaking changes",
      with new functions using diffrent parameters or using parameters in different order. One of the column's on the sheet is used to store EAN13 (barcode) and was formatted like 1,23E12.
      I couldn't change the cell to text, also autofitwidt was not working and using conditional formatting was also a no-no.
      So in the end I could use the new Excel UDF, but not desapointed management.
       
      What would I like to ask?
      I understand that sometimes you want to rewrite a program to make it better. I even understand that one has to make breaking changes sometimes.
      But in this case because of lacking examples/resources my day went completely down the drain.
      I would like to ask the Excel.udf developpers to:
      Make more functions available to do things like changing cell properties easily, changing cell color, do an autofit columnwidth, format data conditionally. 
        Or write an Example using the (new) Excel UDF, making examples how to format a cell, do conditional format, changing cell colors etc.
        I probably am more of an example guy.
      Having a good Excel.UDF Example showing a lot of common things normally makes, programming things easy for me.
      Because I can keep tweaking snippets until I get it working the way I want it.
       
      So dear developpers, could you help me and other future user out?
       
       
       
       
       
       
       
       
       
       
       
       
       
       
×