vba excel 2010 Environ("username") not working in saveas filepath

vba excel-2010 username
cheapkid1 picture cheapkid1 · Jan 13, 2014 · Viewed 47.1k times · Source

For some reason I can get the Message Box to tell me what the username is, but it doesn't work the same way within the save as path. I get a "Cannot find path" error.

Not sure how to fix this. I've also looked at references, and they seem like they are correct.

If I change the path to include the actual username per computer, the macro works fine. But I need this to be a global macro for any computer/user. Any help would be greatly appreciated.

Here is what I have:

Sub SAVEAS_2010()
'
' SAVEAS_2010 Macro
MsgBox Environ("username")

Dim UserName As String
UserName = Environ("username")

ChDir "C:\Users\" & UserName & "\Dropbox\Open Machine Schedule"
ActiveWorkbook.SaveAs FileName:= _
    "C:\Users\" & UserName & "\Dropbox\Open Machine Schedule\Open Machine Schedule - Current_2.xlsx" _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

Answer

Suresh picture Suresh · Apr 22, 2014

Seems an old question. But for anyone who stumble on this post, adding $ worked for me - & Environ$("username") &

Related questions

Excel VBA Run-time error '13' Type mismatch

I created a macro for a file and first it was working fine, but today I've been opening and restarting the file and macro hundreds of times and I'm always getting the following error: Excel VBA Run-time error '13' …

Read More
Copy an entire worksheet to a new worksheet in Excel 2010

I have found similar questions that deal with copying an entire worksheet in one workbook and pasting it to another workbook, but I am interested in simply copying an entire worksheet and pasting it to a new worksheet -- in …

Read More
Copying and pasting data using VBA code

I have a button on a spreadsheet that, when pressed, should allow the user to open a file, then copy columns A-G of the spreadsheet "Data", then paste the data from those columns on the current sheet. I have a …

Read More