Obtaining Desktop path from VBA code

[trifest]

I was wondering if there was some environment variable i could call from the vba code to obtain the users desktop path since they'll be different because of the Windows version and user profile.

i'm trying to copy a file to their desktop automatically but need to dynamically pull the desktop path from the code. any help would be greatly appreciated.

 

[oharab]

If you have Scripting available:

Dim WshShell As Object
Dim strDesktop as String
set WshShell = WScript.CreateObject("WScript.Shell")
strDesktop = WshShell.SpecialFolders("Desktop")

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[oharab]

If you don't have WScript, there are some api codes at vbNet
which will work and are easily used in Access.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------

 

[syncmaster955]

Hi trifest. This worked for me to export data from a query into Excel, and save it in the user's 'My Documents' folder. You can just replace "\My Documents\" with "\Desktop\".
This won't work with Win 9x/ME though. I believe using oharab's method would work better if you are trying to do it for multiple OSs.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "QueryName", _
"C:\Documents and Settings\" & Environ("username") & "\My Documents\" & strFileName & ".xls", True,