1

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

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Alex
  • 487
  • 1
  • 5
  • 10
  • What is the value of `filepath` ? What happens when you try to use it? – Alex K. Feb 07 '17 at 14:13
  • 1
    If this is MS Word then `saveas2` is a Method not a Property so lose the equals sign: `file.saveas2 filepath &"\folder\filename.pdf"` – Alex K. Feb 07 '17 at 14:15
  • I´edited the question but it is `filepath = Environ("USERPROFILE")` – Alex Feb 07 '17 at 14:16
  • Even after setting the variable no file is saved to the destination folder – Alex Feb 07 '17 at 14:17
  • What exactly does "no success" mean? Do you get an error? Does it save to the wrong location? This is a bit difficult to answer without knowing the exact issue you're having with the code you included. – Comintern Feb 07 '17 at 14:17
  • @Comintern - No errors, it creates the file and opens adobe reader, but no file is created on the destination folder. – Alex Feb 07 '17 at 14:18
  • Where is Adobe opening it from? – Comintern Feb 07 '17 at 14:19
  • @Comintern - I´ve used a code that sets Adobe as `apppdf.Active` `apppdf.Visible = True` so after clicking the button it goes to the application and saves it - I guess at this moment it is openning from memory – Alex Feb 07 '17 at 14:21
  • If I Hardcode the path all goes really smooth, problem is when trying to add the variable to path – Alex Feb 07 '17 at 14:22
  • @AlexK. no changes, still no file saved to folder – Alex Feb 07 '17 at 14:25
  • Does `msgbox filepath` actually show you `c:\users\whatever` ? – Alex K. Feb 07 '17 at 14:31
  • @AlexK. - Changed the placement to the end of the code showed the full path C:\Users\ that came out right, now – Alex Feb 07 '17 at 14:34
  • AFAIK, Adobe doesn't open documents from memory. Put `Debug.Print Environ("USERPROFILE")` in your code and make sure it's returning the result you're looking for. – Comintern Feb 07 '17 at 14:35
  • I can see the finished document after clicking to export, just it does not save it on the defined path, could this be because the template path and the destination are different? – Alex Feb 07 '17 at 14:54
  • So I´ve just deleted the destination folder and recreated with same name and it worked, could this be a network issue? Should I talk to my admin? – Alex Feb 07 '17 at 15:00
  • THANKS A LOT, really to all you guys for the tips and time spent helping me. – Alex Feb 07 '17 at 15:05

2 Answers2

4

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")
RyanL
  • 1,246
  • 2
  • 10
  • 14
1

I am a learner of VBA myself.

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

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

Sam
  • 15
  • 5