GetSaveAsFilename File Name Won't Display

[MAM8433]

I want the user to save the current macro file as a non-macro Excel file. The user controls the file name and file location on a shared network. In the code below, I cannot get the current macro file name to populate the file name window of the Save As dialog box.

Otherwise, the code seems to work, because I can see the 3 Excel format options in the Save as Type window.

I have also tried (InitialFileName:=txtFileName,…), as well as (, Filefilter:=…) and (Filefilter:=…). Each time, the save-as box opens with nothing in the filename window. What am I missing?

My code:

‘Eliminate the macro file extension.
txtMacroFile = ActiveWorkbook.Name
txtFileName = Replace (txtMacroFile, ".xlsm", "")

'Display the saveas dialog box with xlsx as default format

FileName = Application.GetSaveAsFilename(txtFileName, FileFilter:= _
" Excel Macro Free Workbook (*.xlsx), *.xlsx," & _
" Excel 2000-2003 Workbook (*.xls), *.xls," & _
" Excel Binary Workbook (*.xlsb), *.xlsb")

If FileName = "False" Then Exit Sub

ActiveWorkbook.SaveAs FileName:=FileName

Thank you. I hope you can help.

 

[HaHoBe]

Hi MAM8433,

if macrofree you should only use xlsx for saving.

ActiveWorkbook.SaveAs FileName:=FileName

Maybe use different names for variables as those of a Parameter used by Excel:

Dim txtFileName As String, varFileName As Variant
'Eliminate the macro file extension.
txtFileName = Replace(ActiveWorkbook.Name, ".xlsm", "")

'Display the saveas dialog box with xlsx as default format

varFileName = Application.GetSaveAsFilename(txtFileName, FileFilter:= _
  " Excel Macro Free Workbook (*.xlsx), *.xlsx,")

If varFileName = "False" Then Exit Sub
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=varFileName, FileFormat:=51
Application.DisplayAlerts = True

Ciao,
Holger

 

[MAM8433]

Thanks, Holger: In my case, saving macro-free is paramount. I will restrict users to xlsx. Thank you, again!