Importing/Exporting multiple Excel tabs without changes

[ppatane]

In the course of my workflow, I need to import 3 tabs from an Excel file and then export them, with no changes, to another Excel file. I will pick up things after that happens.  I have read elsewhere that I can use an input tool for each tab and then an output tab for each, but that doesn't work as the output workbook is in use for the 2nd and 3rd tabs.  I can't use a Block Until Done tool as I can't connect the 2nd and 3rd outputs to another input tool.

How can this be accomplished?  Thanks!

[flying008]

Hi, @ppatane 

Maybe you can use powershell code in event or Run Command tool to get it .

$sourcePath = "\\data1\user2\ppatane\Files\original data.xlsx"
$destinationPath = "\\data1\user2\ppatane\Output\output data.xlsx"
$sheetNames = "Load File", "Excel Format", "Detail"

# Create Excel COM object
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false # Optional: Set Excel to invisible

try {
    # Open the source Excel file
    $sourceWorkbook = $excel.Workbooks.Open($sourcePath)

    # Open the destination Excel file (assuming it exists)
    $destinationWorkbook = $excel.Workbooks.Open($destinationPath)

    foreach ($sheetName in $sheetNames) {
        try {
            # Get the source sheet
            $sourceSheet = $sourceWorkbook.Worksheets.Item($sheetName)

            # Check if a sheet with the same name already exists in the destination workbook
            $existingSheet = $destinationWorkbook.Worksheets.Item($sheetName)

            if ($existingSheet) {
                # If it exists, delete the existing sheet
                $existingSheet.Delete()
            }

            # Copy the sheet and place it at the end
            $sourceSheet.Copy(After:=$destinationWorkbook.Worksheets($destinationWorkbook.Worksheets.Count))

        }
        catch {
            Write-Warning "Failed to copy worksheet '$sheetName': $($_.Exception.Message)"
        }
    }

    # Save and close the destination Excel file
    $destinationWorkbook.Save()
    $destinationWorkbook.Close()

}
finally {
    # Close the source Excel file
    $sourceWorkbook.Close()

    # Quit the Excel application
    $excel.Quit()

    # Clean up COM objects
    [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
    Remove-Variable excel
}

Write-Host "Worksheet copying complete."

[binuacs · Accepted solution]

@ppatane I updated the macro to save the output file in the output folder

make sure you have the output folder present

[ppatane]

@binuacs Thanks for this...this is something I can work with and learn from. My apologies for taking so long to get back. I never received a notice that you posted this even though you used the @ notification feature.