Deon

Merge several Excel workbooks into another multiple-sheet workbook

4 posts in this topic

#1 ·  Posted (edited)

Hi,

I have a script which fetches 7 excel files from our company's webserver and saves them as a file.

I then create a new spreadsheet with 7 tabs and copy-paste each of the 7 files into it's own tab.

I'm trying to automate this and not having much luck. I've tried using _Excel_RangeCopyPaste and failed abysmally, and then in desperation tried using Send() to send ctrl and tab keys but I really want a cleaner way.

I would paste the code I have, but I overwrote the one using the Excel UDF, and you really don't want to see the one with a hundred Send()'s and Sleep()'s in it.

I'd really appreciate any help anyone can offer! I've been using AutoIt for a while, so even if you can push me in the right direction, I can continue from there :)

 

Cheers,
Deon.

Edited by Deon

Share this post


Link to post
Share on other sites



Something like this?

#include <Excel.au3>
#include <File.au3>
Global $aWorkbooks[] = ["C:\temp\WB1.xlsx", "C:\temp\WB2.xlsx"]
Global $sDrive, $sDir, $sFileName, $sExtension
$oExcel = _Excel_Open()
$oWorkbookOut = _Excel_BookNew($oExcel, 1)
For $i = 0 To UBound($aWorkbooks, 1) - 1
    $oWorkbookIn = _Excel_BookOpen($oExcel, $aWorkbooks[$i], True)
    $oSheet = _Excel_SheetCopyMove($oWorkbookIn, 1, $oWorkbookOut, $i + 1)
    _Pathsplit($aWorkbooks[$i], $sDrive, $sDir, $sFileName, $sExtension)
    $oSheet.Name = $sFileName
    _Excel_BookClose($oWorkbookIn)
Next

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

You are actually a god, water.

Thank you so much for your help!! :D

Share this post


Link to post
Share on other sites

Glad to be of service :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

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

    • LoneWolf_2106
      By LoneWolf_2106
      Hi,
      i have an error:
      ==> The requested action with this object has failed.: $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count $iRowCount = .Range(^ ERROR  
      My code is:
      $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) Sleep(1000) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count Sleep(1000) _Excel_RangeWrite($oWorkbook_1, $oWorkbook_1.ActiveSheet,$aFileList[$i][2] , "AB3:AB"&$iRowCount) I have added some sleep because the application was crashing more often before, so i thought to slow down the code execution.
      But i didn't solve the issue.
      Has anyone an idea of what the problem might be?
      Thanks in advance.
    • water
      By water
      Extensive library to control and manipulate Microsoft Excel charts.
      Written by GreenCan and water.
      Theads: General Help & Support - Example Scripts
      BTW: If you like this UDF please click the "I like this" button. This tells me where to next put my development effort

      KNOWN BUGS (last changed: 2017-07-21)
      None. The COM error handling related bugs have been fixed.
       
    • LoneWolf_2106
      By LoneWolf_2106
      Hi,
      i have a problem with the deletion of an empty row in Excel.
      My code:
       
      If $vRow_2 = "" Then _Excel_RangeDelete($oWorkbook_1.ActiveSheet,"2", $xlShiftUp,1) EndIf I want to delete the second row. $vRow_2 is an empty cell, "A2".
      After running the code, the second row is not deleted.
      I have tried also:
       
      If $vRow_2 = Null Then _Excel_RangeDelete($oWorkbook_1.ActiveSheet,"2", $xlShiftUp,1) EndIf But it doesn't work.
      Any suggestion?
      Thanks in advance.
    • VeryGut
      By VeryGut
      I'm trying to insert the following formula in cell A2 using my script:
      =if(A1=""; "YES"; "NO")
      To my understanding, the line of code should be similar to this:
      _Excel_RangeWrite($MasterFile, Default, "=if(A1=""; "YES"; "NO")", "A2")
      However, it does not work, probably due to the multiple quotation marks that confuse the script :C
      How do I avoid this problem?
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have a question about Excel, i have to create several charts one below the other dynamically.
      I have thought to use:
       
      $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count  
      And then to use it in this way:
      $Graph_position = "=Test1!A"&$iRowCount+2&":K"&$iRowCount+24 But it doesn't work with charts.
      Does anyone have a suggestion?