0

I'm using the following VBA code to let the user choose a path to save a xlsm excel file as a xlsx file. The functionality is triggered by clicking a button in the xlsm excel file.

When I try it out, the name of the file in the saveas-window is empty, how can I set a default name (e.g. report_xyz) so the user does not have to type it himself?

'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogSaveAs).Show
'determine what choice the user made
If intChoice <> 0 Then
    'get the file path selected by the user
    strPath = Application.FileDialog(msoFileDialogSaveAs).SelectedItems(1)
    'displays the result in a message box
    Call MsgBox(strPath, vbInformation, "File got saved as")
End If

' Set to a xlsx file from xlsm
Application.DisplayAlerts = False 'switching off the alert button
Worksheets("Import_Sheet").Delete 'Delete Import Sheet
ActiveWorkbook.SaveAs strPath, FileFormat:=FileFormatNum  ' save with new name and format
Cribber
  • 2,513
  • 2
  • 21
  • 60

1 Answers1

1

Preset the "save as type" field while using Application.FileDialog(msoFileDialogSaveAs) with MSAccess

i think Application.FileDialog(msoFileDialogSaveAs).InitialFileName = "test" before intChoice = Application.FileDialog(msoFileDialogSaveAs).Show will do the job

Vincent Farcy
  • 91
  • 2
  • So simple... I saw the initialFileName attribute in the docs, but could not get it to work. Thanks! – Cribber Jul 16 '20 at 11:52