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

[gawa]

hi @KamenRider when I needed to do the similar thing, I went with Python tool.

You can manipulate Excel object by win32com library(I guess win32com is installed along with Designer as default, and no need to additionally install).

Though I cannot share my WF, please refer to the script inside Python tool. Even if you are not familiar with Python code, you can manage it somehow because it is a simple code.

[KamenRider]

HI @gawa 

May I know how I am going to enter it in python tool and in what part of the code I am going to enter the two excel filename?

Thanks,

Kamen

[KamenRider]

Hi,

I found a code, however it does not recognize the $file1 and $file2. Can someone help me with this? Please apologize that I am not expert on this so kindly provide a step step process on how it will be done. The batch file using the cmd tool will be the one to read it in Alteryx.

@Echo off
setlocal enabledelayedexpansion

$file1 = "C:\Users\Downloads\Copy Sheet\Controller PnL Template.xlsx" # source's fullpath
$file2 ="'C:\Users\Downloads\Copy Sheet\Financial Sample.xlsx" # destination's fullpath

powershell -Command "$xl = new-object -c excel.application;
$xl.displayAlerts = $false # don't prompt the user;
$wb2 = $xl.workbooks.open($file1, $null, $true) # open source, readonly;
$wb1 = $xl.workbooks.open($file2) # open target;
$sh1_wb1 = $wb1.sheets.item(2) # second sheet in destination workbook;
$sheetToCopy = $wb2.sheets.item('Controller PnL') # source sheet to copy;
$sheetToCopy.copy($sh1_wb1) # copy source sheet to destination workbook;
$wb2.close($false) # close source workbook w/o saving;
$wb1.close($true) # close and save destination workbook;
$xl.quit();
spps -n excel"

echo All Done.

Thanks and hoping to hear from someone.

Kamen

[flying008]

Hi, @KamenRider 

FYI.

@echo off
setlocal enabledelayedexpansion
set "objFrom=D:\123\Control2.xlsx"
set "objTo=E:\456\Financial sample.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 ! ***

[KamenRider]

Hi @flying008 

Thanks, the code works however can I add additional requirements -- the destination file added a date in the filename, it gets the Friday's date. For example:

Financial sample 2024.03.01.xlsx

Financial sample 2024.02.23.xlsx

Financial sample 2024.02.16.xlsx

The workflow gets the date from the formula tool. Is it possible to call the date in the workflow using the batch file? or can the batch file compute the Friday date? or can the batch read the file with date that has been produced by the workflow in order the insert the sheet name every time I run it?

Please advise. Kindly let me know if you have questions.

Kamen

[flying008]

Hi, @KamenRider 

1- Is it possible to call the date in the workflow using the batch file?
Yes, use the code "%date:~0,4%%date:~5,2%%date:~8,2%" in bat file, you can see date of today.
2- or can the batch file compute the Friday date?
Yes, If you can assign the date for compute weekday.
3- or can the batch read the file with date that has been produced by the workflow in order the insert the sheet name every time I run it?
Yes, you can compute the date and file name by formula tool, then use ran command tool to output bat file and run it at once.

[KamenRider]

Hi @flying008 

Can you please assist me again? I tried using the item # 1 below of connecting the workflow to the batch file and I am getting an error. See below details.

Sample workflow:

 

 

 

Please advise.

Kamen

 

 

[flying008]

Hi, @KamenRider 

1- Did you enter your date manually or was it calculated by a formula?

2- Is your bat file hand-coded or is it generated and output by Alteryx?

3- Is your code set "obj******" in the BAT file? If so, the syntax is wrong, you can just change the last part to \Copy Sheet\Finacial Sample20240301.xlsx, or the bat file code is all generated by Alteryx, because you don't know your date logic, so you can't provide further instructions.

4- BAT file code does not support syntax like Copy Sheet\Finacial Sample + [Date].xlsx,  so you need to think about where exactly to generate your path.

[KamenRider]

1- Did you enter your date manually or was it calculated by a formula?

The sample shows the date has been manually entered however In the workflow I've created, the date came from a filename where it has been extracted.

2- Is your bat file hand-coded or is it generated and output by Alteryx?

Actually, for now, I am not really sure if this will works. What I am going to do is to insert the command prompt and insert the code you gave me.

 

3- Is your code set "obj******" in the BAT file? If so, the syntax is wrong, you can just change the last part to \Copy Sheet\Finacial Sample20240301.xlsx, or the bat file code is all generated by Alteryx, because you don't know your date logic, so you can't provide further instructions.

Actually this is my dilemma now since the file is being updated with date in the filename when run everyday. I am not sure how can this file accept the tab or sheets to be added. I hope you can help me on this.

4- BAT file code does not support syntax like Copy Sheet\Finacial Sample + [Date].xlsx,  so you need to think about where exactly to generate your path.

I am not really good on this since I am a novice that needs to learn more. Is there a way you can think to solve this problem? Some says use python however I did not know how to use nor start it with Alteryx.

Looking forward for your continued assistance and help.

Thanks,

Kamen