VBA to save a folder into current user's 'My Desktop'?

[MikeyW1969]

Hi all,
I am working on a project that I want to roll out to other users. I have set up a script with help from the folks here to create a new folder for today's date, and save my file based on a cell value. This is saving perfectly on my machine with the following code. I'm just not sure what I would have to change to make it save in the same location, but based on who the user is that is running the script. I see posts that reference %userprofile% and others that use 'Environ', but I'm not sure which yo use, and how to specifically plug it into the piece of code that I have. I've tried replacing my user name with %userprofile%, tried adding C:\ to the front, and tried C:\Users\%userprofile\, but none of those seem to work quite right. I am having trouble just figuring out how to combine the code I see on these posts, and the one I currently have. No idea where to start and end.

  ' Create Folder and Save File

 Dim myFolderName As String
    Dim myFileName As String
    
'   Build folder name with today's date
    myFolderName = "C:\Users\mlwells\Documents\Work_Documents\Daily_Work_Notes\" & Format(Date, "mm_dd_yy")


'   Check to see if folder name exists already.  If not, create it
    If (Dir(myFolderName, vbDirectory)) = "" Then MkDir myFolderName


'   Build file name
    myFileName = Sheets("Working Ticket").Range("B1") & ".xlsm"
    
'   Save file
    ActiveWorkbook.SaveAs Filename:=myFolderName & "\" & myFileName, _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

That's the full code, more than I need to show, I know, but I figured it might help with the context of my request. I'm looking for Excel to create a folder in Documents\Work_Documents\Daily_Work_Notes based on that day's date and save a file with the value in cell B1 on the sheet named 'Working Ticket'. Any help would be GREATLY appreciated. Thank you!

 

[John_w]

userprofile is a Windows environment variable. %userprofile% would be used in a batch script to get the value of the variable, and Environ is a VBA function which returns the value of the specified environment variable, e.g. Environ("userprofile"). An alternative to Environ is WScript.Shell SpecialFolders - Special folders | VBScript | SS64.com.

Your post is a little ambiguous - the thread title says "My Desktop", but the code is referring to the Documents folder. Assuming you mean the user's Documents folder, you would call Environ and append the subfolders path like this:

    myFolderName = Environ("userprofile") & "\Documents\Work_Documents\Daily_Work_Notes\" & Format(Date, "mm_dd_yy")

 

[MikeyW1969]

D'oh! One of the posts I had been looking at was about saving the file to the Desktop. My bad. "My Documents" was what I was looking for. Not sure if I can edit the post topic, but I'll try that.

Testing right now. Worked on my computer, trying it on another user's machine. Fingers crossed...

Worked perfectly!! Thank you!