Getting correct default save name and save directory with spaces in VBA

Question

I have a lot of excel templates with varying names. One of them is called griep-weerstand v4.xlsb. But my question is about all the templates.

I want to integrate the filename into a save script which sets the default save directory and default save name. Both have spaces in the name. After adding the correct number of quotes, the default save directory is set correctly, however, I keep struggling with adding the workbookname to the script. I tried several things and none of them has worked so far.

the default save directory is: M:\Commercie\Marktdata\IRi\Segment ontwikkeling

the default file name (in this example) is: griep-weerstand v4.xlsb

Below is what I tried last:

Sub save_workbook_name()
Dim workbook_Name As Variant
Dim location As String
Dim workbookname As String
Dim workbookdirectory As String
Dim correctfilename As Variant

workbookname = ActiveWorkbook.Name
workbookdirectory = "M:\Commercie\Marktdata\IRi\Segment ontwikkeling\"

correctfilename = """M:\Commercie\Marktdata\IRi\Segment ontwikkeling\" & workbookname & """"
workbook_Name = Application.GetSaveAsFilename(fileFilter:="Excel binary sheet (*.xlsb), *.xlsb", initialfilename:=correctfilename)

If workbook_Name <> False Then

    ActiveWorkbook.SaveAs Filename:=workbook_Name, FileFormat:=50

End If
End Sub

I thought reading the workbookname as a string and adding the right number of quotes into the combined initialfilename would do the trick.

When I add a message box displaying the combined result, I get the correct path and name:

But I keep on having the save as dialog screen in the correct directory but without a filename when using this in the getsaveasfilename line.

How can I get the combination of the directory and filename to work with the getsaveasfilename? Or should I just use it in the activeworkbook.saveas script?

Answer 1

It seems a little complicated - have you not tried the simpler:

workbook_Name = Application.GetSaveAsFilename(fileFilter:="Excel binary sheet (*.xlsb), 
*.xlsb", initialfilename:=workbookdirectory & ActiveWorkbook.Name)

full code based on your example here: .. this works for me exactly as you want it (tested with a filename containing spaces)

Sub save_workbook_name()
Dim workbook_Name As Variant
Dim location As String
Dim workbookname As String
Dim workbookdirectory As String
Dim correctfilename As Variant

'workbookname = ActiveWorkbook.Name
workbookdirectory = "C:\Users\myusername\folder with spaces too\"

'correctfilename = """M:\Commercie\Marktdata\IRi\Segment ontwikkeling\" & workbookname & """"
workbook_Name = Application.GetSaveAsFilename(fileFilter:="Excel binary sheet (*.xlsb), *.xlsb", InitialFileName:=workbookdirectory & ActiveWorkbook.Name)

If workbook_Name <> False Then

    ActiveWorkbook.SaveAs Filename:=workbook_Name, FileFormat:=50

End If
End Sub