asked on

Excel VBA - issue with getsaveasfilename

Hi,

I've had some Excel VBA code that existed for years. The code opens a dialog allowing a user to choose a file name for saving a copy of an excel spreadsheet. The dialog is initialized with a file name based on the original spreadsheet name.

The code snippet below illustrates my problem. If the original spreadsheet name has no special characters, it works fine. If there is a "." in the file name, however, it leaves the file name blank when the dialog comes up.

I assume this is a holdover from when a "." in a file name was invalid. Does anyone know of an alternate that would this to work for file names with "."'s in them?

Dim strname As String

strname = "Test"
strname = Application.GetSaveAsFilename("COPY-" & strname, "Excel Macro Enabled Workbook (*.xlsm), *.xlsm," & _
    " Excel 2000-2003 Workbook (*.xls), *.xls,", , "Enter Published File Name")
MsgBox strname

strname = "Test.1"
strname = Application.GetSaveAsFilename("COPY-" & strname, "Excel Macro Enabled Workbook (*.xlsm), *.xlsm," & _
    " Excel 2000-2003 Workbook (*.xls), *.xls,", , "Enter Published File Name")
MsgBox strname

Open in new window

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

byundt

In addition to Rory's comment, you may wish to combine the *.xlsm and *.xls to avoid getting file names with doublequotes:

Sub Test()
Dim strname As String

strname = "Test"
strname = Application.GetSaveAsFilename("COPY-" & strname, "Excel Workbook (*.xlsm;*.xls), *.xlsm;*.xls", , _
                "Enter Published File Name")
MsgBox strname

strname = "Test.1.xls"
strname = Application.GetSaveAsFilename("COPY-" & strname, "Excel Workbook (*.xlsm;*.xls), *.xlsm;*.xls,", , _
                  "Enter Published File Name")
MsgBox strname
End Sub

Open in new window

Geri Richter

ASKER

rorya - Thanks for the assistance. I did some further testing. If I run the original code under Windows XP, it works as written. For my laptop - running Vista - I need to specify the file extension for the initial name. To play it safe, I will always be specific.

Thanks also to byundt - I'm incorporating your suggestion, too.