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]

@flying008 

Btw, Im using Windows Server 2016 Standard.

Thanks,

Kamen

[flying008]

Hi, @KamenRider 

1. returned an error code: 1
After you fixed 2 and 3, skip it.

2. BAT is not recognized as an internal or external command. Not sure if this is a valid error.
Again!  Option [First Row Contains Field Names] must be unchecked at you output bat file. (because you don't need the filed name, and it unless in bat file.) BTW, Before replacing the file names in the code using formulas, you must check that the [Bat] field has sufficient Size, as I noticed in your code screenshot that the final code was truncated and incomplete. Please ensure the completeness of the code.

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.
Try blow code:

@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 = $excel.Workbooks.Open($objFrom); if (Test-Path $objTo) { $wkOutput = $excel.Workbooks.Open($objTo) } else { $wkOutput = $excel.Workbooks.Add() }; foreach ($Sheet in $wkInput.Sheets) { $sheetName = $Sheet.Name; if ($Sheet.Visible -eq -1 -and $wkOutput.Sheets.Item($sheetName)) { $tmpShtName = $sheetName + \'_tmp\'; $Sheet.Name = $tmpShtName }; $Sheet.Copy($wkOutput.Sheets.Item($wkOutput.Sheets.Count)) }; foreach ($Sheet in $wkOutput.Sheets) { if ($Sheet.Name.EndsWith(\'_tmp\')) { $Sheet.Delete() } }; $wkOutput.Save(); $wkOutput.Close(); $wkInput.Close(); $objExcel.Quit();" &&@taskkill /f /im excel.exe &&@echo *** All done ! ***

[KamenRider]

Hi @flying008 

Thanks for the reply. Sorry, I think I missed the output bat file. I don't have it in my workflow. Shall I change the command prompt with an output bat file? If yes, what file I am to reflect with in the "Write to File or Database Field?

 

2. BAT is not recognized as an internal or external command. Not sure if this is a valid error.
Again!  Option [First Row Contains Field Names] must be unchecked at you output bat file. (because you don't need the filed name, and it unless in bat file.) BTW, Before replacing the file names in the code using formulas, you must check that the [Bat] field has sufficient Size, as I noticed in your code screenshot that the final code was truncated and incomplete. Please ensure the completeness of the code.

See my workflow below..

Also, how can i increase the size of the BAT field? What size or length?

Please advise.

Thanks,

Kamen 

[flying008]

Hi, @KamenRider 

If you can , please upload your .yxmd file for check and test.

[KamenRider]

Hi @flying008 

I apologize if I can't upload the workflow since it is restricted. How I hope we can solve this so I can finalize this project. See screenshot below, the configuration of the command prompt seems correct.

 

This is the structure of the workflow. I can't figure out the problem why the results is removing the other tabs.

 

How I really I can upload the workflow so it would be easy. Please bear with me. Need your help with this.

Thanks,

Kamen

[flying008]

Hi, @KamenRider 

So, you need upload the 4 screenshot about the flow.

[KamenRider]

Hi @flying008 

Sorry for late reply for I am sick for a few days. Meantime here's the screenshots for your reference.

1. Original Financial Sample Reports

2. Sample report after run. As you can see, the Finance and Employee are missing and the Controller report is not in formatted format.

3. Snap shot of Controller report

 

4. BAT File after run. Please be advise that I am temporarily use the Home drive. I do hope that it will also accept a network drive.

 

I do hope we can solve this. Thanks and looking forward for your response.

Kamen

 

 

[flying008]

Hi, @KamenRider 

Wish you a speedy recovery!

Judging from the screenshot of your code, you still didn't follow my prompt, line 1 still outputs 'BAT'! It's unnecessary! Also, the last part of your code seems to be incomplete, you can check again.

@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'; $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 ! ***

I've tested this code in its entirety again, and it works very well, so if you still get an error over there, you  need to double-check the content of your code.

[KamenRider]

Hi @flying008 

Thanks for the response.

The way I entered the code in the BAT file is like this:

 

But after I run the workflow, and check the BAT file, it turns out to be like this which might be the reason I have error, "The external program.... returned an error code: 1"

Would you why that BAT line is being generated and the last line is being cut off even though I entered it correctly?

Can you send me the screenshots one by one of your workflow? I'd also to check what went wrong in the workflow I have created and why it doesn't match with yours. Maybe I could find something.

Thanks and looking forward.

Kamen

 

[flying008]

Hi, @KamenRider 

After you modify the options, please give feedback on the results of your run.