Can we export to a text file to the desktop but using relative paths? [A03]

D

Douglas J. Steele

The best way is to determine the path in your code, and export to that path.

For example, if you've got an environment variable DesktopDir that points to
the user's desktop, you can do something like:

Dim strFile As String

strFile = Environ("DesktopDir") & "\Data.txt"
DoCmd.TransferText acExportDelim, , "MyTable", strFile, True
 
D

Douglas J. Steele

I probably should have mentioned that the problem with trying to use
relative paths is you don't know to what it's going to be relative!

You shouldn't assume anything about the current directory when running an
Access application. There are many different ways of invoking an Access
application (such as using a desktop shortcut, double-clicking on the file,
opening Access then navigating to the file or opening Access and selecting
the file from the MRU list, etc), and each can lead to a different current
directory.
 
S

StargateFan

Exporting data is pretty standard, I imagine but can A03 be set to
save to a desktop via a relative path so that the file works no matter
what computer we're working on?

i.e., in a scripting language I use, you can use what is called

@DesktopDir

and it will work with relative paths to your current computere's
desktop no matter what the actual path is.

Thanks! :eek:D
 
D

Douglas J. Steele

StargateFan said:
I just needed to know how Access deals with relative paths and what
its _own_ variable for the desktop would be. I'm hoping that Access
has something similar to use in a script to save an export of the
data.

So pls, just need that point clarified - does Access handle saving to
a existing relative path variable of its own for the desktop and what
is that variable?

Access does not have its own variables to represent the paths to "special
folders".

You can use API calls to get them, though: see
http://www.mvps.org/access/api/api0054.htm at "The Access Web" for a
complete sample.
 
S

StargateFan

The best way is to determine the path in your code, and export to that path.

For example, if you've got an environment variable DesktopDir that points to
the user's desktop, you can do something like:

Dim strFile As String

strFile = Environ("DesktopDir") & "\Data.txt"
DoCmd.TransferText acExportDelim, , "MyTable", strFile, True

I don't know if I've understood, but I get the impression that the
answer to my question is no (?). I used the term "DesktopDir" merely
as an example of how a scripting language that I have deals with a
relative path to any desktop you're on. When you use this scripting
language, it works without you changing anything even when you switch
host computers. This language has many such relative path variables
to major folders: i.e., @StartupDir, @SystemDir, @UserProfileDir,
etc. So you'd do something like this if this scripting langue were a
database program:

(export command...) = @DesktopDir & "\Export of database file.txt",

and I'd end up with my text file export exactly where I needed it.

I just needed to know how Access deals with relative paths and what
its _own_ variable for the desktop would be. I'm hoping that Access
has something similar to use in a script to save an export of the
data.

So pls, just need that point clarified - does Access handle saving to
a existing relative path variable of its own for the desktop and what
is that variable?

Thanks so much and sorry for any confusion! :eek:D
 
L

Len B

You could look at the possibility of using environment variables as Douglas
suggested in his first reply.

To see what environment variables are available on your system open a
command prompt and type
set <enter>

Here are some from my system -
COMPUTERNAME=TREES
HOMEDRIVE=C:
HOMEPATH=\Documents and Settings\Len
SystemDrive=C:
USERPROFILE=C:\Documents and Settings\Len
WINDIR=C:\WINDOWS

You could look at using a combination of Homedrive and Homepath or
Userprofile or some other existing variable. Also, you can create
and use your own custom variables like the ones you are used to from
your scripting language. eg you could create Dsktop=C:\Doc....\Desktop
and use it in Douglas' code.

strFile = Environ("Dsktop") & "\Data.txt"
DoCmd.TransferText acExportDelim, , "MyTable", strFile, True


--
Len
______________________________________________________
remove nothing for valid email address.
| On Wed, 19 Aug 2009 07:42:13 -0400, "Douglas J. Steele"
|
| >| >>
| >> I just needed to know how Access deals with relative paths and what
| >> its _own_ variable for the desktop would be. I'm hoping that Access
| >> has something similar to use in a script to save an export of the
| >> data.
| >>
| >> So pls, just need that point clarified - does Access handle saving to
| >> a existing relative path variable of its own for the desktop and what
| >> is that variable?
| >
| >Access does not have its own variables to represent the paths to
"special
| >folders".
| >
| >You can use API calls to get them, though: see
| >http://www.mvps.org/access/api/api0054.htm at "The Access Web" for a
| >complete sample.
|
| <sigh> Darn. Okay. Well, looks like I'll keep the status quo then,
| for now.
|
| Thanks. :eek:D
|
 
D

Douglas J. Steele

StargateFan said:
<sigh> Darn. Okay. Well, looks like I'll keep the status quo then,
for now.

It's not that difficult to use the API code.

Copy everything in the shaded area (between Code Start and Code End) and
paste it into a code module (not a class module, nor a module associated
with a form or report). To export to the current user's desktop, use:


Dim strFile As String

strFile = fGetSpecialFolderLocation(CSIDL_DESKTOPDIRECTORY ) & "\Data.txt"
DoCmd.TransferText acExportDelim, , "MyTable", strFile, True


If you want it to be on the desktop for all users of the machine, use
CSIDL_COMMON_DESKTOPDIRECTORY instead of CSIDL_DESKTOPDIRECTORY
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top