Include space in name of file

Question

I need to save a file with some random spaces in the name.

It's in Excel Vba. All I found after one hour of net search are suggestions to either replace the space with %20 or avoid spaces in file name. I tried playing with adding quotes or putting %20 with no luck.

The file produced is for export to a legacy system, changing the names or replacing the space by an another character is not an option.

Also the spaces are not necessarily at the same place and I'd like to avoid putting a Case statement for 20 different names.

Here's the piece of code that causes me trouble.

Savepath=range("a1").value
NameOfFile=range("a2").value 
FullnameSave=savepath & "\"& NameOfFile & ".xlsm" 

ActiveWorkbook.SaveAs Filename:=FullNameSave, FileFormat:=xlOpenXMLWorkbookMacroEnabled

Everything works fine excepted when there is a space somewhere in FileName.

For example if the value in A2 is StandardPrice the file is saved fine, if it's standard price I get an error (error 1004 on SaveAs method). As well, if I hard code the name as follow, it works.

FullnameSave=savepath & "\" & "standard price" & ".xlsm" 

I tried to add 2,3,4 even 5 quotes around the file name but it never returns the correct format (which would be something like H:\Pip_Import\ImportTests2016-10\"standard price"

Answer 1

If you are saving to some local folder, there should be no problem. I took your code:

Sub SaveMePlease()
    savepath = Range("a1").Value
    Filename = Range("a2").Value
    FullNameSave = savepath & "\" & Filename & ".xlsm"
    ActiveWorkbook.SaveAs Filename:=FullNameSave, FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub

with this in the worksheet:

Note the space in the filename. Your code ran with no errors and produced:

I have not tried saving to a network location.

Answer 2

In the end it was nothing to do with the spaces in the file name. Rewriting the code to fit the format of SU and seeing that it worked on someone else's computer made me go back to the original code with a different eye.

There was a single space after the backslash " \ " in

FullnameSave=savepath & "\ " & NameOfFile & ".xlsm" 

What threw me off, is that this typo is ignored/corrected automatically if the name of the file does not contain any blank, but raise an error if there's one.

So the solution was simply to rewrite the problematic part of code from scratch and look for typos.

I'm not sure if that kind of solution fits SU but it worked for this specific problem...