Batch File to Copy Sheet to another Excel File

[KamenRider]

Good Day,

Is there anyone from you who could write an autoexec.bat file that will copy the sheets to another excel file? The copy will be a clone with out any changes in the file formats. I am not very much knowledgeable on this but I am thinking maybe this will works since I have tried lot of things to solve this. I have a master file with multiple sheets and I would like to add a sheet coming from different excel file. I have tried many ways like using the blob tool but it is not working.

Below are two sample excel file. The goal is to copy the sheet from Control.xlsx to Financial sample.xlsx without any changes in its formats. Financial sample is the master file.

Looking forward for some bright ideas there.

Kamen

[KamenRider]

HI @flying008 

Thanks for the instructions. After making the modification, the BAT line in the BAT file is gone and the whole code does no longer have a cut off. However, here's the error I've encountered.

 

 

The output still does not contain the other two tabs. It contained two formatted Controller report.

 

Please advise.

Thanks,

Kamen

[flying008]

Hi, @KamenRider 

I see an error message after you run bat, since there is no problem with your bat code output, but the output Sheet is not what you want, now the biggest problem is likely to be in your source file Financial Sample 2024-03-2*.xlsx itself, the error code part of the prompt and the red explanation all indicate that the code is not executing normally because of your source file Financial Sample 2024-03-2*. xlsx only contains 1 sheet, and the output result of your bat after multiple executions is always n sheets named Controller PnL, so according to my analysis, it is likely that your source file Financial Sample 2024-03-2*.xlsx contains only 1 sheet named Controller PnL, and the bat code itself is now without any problems, so in order to reproduce my guess, Please do the following:
1- Disabled the Run Command tool.
2- Run the workflow.
3- Open your Financial Sample 2024-03-2*.xlsx by Output tool.
4- post the screenshot of sheet name list.
5- If the sheet [Finance] and [Employee] are not in Financial Sample 2024-03-2*.xlsx, then maybe your have a mistake in the input.

6- Anyway, please check and take a screenshot of the Excel file you generated with the output tool Financial Sample 2024-03-2*.xlsx the names of all sheets.

[KamenRider]

Hi @flying008 

You are right in your assumptions. The output sheet names are not correct since both Finance and Employee tabs are missing. The result also shows non formatted Controller PnL.

Is it maybe because of the formula tool?

 

 

 

My input file is the Controller Pnl Report, the formatted one.

Please advise.

Kamen 

[flying008]

Hi, @KamenRider 

So, we need to go back to the beginning, could you please describe in detail what exactly you wanted? If your source file is Control.xlsx and financial sample.xlsx, then:

1- Do you need to rename  financial sample.xlsx file name with the date of the current day to like financial sample 2024-03-30.xlsx ?

2- You don't need to make any changes to the Control.xlsx, just copy its sheet into the financial sample 2024-03-30.xlsx file?

3- Does your original financial sample.xlsx contain at least 2 Sheets: [Finance] and [Employee]?

4- If your requirements are limited to what I guessed above, then it doesn't make sense for you to output the [Controller PnL] sheet with Output.

5- We've come full circle, and if you want to get a formatted sheet that the original file doesn't have, it's clear that the Output tool won't be able to satisfy you.

6- Finally, if you can accurately describe your Input and Output in Screenshot, maybe we can achieve your expectations with bat alone.

[KamenRider]

@flying008  - please see my response below.

So, we need to go back to the beginning, could you please describe in detail what exactly you wanted? If your source file is Control.xlsx and financial sample.xlsx, then:  - Financial sample is being updated wit the date. In this file, the Controller is being added at ease (with formats)

1- Do you need to rename  financial sample.xlsx file name with the date of the current day to like financial sample 2024-03-30.xlsx ?

YES

2- You don't need to make any changes to the Control.xlsx, just copy its sheet into the financial sample 2024-03-30.xlsx file?

YES. Copy at ease (the formatted type)

3- Does your original financial sample.xlsx contain at least 2 Sheets: [Finance] and [Employee]?

YES however this is for just for this sample. Originally, in the project I am creating, there are more tabes being connected and added.

4- If your requirements are limited to what I guessed above, then it doesn't make sense for you to output the [Controller PnL] sheet with Output.

What do you mean? The Controller PnL tab is being copy to the Financial report without ruining its formats.

5- We've come full circle, and if you want to get a formatted sheet that the original file doesn't have, it's clear that the Output tool won't be able to satisfy you. - The original Controller PnL report does have formats and this is the copy I wanted to be copied to financila reports.

6- Finally, if you can accurately describe your Input and Output in Screenshot, maybe we can achieve your expectations with bat alone.

See snapshots.

 

 

 Input BAT code

 

Output - Controller PnL to be copied to Financial Report + Date today

 

 

Looking forward to hear from you.

Kamen

[flying008]

Hi, @KamenRider 

