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.

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"

  • "excepted when there is a space somewhere in FileName" - do you get any error message? What? What exactly you've tried? Nov 1, 2016 at 13:52

2 Answers 2


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:

enter image description here

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

enter image description here

I have not tried saving to a network location.


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...

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .