SaveAs Dialog Box with Custom Filename and .xlsx extension

[bigmacfann]

Hello, I have been working on a macro to save a file with a custom filename but I can't seem to find a way to put the .xlsx extension on the end of the filename.

Dim sFile As String
     sFile = Application.GetSaveAsFilename("RFI " & Range("G5").Text)

If I have "120" in cell G5, ideally, the filename will be "RFI 120.xlsx" but currently all that shows up is "RFI 120" and I do not have the option to select .xlsx in the 'Save As Type' drop down. Any help would be greatly appreciated! Thank you.

 

[MDuff]

Hello, I have been working on a macro to save a file with a custom filename but I can't seem to find a way to put the .xlsx extension on the end of the filename.

Dim sFile As String
     sFile = Application.GetSaveAsFilename("RFI " & Range("G5").Text)

If I have "120" in cell G5, ideally, the filename will be "RFI 120.xlsx" but currently all that shows up is "RFI 120" and I do not have the option to select .xlsx in the 'Save As Type' drop down. Any help would be greatly appreciated! Thank you.

Here is one way (although my VBA is limited I am sure some one knows a better way

Sub Save_me()
Dim sFile As String
     sFile = ("RFI " & Range("G5").Text)
 
 ActiveWorkbook.SaveAs Filename:=sFile, FileFormat:=xlOpenXMLWorkbookMacroEnabled
   
   
End Sub

 

[bigmacfann]

MDuff,

Thank you for your reply. Unfortunately, the line of code provided did not add .xlsx to the end of the custom filename. Also, if the user clicks 'Cancel', it automatically saved the file as 'False.xlsx'

I should mention that in addition to adding the extension .xlsx to the end of the custom filename, I would like the user to have the ability to specify which folder to save the file in.

This piece of code is at the end of a macro which takes data from all over another workbook and adds it to a new workbook called 'RFI_Temp.xlsx'. If the user clicks 'Cancel', I would like the filename to remain as RFI_Temp.xlsx and not save. Thank you everyone in advance!

 

[MDuff]

Maybe you can find somehtings here to help you or an other (and more wise) excel Guru will post sometinng else for you


http://msdn.microsoft.com/en-us/library/ff458119(office.11).aspx