Export spreadsheet to %userprofile%\My Documents

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All,
I want to export a query using code. I have the code working as long as I
have this path:
conPath = "C:\Documents and Settings\(myusername)\My
Documents\Billing_Backup\"

Since this is a database that I am only updating the structure on I would
like to be able to have a generic path such as:

conPath = "C:\Documents and Settings\%userprofile%\My
Documents\Billing_Backup\"

This is so I do not have change the path every time I update the user's
front end.

Any help would be greatly appreciated.

Thanks
 
Try using "%userprofile%\My Documents\Billing_Backup\" instead.
The %userprofile% specifies everything, including drive letter.
 
Thank you for your quick response, when I put this in:

conPath = "%userprofile%\My Documents\Billing_Backup\"

This is what I got:

Microsoft Excel cannot access the file 'C:\%userprofile%\My Documents\
Billing_Backup'. blah, blah, blah (The file name or path does not exist.)

???
Thanks,
NickX
 
To get the USERPROFILE environment variable, use
Environ("USERPROFILE")

But you can't rely on the user's My Documents folder being at
%USERPROFILE%\My Documents. Best to use WSH:

Function GetMyDocuments() As String
Dim oWSH As Object 'IWshShell

Set oWSH = CreateObject("WScript.Shell")
GetMyDocuments = oWSH.SpecialFolders("MyDocuments")
Set oWSH = Nothing
End Function

See
http://msdn.microsoft.com/library/d...html/14761fa3-19be-4742-9f91-23b48cd9228f.asp
for more.
 
Thank you very much. After scratching my head for a while trying to figure
out how to fit it into my "Sub" (most of my experience with coding has been
trial and error) and working it to where I wasn't repeating the path that
this function calls; it works wonderfully.

Thank you for making me think!
 
Back
Top