Eliminate Slashes from File Names When Saving with Macro

May 18, 2002 - by Juan Pablo Gonzalez

Deepak asks:

Hi, I am trying to assign the current date to a cell A38 using =NOW() and then do a SaveAs by assigning the contents of that cell as the file name for the file being saved. I am not being able to write the correct code for it .

ChDir "C:\Backup"
	ActiveWorkbook.SaveAs Filename: = Range("A38").Text, FileFormat:=xlNormal, Password:="", WriteResPassword:="",
	ReadOnlyRecommended:=False, CreateBackup:=False

In the above mentioned code all I need to do is to replace the filename by the contents of a cell A38 which will always contain the current date. I would appreciate if you would please let me know the correct way to write this code. I tried to replace Filename with a few things looking at tip025 in www.mrexcel.com in but it did not work.

The problem arises when the format of the date includes the “/” character… try the following:

ActiveWorkbook.SaveAs Application.Substitute(Range(“A38”).Text,”/”,”-“)