Hello

Is something like this possible to do with powershell? I'm using excel 2013. The script should create the 1 excel file by itself.

Thank you.

If you search you will find many examples of how to copy worksheets between workbooks.

Here is one simlpe example:

$xl = new-object -c excel.application
$xl.displayAlerts = $false
$source = $xl.workbooks.open("$pwd\book1.xlsx")
$target = $xl.workbooks.open("$pwd\book2.xlsx")
$source.sheets.item('sheet1').copy($target.Worksheets.Item(1))
$source.close($false)
$target.close($true) 
$xl.quit()

Hello

The problem with google this stuff.. it never works. And neither does that one :(

Exception getting "Item": "Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"
At C:\Users\x\Documents\x\x\Data\xxx.ps1:177 char:1
+ $source.sheets.item('sheet1').copy($target.Worksheets.Item(1))
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], GetValueInvocationException
    + FullyQualifiedErrorId : CatchFromBaseAdapterParameterizedPropertyGetValueTI

Also noticed it doesn't create target by itself.

I cannot help you to understand the code I can only show it to you.  If you don't know Excel or the object model then it is going to be very hard for you to write this script.

The script does not combine six arbitrary files. It copies one sheet named "sheet1" from one wookbook to another. The second workbook has to have one sheet minimum.

Example:

# get first sheet in source and rename then copy to target.
$source.sheets.item(1).Name='NewTest'
$source.sheets.item('NewTest').copy($target.Worksheets.Item(1))

If your workbooks do not conform then you will have an issue.

If you cannot understand how to use this information or the meanning of the errors then I recommend hiring a consultant to help you.

You are asking for a large script.  We don't write scripts on demand. Sorry.