I have been reading a lot of posts regarding "save as" in macros and I can't get it to work correctly. Essentially, the last thing I want my macro to do is to open the save as window and default to the current filename and path.
The first problem is I can't get the current file name to show when the save as windows opens, it is blank. The code below- does work to populate the file name field as "test":
Application.Dialogs(Application.GetSaveAsFilename(InitialFileName:="test")).Show
However, I want "test" to be the current file name, let's call it "MySaveAsMacro." So I tried this:
Dim fileName as String
fileName = ActiveWorkbook.Name
Application.Dialogs(Application.GetSaveAsFilename(InitialFileName:=fileName)).Show
When I step through the code and hover over "fileName" in the third line, it is assigned "MySaveAsMacro." However, when the save as windows opens, the File name: field is blank. Entering a random string in "" works fine, but not when using a variable.
Any ideas? I guess will tackle the path later.
Thanks.
The first problem is I can't get the current file name to show when the save as windows opens, it is blank. The code below- does work to populate the file name field as "test":
Application.Dialogs(Application.GetSaveAsFilename(InitialFileName:="test")).Show
However, I want "test" to be the current file name, let's call it "MySaveAsMacro." So I tried this:
Dim fileName as String
fileName = ActiveWorkbook.Name
Application.Dialogs(Application.GetSaveAsFilename(InitialFileName:=fileName)).Show
When I step through the code and hover over "fileName" in the third line, it is assigned "MySaveAsMacro." However, when the save as windows opens, the File name: field is blank. Entering a random string in "" works fine, but not when using a variable.
Any ideas? I guess will tackle the path later.
Thanks.