Automatically name a file based on cell data and function when saving a spreadsheet

Question

I have an .xltm template spreadsheet that I'm wondering if I can get a macro to populate the "save as" file name based on cell data, is this possible?

There are over 7 people who will have this spreadsheet, it's more of a form, and we are trying to figure out a way to keep the filenames uniform. I know there is the ThisWorkbook.BeforeSave, but I'm not really having any luck there. I just need it to make a file named something like $A$1 R $B$1 T $B$3.xlsx

how ever the problem im having is that one of the celss will have a function "Today()" and sometimes it will be a regular text the picture I included is off code that doeas work but only if the cell have a regular text in them but not a function, I quess it is due to it being a string or variable or something.

enter image description here

Any ideas on how to do this? anything helps thank very much.

Answer 1

I see from the image that you are working with Mac OS. The thing that comes to mind immediately is that the forward-slash character (/) is not allowed in a file name in Mac OS and by default, TODAY() is going to include the forward-slash character. Try using the replace function to switch it out for an underscore (_). So if your cell B7 uses the TODAY() function replace it with:

replace(range("B7").value,"/","_")

It also seems a bit strange to save an .xltm template as .xls file. Be sure you understand the workbook.saveas method including the fileformat argument

I think that part of your code should be:

ActiveWorkbook.saveAs strPath, xlExcel8