Editing and saving a macro enabled file dynamically

[mkankaria]

Hi,

I have seen many posts about dynamically naming a file but none have proved successful in my case. I have a macro enabled file with multiple sheets in the workbook, in which one sheet is edited with new information and supposed to be saved as a new file with todays date. I am able to edit the sheet that needs change but the issue is with saving. So far I used the formula tool to add the new file path and at the end it does create a new file with todays date, however it only contains the one sheet I selected and edited in the input tool and loses all the other existing sheets and formatting. Any suggestions on how to retain all the sheets and formatting would be greatly appreciated. (I won't be able to upload any files due to work policies) 

[mkankaria]

Referenced  @DavidP 's solution in forum

[DataNath]

It’s kind of hard to diagnose with at least screenshots or your current output data configuration (I know you mention work policies but you could provide this with the file path censored).

As you have brought multiple sheets in, have you unioned these back together if they came from different input data tools?

You mention you have a formula that adds today’s date to the file name. Does that formula also include ‘|||<Sheet Name>’ where ‘<Sheet Name>’ is the field you’ll group on?

Without showing a full workflow we may need slightly more info/some censored screenshots if the above checks don’t work.

[mkankaria]

Unfortunately I won't be able to provide screenshots of any kind, sorry! I have a template workbook that contains all the sheets and macros, of that I pick the sheet that the new info goes into at the beginning with the input tool. Once the data is in I save it  as "overwrite sheet (drop)" with a specified range, this keeps the formatting and macros. The problem occurs when I try to take that saved workbook and try to create a new version of that with todays date. I once again select the sheet I edited in the new input tool, have it go through the formula tool with the file path and output it. But this time the output creates a new file with today's date and has the edited sheet but all other sheets and formatting are lost. Only one sheet is brought in but the others still remain when saved the first time without the file path formula, and yes the formula does include the field. I hope that makes it a little more easier to understand 

[DavidP]

Hi @mkankaria 

The best way to do this in my mind would be to create a DOS Copy command to copy and rename the original file to the required new file using the Run Command tool and then to write the new data to the file you just created. I'll look for an example of where I've done this before and post it here.

[DataNath]

If you only bring in the sheet that you just changed in the final step then you’ll also need to append the other sheets (from another input) to make sure they’re there for the final output, and then do the same overwrite range/sheets in your output, again using the formula you’ve created for the filepath to differentiate the sheets.

[DavidP · Accepted solution]

Hi @mkankaria 

Here are the details of what I mentioned earlier.

I point the Directory tool to a folder with an excel file in it. If you have other files in that folder, you'll want to use filters in the Directory tool or after it to ensure only the file that you want to make a copy of is included.

The file is then copied within the same directory to a new filename with a date stamp using a DOS command with the Run Command tool. This ensures that all your sheets are copied to the new file.

Once this is done, you can write data to this new file, either updating an existing sheet or creating a new sheet.

If you need to delete the original file, this can be included in the DOS command.

Have a play with this and let me know if you have any questions.

You'll have to repoint the Output Data tool to the file you want to copy.

[mkankaria]

Hi @DavidP 

I have been trying to implement your solution but I get a error- "The external program "% temp%\CopyTemplate.bat" returned error code :1" from the run command tool. The flow still runs but doesn't create a new file with the date and saves the info to the template file selected in the directory tool instead. Any suggestions/ideas I could try to fix it?

[DavidP]

Hi @mkankaria 

The error means that there is a syntax error in the DOS command that the Run Command tool is trying to execute.

Can you perhaps show me a screenshot of what the output of the directory tool is once you've run the workflow. Please expand the Directory field and the Filename field.

My example copies an .xlsx file - if you look at the first formula tool after the Directory tool, I create the new filename by replacing ".xlsx" in the original filename with "[yyyy_mm_dd].xlsx" so myexcelfile.xlsx becomes myexcelfile 2022_06_08.xlsx

If in your case it's an xlxm file, you need to change xlsx in the formula to xlxm,

so:

Replace([FileName],".xlsx", " "+DateTimeFormat(datetimetoday(),"%Y_%m_%d") + ".xlsx")

should be changed to

Replace([FileName],".xlsm", " "+DateTimeFormat(datetimetoday(),"%Y_%m_%d") + ".xlsm")

If this is not it, I'll hopefully be able to tell from you screenshot what the issue is. I'm looking for something like this:

[mkankaria]

@DavidP that's exactly what the issue was. It works flawlessly now! Thank you very much, this was extremely helpful!