Editing and saving a macro enabled file dynamically

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)

Output

Date Time

Common Use Cases

Help

Accepted answers

DavidP

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.

Create copy of excel file with datestamp and write data to it.yxmd

All comments

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

Hi @mkankaria

Here are the details of what I mentioned earlier.

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.

Create copy of excel file with datestamp and write data to it.yxmd

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?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703