Batch File to Copy Sheet to another Excel File

It's nice to see that the code finally does what you want on your machine. According to the prompt, some of the code for kill process requires you to run it with administrator privileges, so in order not to affect your normal function execution, please remove this part of your code &&@taskkill /f /im excel.exe .

By the way, please check if you have ntsd.exe this file in the C:\Windows\System32 directory?

All comments

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.

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

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

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

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

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.

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:

Sample Workflow.PNG

Sample dos copy to.PNG

Copy Sheet.PNG

Please advise.

Kamen

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.

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.

sample workflow bat.PNG

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

FYI.

录制_2024_03_07_10_03_56_881.gif

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?

what this represemt.PNG

Sorry for the question and my slowness,

Kamen

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.

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.

Destination file.PNG

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

FYI.

录制_2024_03_11_09_24_57_590.gif

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

Changing the contents.PNG

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

Financial Sample File

Tabs not exist.PNG

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

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.

录制_2024_03_14_12_41_00_336.gif

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

录制_2024_03_14_12_50_27_802.gif

5- Wish you have a good luck !

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:

new error01.PNG

new error02.PNG

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.

new error03.PNG

Please advise.

Kamen

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 ?

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.

worfklow bat.PNG

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

batcfh file.PNG

point bat.PNG

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

Kamen

@flying008

Btw, Im using Windows Server 2016 Standard.

Thanks,

Kamen

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.

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

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?

write file to database.PNG

See my workflow below..

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

Please advise.

Thanks,

Kamen

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

录制_2024_03_20_08_18_03_449.gif

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

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

录制_2024_03_22_08_49_25_196.gif

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

Finance orig.PNG

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

Finance after.PNG

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

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.

Thanks for the response.

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

Entered Code.PNG

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"

After Run.PNG

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

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

录制_2024_03_28_10_33_12_14.gif

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.

New error.PNG

New error01.PNG

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

New error02.PNG

Please advise.

Thanks,

Kamen

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.

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.

Controller nonformat.PNG

Is it maybe because of the formula tool?

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

controller input.PNG

Please advise.

Kamen

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.

@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

Bat code.PNG

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

Looking forward to hear from you.

Kamen

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.

录制_2024_04_02_14_50_00_787.gif

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)

BAT Code.PNG

Formula Tool (Replace)

Formula Replace.PNG

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

BAt Sheet.PNG

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

Kamen

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

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.

New bat file.PNG

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

Please advise.

Kamen

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

录制_2024_04_04_16_03_06_724.gif

First of all, thanks for your patience. happy vacation.

I found some misspelled in my work and corrected it from Financial to Financial. I've increased the BAT character to 3048. The truncation was gone but I still have some errors. The good news is that it is now able to produced the output. Please below snapshots in that maybe you can help me fix the errors.

Snapshot coming from Sample Tool

Snapshot from Run Command Tool

Results after Running the workflow

Errors found

Output - it produce the output I am looking for.

I think we are near to solve the issue. Need to solve the errors. Please advise.

Kamen

By the way, please check if you have ntsd.exe this file in the C:\Windows\System32 directory?

Removing the "&&@taskkill /f /im excel.exe" works. In line to ntsd.exe you have mentioned, I can't access the file since our drive C is somewhat inaccessible. We are given home drive to save our files.

Anyway thank you so much for your patience and your 100% assistance. Your expertise has greatly help me in completing this task. I have learned something from you and this BAT file of your will be very much helpful in my other projects.

Thank you,

Kamen