UserProfile environ on vba

Question

First I'm new to vba coding . I wrote a form in Access 2013 - VBA and created a function that generates a PDF/txt document on button click, thing is that boss wants it to save on a shared folder that´s located on %userprofile% path - like C:\Users\<username>\folder and we have a lot of users.

How can I add to the path of the SaveAs2 that i´m using without having to hardcode to each user?

Code is like:

file.SaveAs2 = ("C:\Users\username\folder\filename.pdf")

I tried defining code like:

Dim filepath as string 
filepath = environ("USERPROFILE")

and then:

file.saveas2 = (filepath &"\folder\filename.pdf") 

but still no success.

Thanks for any help

Answer 1

Sounds like you're trying to return the username of the logged in user?

Add a module, insert this code:

Option Compare Database

Declare Function wu_GetUserName Lib "advapi32" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

Function NetworkUser() As String

Dim lngStringLength As Long
Dim sString As String * 255

lngStringLength = Len(sString)
sString = String$(lngStringLength, 0)

If wu_GetUserName(sString, lngStringLength) Then
NetworkUser = Left$(sString, InStr(sString, Chr(0)) - 1)

Else
NetworkUser = "Unknown"
End If

End Function

Then if you want to return the network user, try something like this:

filepath =  = "C:\Users\" & networkuser() & "\folder\filename.pdf"

If you want to return the 'My Documents' folder, you could use something similar to what you were attempting above. This is wrapped in a function.

Public Function MyDocsPath() As String

     MyDocsPath = Environ$("USERPROFILE") & "\My Documents"

End Function

Then call it.

filepath = MyDocsPath & \filename.pdf")

Answer 2

I am a learner of VBA myself.

("C:\Users" & Environ("username") & "\folder\filename.pdf"

Substitute the folder, filename and file extension (.pdf) with yours.