Export spreadsheet to %userprofile%\My Documents

[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

 

[Guest]

Try using "%userprofile%\My Documents\Billing_Backup\" instead.
The %userprofile% specifies everything, including drive letter.

 

[Guest]

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

 

[John Nurick]

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.

 

[Guest]

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!