I'm changing over some of my record export features to csv rather than Excel due to ever increasing results. When using transfertext a required argument is filename/path. Since this screen will be used on different computers where the paths and usernames aren't always known I was hoping to just put an environment variable like %HOMEPATH%\Report.csv, but that isn't accepted as a valid path, nor are any combinations like C:\Document and Settings\%USERNAME%\My Documents\Report.csv
I suspect this will be a bit tougher than it seems but does anyone know of a way to specify this without having to get into other procedures..
Tried searching the forum on this as it seems a pretty simple thing, but most of the results led to dead links. Any help is appreciated.

If you're certain there are environment variables such as HOMEPATH or USERNAME, you can use



or



(and yes, the unusual number of double quotes is necessary to handle spaces in the path name)

Realistically, though, I think you'd be better off using the API approach illustrated in Retrieving a Special Folder's location at "The Access Web"

Thanks Doug!

I'm facing a very similar situation. I have a number of saved imports in my database that are necessary to refresh the data I am using. I would like to provide the database to several colleagues, but not have to have them redefine the import for their machine. I attempted to use the paths specified in the last reply to this post, but on my system whatever I type in the Managed Data Tasks path box is prefixed by HOMEPATH. If I just leave the HOMEPATH portion of the path out, it tells me I have an invalid path. It seems I should be able to specify a variable...what am I doing wrong.

This is my path: C:\Users\matthew.d.angove\Documents\CRS v2.0\Data\tblDEPL_CODES.xlsx

This is what I would like: %USERPROFILE%\CRS v2.0\Data\tblDEPL_CODES.xlsx

Hope someone can help!

Environ("UserProfile") & "\CRS v2.0\Data\tblDEPL_CODES.xlsx"

I have tried from two different machines running different versions of Windows with the same effect. I get a message as follows:

The MIcrosoft Office Access Database engine could not find the object 'C:\Users\Mathew D. Angove\Documents\Environ("UserProfile") & "\CRS v2.0\Data\tblDEPL_CODES.xlsx". Make sure the object exists and that you spell its name and the path name correctly.

Happen to have any other ideas? Thanks!

What's the exact code you're using?

Environ("UserProfile") & "\Documents\CRIS\Data\tblSOLDIER_RAW.xlsx" is the exact code that is currently in the Manage Data Tasks dialog. The path does vary somewhat from what I presented to you previously, but I copied from the wrong Saved Import; this IS the correct path. Note that while the ampersand in your code is showing (I copied this directly from the Saved Import), in the Manage Data Tasks dialog it shows as a underscore "_").

Sorry, what do you mean by "the Manage Data Tasks dialog"? And what do you mean by "the Saved Import"?

The Manage Data Tasks dialog is the window that opens when the user clicks Saved Imports in Access. It shows the various Saved Imports and allows the user to make some edits to the descriptions and paths.

Access allows a user to save the steps of an import operation so that the identical import can be executed again without having to redefine the steps. The various saved imports are shown in the Manage Data Tasks dialog describe above.

One of the Saved Imports in my Manage Data Tasks dialog contains the path issue we have been discussing, Doug. Nothing has changed...just trying to be precise in describing the circumstances by using the text displayed when the window title bar.

I don't believe it's possible to use expressions in the Saved Imports. I believe you need to use the TransferSpreadsheet method in VBA code (or perhaps the TransferSpreadsheet action in a Macro)

That's what I was afraid of. Can always count on Microsoft to make it difficult, lol. On another part of this project I learned they modified the QueryTables function to be subordinate to TableList, without mention of this change in the Help files. I spent several hours looking for a problem, combing through the help, etc. Alright, I'll abandon this avenue and go for a more complicated procedure. Thanks!