community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More

Use 3 Output Tools to a .XLSM format and rename the entire .XLSM file

Meteor

I have three different spots in my workflow where I am outputting successfully to 3 separate tabs of a .XLSM template file that already exists on a network location. After the 3rd tab is written to, I want to save that .XLSM file as a new .XLSM file with a different name that has the name of a field that I also specified in the workflow. I am using the "Overwrite Sheet (Drop)" option for all 3 Output Tools and then am selecting the "Change Entire File Path" and choosing the field with the desired file name in the "Field Containing File Name or Part of File Name". The output that I am seeing upon choosing these settings is just a .XLSM file with the three tabs that I am outputting to .XLSM and the rest of the tabs in that Template .XLSM file I was using for a destination are no longer included. 

 

What am I missing? Do I just need to choose "Change Entire File Path" for the 3rd of the Three Outputs?

Aurora

Hi @taxguy33 

 

In your 1st 3 outputs, you're writing to a file that already has the extra tabs defined in it.  In your last one, you're creating a new file with just the tabs that you've built in the workflow.  You probably want to copy the template file to the final location as the 1st step in the workflow and then write your 3 tabs to this copy.

 

Check out this post for a suggestion on how to copy a file from one location to another.

 

Dan 

Meteor

Hi @danilang ,

 

Thanks for the link to the post about how to copy a template file from one location to another. I am attempting to workthrough the steps jdunkerly79 mentioned in that post and am getting error from the Command / bat file. Do you know what type of .bat file I need to create or even how to create that (as well as what to change the "Write Source" and "Run External Program Command" fields to as part of the "Run Command" tool)?

 

I have a XLSM template file (there is also a .bak version of that XLSM file) that I am attempting to just copy to a different folder and can't seem to get this to work at all. 

 

Any further suggestions are welcome, thanks!

Aurora

Hi @taxguy33 

 

Can you post your workflow?  if not, then some details about the error as well as the batch file your using and what it's supposed to do?

 

Dan

Highlighted
Meteor

Hi @danilang ,

 

I have attached my sample workflow to this post where I am trying to just write a batch file to copy a template .xlsm file to a different location (from the C Drive / Desktop to the C Drive / Documents folder for now - want to perfect this before changing these to my network server locations) and also to rename the copied file with a date suffix. 

 

Please note that in the directory tool I have taken out my "Username" value between "Users\" and "Desktop" and "Documents" to remove my identity from this data - this will likely need to be changed to your username on your computer before you can test this workflow.

 

The workflow is supposed to write a batch script file to the C:\Users\Desktop location that's contents are below and then run that script.bat file as the 2nd part of the "Block Until Done" tool. 

 

xcopy /s "C:\Users\Desktop\FA SUBLEDGER REC - TEMPLATE.xlsm" C:\Users\Documents\
xcopy /s "C:\Users\Documents\FA SUBLEDGER REC - TEMPLATE.xlsm" "C:\Users\Documents\My Received Files\"
ren "C:\Users\Documents\My Received Files\FA SUBLEDGER REC - TEMPLATE.xlsm" FASUBLEDGERREC-TEMPLATE_

 

Unfortunately, I have only been able to get the workflow to output the "output.csv" file and copy the .xlsm file from the Desktop to the Documents folder, but the copy from the Documents folder to the Documents\My Received Files location has never worked and neither has the rename (obviously since the file never gets copies to the "My Received Files" location). I am constantly getting an error code 4 as part of the Run Command, which apparently means the system cannot open the .bat file for some reason.

 

Ultimately, if I am able to get this entire container fixed and working on my C drive, I'm almost positive I can attach this to my workflow I mentioned earlier.

 

Thoughts?

Labels