Importing/Exporting multiple Excel tabs without changes

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!

Workflow

Accepted answers

binu_acs

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

make sure you have the output folder present

Copy_Sheets_03022025.yxzp

All comments

alexnajm

Seems like you just want to copy a file and paste to another fill name? If so, the Blob tools may be an option: Solved: Copy and Paste Excel from one folder to another an... - Alteryx Community

ppatane

@alexnajm It's a bit different than that, the source workbook has 4 tabs and I only want to copy 3 of them to the destination file, which has a total of six tabs. Would the Blob tools work for that as well? It's not just a straight move of the entire file

Jfoss

I am not sure what you data looks like but

What I have done in the past is to use an input to bring just the excel tab names. one input to get just the tab names (step1.png)

I then added a formula tool to add the full file destination and dynamically add the tab name. (step2.png)

The dynamic input tool was then used to bring in all of the data into one flow. (step3.png)

Use another formula tool to create a column that identify what tab it needs to go on the new file (simple formula tool adding a column to the dataset)

Formula tool to create file path for where it needs to go (Step5.png)

output tool utilizing the bottom setting to dynamically write the output based on the file path created in the previous step. (Step6.png)

Again, not sure what your data looks like but this is how I have done it in the past.

step1.PNG

step3.PNG

step2.PNG

step5.PNG

step6.PNG

ppatane

@Jfoss This sounds very logical, I am leaving soon, so I will try this out 1st thing Monday morning. Grazie!

alexnajm

@ppatane yes those requirements do change my original suggestion, hopefully @Jfoss 's solution works for you!

binu_acs

@ppatane if you are getting a different schema error, use the batch macro instead of the dynamic input tool. Sample workflow attached. The output file will be saved with the name of the input file followed by "_Copy" in the folder where the input file is saved

Copy_Sheets_03022025.yxzp

ppatane

@binu_acs I think this is going to be the solution, but I am currently unable to tweak this to my needs. I need to move spreadsheet tabs "Load File", "Excel Format", and "Detail" from \\data1\user2\ppatane\Files\original data.xlsx to \\data1\user2\ppatane\Output\output data.xlsx. My Regex skills are quite minimal, so your solution worked well for the file names and locations you used but I failed at editing to get the results I need, Can you explain to me how i put in the proper paths. I can adjust the tab names

flying008

Hi, @ppatane

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

录制_2025_02_13_13_49_37_344.gif

$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."

binu_acs

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

make sure you have the output folder present

Copy_Sheets_03022025.yxzp

ppatane

@binu_acs 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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703