VBA Save As with User prompted to select folder path

[lwebbie]

I have a macro that uses the GetSaveAsFilename which is fine except it makes the user type in the file name to save the file. The issue is people mistype the file name which messes up other macros down the line. The file name never changes but the folder location does so the user needs to be able to select the folder to save the file in but the file name will always be "Device Complianc.xlsx". One other thing to note the file they are doing a Save As to has the original format as .xls.

Here is the current code:
Sub sbSaveExcelDialog()
Dim IntialName As String
Dim sFileSaveName As Variant
IntialName = "Device Compliance"
sFileSaveName = Application.GetSaveAsFilename(InitialFileName:=InitialName, fileFilter:="Excel Files (*.xlsx), *.xlsx")
If sFileSaveName <> False Then
ActiveWorkbook.SaveAs sFileSaveName
End If
End Sub

 

[Fluff]

How about

Sub sbSaveExcelDialog()
   Dim Fldr As String

   With Application.FileDialog(4)
      .AllowMultiSelect = False
      If .Show <> -1 Then Exit Sub
      Fldr = .SelectedItems(1)
   End With

   ActiveWorkbook.SaveAs Fldr & "\Device Compliance.xlsx", 51

End Sub

 

[JLGWhiz]

For whatever reason, it does not like using a variable for the Initial Name.

Sub sbSaveExcelDialog()
 Dim IntialName As String
 Dim sFileSaveName As Variant
 'IntialName = "Device Compliance.xlsx"
 sFileSaveName = Application.GetSaveAsFilename("Device Compliance.xlsx", "Excel Files (*.xlsx), *.xlsx")
     If sFileSaveName <> False Then
         ActiveWorkbook.SaveAs sFileSaveName
     End If
 End Sub

This displayed the file name as the Initial Name.

 

[lwebbie]

Thank you that worked perfectly!

 

[SteveOranjinSteve]

Sub sbSaveExcelDialog()
   Dim Fldr As String

   With Application.FileDialog(4)
      .AllowMultiSelect = False
      If .Show <> -1 Then Exit Sub
      Fldr = .SelectedItems(1)
   End With

   ActiveWorkbook.SaveAs Fldr & "\Device Compliance.xlsx", 51

End Sub

How do you get this so it doesn't close the file that you save the file from?


Steve

 

[Fluff]

You don't, that's how SaveAs works. You could use SaveCopyAs, but I don't think you can change the filetype.

 

[JLGWhiz]

You can add a line after the SaveAs to re-open the original workbook.