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
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")