Merge several Excel workbooks into another multiple-sheet workbook

[Deon]

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 November 25, 2015 by Deon

[water]

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

 

[Deon]

You are actually a god, water.

Thank you so much for your help!! 

[water]

Glad to be of service