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

[flying008]

Hi, @KamenRider 

FYI.

[KamenRider]

HI @flying008 

Is it possible for you to kindly attached the workflow? I have a hard time understanding it and figuring how I can connect it with the workflow I've created. Can you please explain to me the Data of Sheet input file? the text tool containing the bat file code whose "from" is source file and "to" is destination file? Do I need to change the file name in my shared drive to SourceFile and DestinatinFile?

Sorry for the question and my slowness,

Kamen

[flying008]

Hi, @KamenRider 

The whole workflow animation is actually an idea for you to implement, Sorry due to security policy restrictions, I can't upload any workflow files.

******

1- Data of Sheet: The data here is an example of your source data, which is the data structure you want to output eventually.

2- Bat Code: The text here is the code you want to use, the strings "SourceFile" and "DestinationFile" in the middle are just to demonstrate your file path, if your file path and file name are fixed, you can put the file path directly into the code, without the need to replace it with the Formula tool later.

3- Formula tool for [Date]: It uses the formula tool to generate the date field you want, either manually or calculated using other fields.

4- Append Fields: It combines the previous data source with the date field, and does not require special configuration, just for the later calculations.

5- Formula tool for [Bat]: As mentioned earlier, if your file is not dynamic, then this step can be omitted, and if the file name needs to be replaced, then use the Replace function to implement the path and file name you want.

6- Block Until Done: This is a sequential running tool, which is mainly here to get you to make the .xlsx files you want, and then run the Bat code to achieve the actions you want. Of course, I don't know if the data file you want to output will affect your BAT code, and if not, this step can be omitted.

7+8- Select tool + Output: Unselected [Date] and [Bat] fields for output .xlsx later.

9- Select tool : Only retain [Bat] field, Only the field is left in order to generate a bat file and run it later, so all other fields must be filtered out.

10- Sample tool: only retain first 1 row for bat code once.

11- Run Command: Output the [Bat] field as a bat file, either in a temporary folder, or any path you want to specify, and then run it by filling in the absolute path of the file in the command line arguments. Thus enabling operations such as batch copying multiple worksheets or renaming or deleting them at once.

******

Above, if you still have doubts, please step by step elaborate on your workflow and logic, otherwise it will be difficult for me to help you further.

[KamenRider]

HI @flying008 

Thanks for the explanation. I have created a simple workflow before finalizing to check if I can successfully run it however I've got some error. Please see below screenshots for your reference.

 

 

 

 

 

I'm not sure why the batch file is not identifying the file to copy with. Please advise.

Please let me know if you have any questions.

Kamen

[flying008]

Hi, @KamenRider 

FYI.

[KamenRider]

Hi @flying008 

Tried some testing few days ago trying to fix the error but unfortunately I can't figure out what's  the cause. My files are in a network drive, the source and destination file and I am still receiving the error so I move my files temporarily to drive C and do the testing. Below are the screenshots for your reference.

 

These are the errors I've encountered.

 

This one states "UNC" paths are supported and there is statement "BAT" is not recognized as an internal or external command.

Whenever I run the workflow, the bat file contents changes from >>>

TO

 

and maybe because of the error, the output removes the other tabs, and resulted to containing the formatted and unformatted data.

 

Financial Sample File

 

The results are below (the finance and employee tab are gone). The Controller PnL tab increments every time I run it instead of overwriting it.

 

Please advise how can this be fix. I'm not sure why I am having this errors.

Thanks for your continued support and assistance.

Kamen 

[flying008]

Hi, @KamenRider 

Anyway, the format of bat file are not correctly by your output. maybe your option is wrong.

1- Please try to change your 'bat code' of [Bat] by Input tool as below [add '&&' before each line from 2th row, then you will be free.]

@echo off 
&& @setlocal enabledelayedexpansion 
&& @set "objFrom=D:\123\SourceFile.xlsx" 
&& @set "objTo=E:\456\DestinationFile.xlsx" 

&& @echo Copy WorkSheets From +++' %objFrom% '+++ To +++' %objTo% '+++ ... 

&& @powershell -Command "$objExcel = New-Object -ComObject Excel.Application; $objExcel.Visible = $false; $wkInput = $objExcel.Workbooks.Open('%objFrom%'); $wkOutput = $objExcel.Workbooks.Open('%objTo%'); foreach ($Sheet in $wkInput.Sheets) { if ($Sheet.Visible -eq -1) { $Sheet.Copy($wkOutput.Sheets.Item($wkOutput.Sheets.Count)); } }; $wkOutput.Save(); $wkOutput.Close(); $wkInput.Close(); $objExcel.Quit();" 

&& @taskkill /f /im excel.exe 
&& @echo *** All done ! ***

******

If above code return error, you need try this : (Join all code to one line)

@echo off && @setlocal enabledelayedexpansion && @set "objFrom=D:\123\SourceFile.xlsx" && @set "objTo=E:\456\DestinationFile.xlsx" && @echo Copy WorkSheets From +++' %objFrom% '+++ To +++' %objTo% '+++ ... && @powershell -Command "$objExcel = New-Object -ComObject Excel.Application; $objExcel.Visible = $false; $wkInput = $objExcel.Workbooks.Open('%objFrom%'); $wkOutput = $objExcel.Workbooks.Open('%objTo%'); foreach ($Sheet in $wkInput.Sheets) { if ($Sheet.Visible -eq -1) { $Sheet.Copy($wkOutput.Sheets.Item($wkOutput.Sheets.Count)); } }; $wkOutput.Save(); $wkOutput.Close(); $wkInput.Close(); $objExcel.Quit();" && @taskkill /f /im excel.exe && @echo *** All done ! ***

2- Option [First Row Contains Field Names] must be unchecked. (because you don't need the filed name, and it unless in bat file.)

3-  Output set.

4- So, Please try again, and check your bat file format after output. ( Does the path appear to have an extra slash symbol in the code you output? )

5- Wish you have a good luck !

[KamenRider]

HI @flying008 

I have change the code of the text input file and for the first option, I have an error that says error code 255. For the second one where all code join in one line, below is the error:

 

Maybe this error is the reason why it didn't give me the correct output or results. The output I received removed the other two tabs and gave me an unformatted data of Controller tab.

For the code, below is the result after I run the workflow. There no more extra slash but the date for Financial sample is missing.

 

Please advise.

Kamen 

[flying008]

Hi, @KamenRider 

Hello! I've seen your screenshot of the error prompt, so,

1- allow me to make a bold guess, you actually saved the bat file in a UNC path like "\\Server\ABC\RUN\Copy.bat", and you ran it from your local computer, that's why the screenshot prompt appears.

2- Those red error messages come from code part of PowerShell, and they are actually error because your code doesn't get to the correct folder path. I've improved the code structure, please use the code below.

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

3- Regarding the problem that the file name in the final output code is missing the date, please check the formula in your workflow again, it must output the correct destination path.

4- Finally, if you can, please tell me what version of the operating system you are using? like win7 or win10 ?

[KamenRider]

HI @flying008 

Quite an improvement in your code. I was able to run your new code and the errors found are as follows:

1. returned an error code: 1

 

2. BAT is not recognized as an internal  or external command. Not sure if this is a valid error.

 

 

3. the formatted data was successfully copied however, why is it there is an unformatted data (See below SCR2). And then the two tabs from financial sample are missing. Also, every time I rerun the workflow, the tabs added. It should be rewriting the file.

 

SCR2

 

 

Other references

 

Batch File

 

 

 

Thanks and hope to hear for your answers. Looking forward for your continued assistance.

Kamen