How to express a lone "\" in a file path in VBA?

0

I'm using a VBA script to save a file titled "month-day-hour-minute" inside of a folder titled "month-year"

Anyways, excel doesn't like my code, 

ActiveWorkbook.SaveAs "C:\Users\" & Environ$("UserName") & _ 
"\Documents\Workout Logs\" & _
Format$(Date, "mmmm-yyyy") & _ 
"\" & _ 
Format$(Date, "mmmm-dd") & Format$(Time, "hh-mm") & ".xls"

Specifically this part

"\" & _

How do I express the \ break in the pathing in a way that it likes so it sees 

Format$(Date, "mmmm-yyyy") & _

as the folder in which the file that will be saved as

Format$(Date, "mmmm-dd") & Format$(Time, "hh-mm") & ".xls"

should be placed in?

Valerie

Posted 2016-05-19T16:23:14.917

Reputation: 3

well, VBA/VBS doesn't require escape sequences except for doublequotes, so while I would normally suggest that you double your backslashes, it probably won't help. have you tried splitting it into multiple lines? VB editors can often be confused by & _ and if you are having trouble concatenating multiple expressions, and those expressions are the result of a function call, there can be casting issues that are hard to anticipate. try generating the path tokens seperately in their own vars, and finally concat them together. – Frank Thomas – 2016-05-19T16:35:48.333

After a short break cursing MS, my first attempt would be to include the backslash in the date format, but I haven't checked the manual. It's just that sometimes these formatting functions are more flexible than one tends to think. -- I wouldn't exclude that it might need to be escaped there, although the other comment suggest that this is not the case. – Run CMD – 2016-05-19T16:39:52.700

You could use CHR$(x) where x is the asci code of \ – Aganju – 2016-05-19T17:01:41.810

Answers

0

This runs fine on my computer (Windows Excel 2013)

Sub test()
a = "C:\Users\" & Environ$("UserName") & _
"\Documents\Workout Logs\" & _
Format$(Date, "mmmm-yyyy") & _
"\" & _
Format$(Date, "mmmm-dd") & Format$(Time, "hh-mm") & ".xls"

MsgBox (a)

End Sub

Msgbox results C:\Users\username\Documents\Workout Logs\May-2016\May-1910-13.xls

Its the same thing it just saves the string to a variable instead of saving the document.

gtwebb

Posted 2016-05-19T16:23:14.917

Reputation: 2 852

So it does, I guess I was making an assumption that it was the "". I still get an error message but apparently it's unrelated that. – Valerie – 2016-05-19T19:55:27.670

Asked: 2016-05-19T16:23:14.917

Viewed: 456 times

Active: 2016-05-19T17:14:33.007