Sign in to follow this  
Followers 0
sc4ry

sumup several Excel-Files to one file

5 posts in this topic

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.

Share this post


Link to post
Share on other sites



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)

Share this post


Link to post
Share on other sites

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


(Yet Another) ExcelCOM UDF"A human being should be able to change a diaper, plan an invasion, butcher a hog, conn a ship, design a building, write a sonnet, balance accounts, build a wall, set a bone, comfort the dying, take orders, give orders, cooperate, act alone, solve equations, analyze a new problem, pitch manure, program a computer, cook a tasty meal, fight efficiently, die gallantly...[indent]...specialization is for insects." - R. A. Heinlein[/indent]

Share this post


Link to post
Share on other sites

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.

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
Sign in to follow this  
Followers 0