sumup several Excel-Files to one file

[sc4ry]

hello,

i search for a method to sumup several excel files into one file.

the search tells me a lot about excel and autoit, but i didn´t find anything which helps me.

i have 5 files in xls format with 1 worksheet, all worksheets are different, and i need all these worksheets in one excel file.

can anybody tell me how to do this?

i also have a macro which can do this, but with this macro you got a gui where you can set different settings. so only start the macro doesn´t really work.

[Zedna]

Look at Randall's Excel COM UDF

example

[sc4ry]

hey, thank you for your help.

i think i found something interesting for me.

this script below works, only the function i need, doesn´t work =(

somebody who knows _ExcelSheetMove?

with the help _ExcelSheetMove($oExcel, $vMoveSheet, $vRelativeSheet = 1, $fBefore = True) i don´t really understand what this mean.

anything i tried does not work.

#include<ExcelCOM_UDF.au3>

$ooExcel = _ExcelBookNew(1)
$o1 = _ExcelBookOpen("G:\11 BPS\Azubi\Pluto\Tagesumsätze\IE-Exports\Produkte.xls",1)
$o1sheet = _ExcelSheetNameGet($o1)
_ExcelSheetMove($ooExcel, $o1sheet)
_ExcelBookClose($o1)

[Locodarwin]

Howdy do,

_ExcelSheetMove() isn't what you're looking for anyway. It only moves sheets around within a workbook. If I understand your requirements, you need to move or copy sheets between workbooks. To do that, the best approach would probably be to open the workbook you want to move a sheet from, select the sheet, select all used cells in the sheet, then copy. Once copied, open the workbook that will receive the copy, create a new sheet, give it focus, and then paste.

There are commands in the UDF collection to perform every single step above.

Good luck in your Excel endeavors!

-S

[sc4ry]

hey, thanks for your help.

i thought about it, but there must be an easier way.

i tried to change the udf for my problem, but it doesn´t work very well.

But then user BugFix came up, and helped me (thanks for this).

his way was (i useed it with the udf):

$SaveDirExcel = IniRead(@scriptdir & $Settings, "Path", "SaveDirExcel", "")
$CreateExcel = _ExcelBookNew(0)
_ExcelBookSaveAs($CreateExcel, $SaveDirExcel & $FilenameTG)
_ExcelBookClose($CreateExcel) ;cause it only works with saved files

    $sFilePath1 = ($SaveDirIE & $FileNameMarken)
    $sSavePath1 = ($SaveDirExcel & $FileNameTG) 
    $Anzahl = 1 ;count of sheets to move
    $oExcel = ObjCreate("Excel.Application")
    $oWBook1 = $oExcel.Workbooks.Open($sFilePath1)
    $oSBook1 = $oExcel.Workbooks.Open($sSavePath1)
        For $i = 1 To $Anzahl
            $oWBook1.Sheets(1).Move($oSBook1.Sheets(1))
        Next
    $oExcel.DisplayAlerts = False
    $oExcel.Save
    $oExcel.Application.Quit 
    $oExcel = ''

maybe some of u will need it too, or somebody can enclose it to the udf.