Depending on your needs, I've added the ability to copy files to the Bat code, and now you don't need the Output Tool.

1- Please replace the old BAT code with the new one.

@echo off &&setlocal enabledelayedexpansion &&@echo Copy WorkSheets From ***' SourceFile.xlsx '*** To ***' DestinationFile.xlsx '*** ... &&powershell -Command "Add-Type -AssemblyName 'Microsoft.Office.Interop.Excel'; $objExcel = New-Object -ComObject Excel.Application; $objExcel.Visible = $false; $objFrom = 'D:\123\SourceFile.xlsx'; $objTo = 'E:\456\DestinationFile.xlsx'; $objSample = 'D:\123\InputSample.xlsx'; Copy-Item $objSample $objTo; $wkInput = $objExcel.Workbooks.Open($objFrom); $wkOutput = $objExcel.Workbooks.Open($objTo); foreach ($inSht in $wkInput.Sheets) { foreach ($exSht in $wkOutput.Sheets) { if ($exSht.Name -eq $inSht.Name) { $objExcel.DisplayAlerts = $false; $exSht.Delete(); $objExcel.DisplayAlerts = $true } } $inSht.Copy($wkOutput.Sheets.Item($wkOutput.Sheets.Count)) }; $wkInput.Close(); $wkOutput.Save(); $wkOutput.Close(); $objExcel.Quit()" &&@taskkill /f /im excel.exe &&@echo *** All done ! ***

2- Replace the [BAT] field with one more Repalce function in the Formula tool, which is to put the absolute path of your current Finacial Sample.xlsx into the code, so that it is ready to copy the file.

BAT =  REPLACE( REPLACE(REPLACE([BAT], "SourceFile", "H:\aaa\Controller PnL Template.xlsx"), "DestinationFile", "H:\bbb\Finacial Sample " +  [Date] + ".xlsx"), 'InputSample', 'Absolute Path of your Existing Finacial Sample.xlsx')

3- Please check the formula and code carefully and you can run the workflow, if the result still does not meet your expectations, please post the code and take a screenshot of any error prompts.

[KamenRider]

Hi @flying008 

Let me upload the snapshots and kindly check since it is not producing an output. There is an error produced in the command prompt however I can't catch it since disappear very fast.

The updated workflow

 

The BAT Code (Input)

 

Formula Tool (Replace)

 

The results found in the BAT file. I've increased the size of BAT file to 3048 but still it is being truncated.

 

 

I tried looking what went wrong but I am not able to see it. Everything seems right. Please advise.

Kamen

[flying008]

Hi, @KamenRider 

Seeing your screenshot, I guess I already know what the problem is, now you just need to replace the BAT code with the following, don't change other parts of the workflow, and then run.

- Remember, if everything works fine, then delete the last statement "&&pause" in the code.

@echo off &&setlocal enabledelayedexpansion &&@echo Copy WorkSheets From ***' SourceFile '*** To ***' DestinationFile '*** ... &&powershell -Command "Add-Type -AssemblyName 'Microsoft.Office.Interop.Excel'; $objExcel = New-Object -ComObject Excel.Application; $objExcel.Visible = $false; $objFrom = 'SourceFile'; $objTo = 'DestinationFile'; $objSample = 'InputSample'; Copy-Item $objSample $objTo; $wkInput = $objExcel.Workbooks.Open($objFrom); $wkOutput = $objExcel.Workbooks.Open($objTo); foreach ($inSht in $wkInput.Sheets) { foreach ($exSht in $wkOutput.Sheets) { if ($exSht.Name -eq $inSht.Name) { $objExcel.DisplayAlerts = $false; $exSht.Delete(); $objExcel.DisplayAlerts = $true } } $inSht.Copy($wkOutput.Sheets.Item($wkOutput.Sheets.Count)) }; $wkInput.Close(); $wkOutput.Save(); $wkOutput.Close(); $objExcel.Quit()" &&@taskkill /f /im excel.exe &&@echo *** All done ! *** &&pause

[KamenRider]

Hi @flying008 

I copy paste the code below to the BAT code as is (did not change anything) and still found an error in the command prompt. Unable to read what error it is since it disappear immediately. Below is the snapshot coming from the BAT file after the run.

 

As you can see, it is still truncated even thought I've increased the character length to 4048.

 

 

Please advise.

Kamen

[flying008]

Hi, @KamenRider 

- Under normal settings, this sample tool will only output 1 line of code, even if the field is too long, there will be no multiple "*** was truncated" prompted by your screenshot (only 1 time at most). So, you have to double-check the settings of each of your tools, exactly what went wrong.

- Also, add a Replace function to the [BAT] formula in your Formula tool, replacing all \ characters in your code with the / characters. This is done to avoid failures caused by code parsing errors, although the code I have worked fine with me, but you can try it.

******

I'm on vacation from today, so I'll probably reply to your message until next Monday.