Window 7 - Excel 2010 setting? VBA ActiveSheet.SaveAs Filename not posting name to dialog box

[portews]

I recently changed over from a Windows XP box running Excel 2010 to a 64 bit Window 7 box running Excel 2010. One of my macros would bring up a save box upon opening, making the user save the template. I would populate the filename with a new name and allow the user to select the directory.

ThisFile = Worksheets("Transmittal").Range("F1").Value
newName = Application.GetSaveAsFilename("S:\Transmittal " & ThisFile & ".xls")
If newName = False Then GoTo do_not_overwrite
ActiveSheet.SaveAs Filename:=newName

On the new box, when the macro is run, the save dialog comes up, the the filename does not populate.

Is this a setting in Excel, a glitch in Windows 7 or a change in programming requirements?

Thanks!

 

[portews]

One of our inhouse Gurus came to my rescue. He found a link that explains this problem (see link for full explaination below). It has to do with the file filter criteria.

• if the extension of the filemane you provide is the same as the last one in the filter criteria, the name is not cleared
• if you do not provide an extension on the filename, the name is also not cleared
• if you supply an extension that is either not in the list of wildcard extensions or not the last one, the name is cleared (in XP the full name would be enclosed in double quotes)

The fix is to set a file filter criteria that will match the file type you want to save as before the save.

Dim sFilter as text
...
sFilter = "Excel Macro Enabled filter,*.xlsm;*.xlsb;*.xls,All Files,*.*"

Weird bug in Excel’s Application.GetSaveAsFileName() function on Vista and 7 | Dutch Gemini's Weblog Pages