Export to My Documents using universal path 1

[cdck]

I'm exporting a query from access using a macro. I want to send the excel file to the user's "My Documents" folder - but on different machines the main drive has different letters (they're not all C). How can I denote a universal path for the "My Documents" folder so that the file will save there no matter what letter the user's main drive has?

Cheryl dc Kern

 

[PHV]

In VBA:
CreateObject("WScript.Shell").SpecialFolders("MyDocuments")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cdck]

I'm afraid I don't understand how to use that. I'm using a macro, and in the Macro builder, I've told it to export the query in question. One one line it asks for the destination and file name, which I need to type in a followable format. I've been told that there are wildcards that I could use that would direct the file into the user's My Documents folder, no matter what the letter of their main drive is. Can anyone show me how that looks?

If this is not possible in the Macro builder, please tell me that.

Cheryl dc Kern

 

[PHV]

I gave you a VBA code suggestion.
Sorry I know nothing about macros in access.
Nevertheless you may convert your macro to a VBA function ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CautionMP]

cdck,
This will work as the File Name argument of the TransferSpreadsheet action in a macro to create an Excel file named Excel File.xls in the current users My Documents folder:
[tab][tt]=Environ$("USERPROFILE") & "\My Documents\Excel File.xls"[/tt]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[cdck]

That worked! Thank you very much!

Cheryl dc Kern