topic Re: Batch File to Copy Sheet to another Excel File in Alteryx Designer Desktop Discussions

Batch File to Copy Sheet to another Excel File

KamenRider — Thu, 29 Feb 2024 11:15:04 GMT

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

Re: Batch File to Copy Sheet to another Excel File

gawa — Thu, 29 Feb 2024 11:49:21 GMT

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.

Re: Batch File to Copy Sheet to another Excel File

KamenRider — Thu, 29 Feb 2024 13:02:40 GMT

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

Re: Batch File to Copy Sheet to another Excel File

KamenRider — Fri, 01 Mar 2024 21:17:00 GMT

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

Re: Batch File to Copy Sheet to another Excel File

flying008 — Sat, 02 Mar 2024 08:15:37 GMT

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 ! ***

Re: Batch File to Copy Sheet to another Excel File

KamenRider — Mon, 04 Mar 2024 11:54:13 GMT

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

Re: Batch File to Copy Sheet to another Excel File

flying008 — Tue, 05 Mar 2024 00:26:33 GMT

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.

Re: Batch File to Copy Sheet to another Excel File

KamenRider — Tue, 05 Mar 2024 11:15:16 GMT

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

Re: Batch File to Copy Sheet to another Excel File

flying008 — Wed, 06 Mar 2024 01:21:38 GMT

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.

Re: Batch File to Copy Sheet to another Excel File

KamenRider — Wed, 06 Mar 2024 10:46:24 GMT

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.

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

Re: Batch File to Copy Sheet to another Excel File

flying008 — Thu, 07 Mar 2024 02:07:31 GMT

Hi, @KamenRider

FYI.

Re: Batch File to Copy Sheet to another Excel File

KamenRider — Thu, 07 Mar 2024 17:42:36 GMT

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

Re: Batch File to Copy Sheet to another Excel File

flying008 — Fri, 08 Mar 2024 00:57:21 GMT

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.

Re: Batch File to Copy Sheet to another Excel File

KamenRider — Fri, 08 Mar 2024 20:51:15 GMT

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

Re: Batch File to Copy Sheet to another Excel File

flying008 — Mon, 11 Mar 2024 01:26:11 GMT

Hi, @KamenRider

FYI.

Re: Batch File to Copy Sheet to another Excel File

KamenRider — Wed, 13 Mar 2024 14:54:27 GMT

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 >>>

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

Re: Batch File to Copy Sheet to another Excel File

flying008 — Thu, 14 Mar 2024 05:20:00 GMT

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)

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 !

Re: Batch File to Copy Sheet to another Excel File

KamenRider — Thu, 14 Mar 2024 19:42:05 GMT

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

Re: Batch File to Copy Sheet to another Excel File

flying008 — Fri, 15 Mar 2024 03:24:09 GMT

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 ?

Re: Batch File to Copy Sheet to another Excel File

KamenRider — Mon, 18 Mar 2024 17:33:10 GMT

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