Code to import a filename located on the desktop

[betwalk]

Hello-

I have a user who does imports from Excel. Currently I have a button
that runs the following code:

DoCmd.TransferSpreadsheet acImport, 8, "tblImportedFromExcel",
"C:\FromExcel\DataFromExcel.xls", True, ""

It works quite well, but I'd prefer that my user not have to create the
folder "FromExcel" in the root of C. It would be much easier for him
to simply place the file on his desktop for this task.

I don't know how to write the path for that in the above statement.

Thanks in advance for any help on this!

 

[Brendan Reynolds]

There's code to retrieve the location of special folders, including the
user's desktop, at the following URL ...

http://www.mvps.org/access/api/api0054.htm

The code is long, but once you've copied and pasted all that code into a
standard module (not a form, report, or class module) all you need to
retrieve the path to the user's desktop is the following single line of code
....

fGetSpecialFolderLocation(CSIDL_DESKTOPDIRECTORY)

The code returns the path minus the trailing backslash character, so the
code to construct your full path and file name would look like so ...

fGetSpecialFolderLocation(CSIDL_DESKTOPDIRECTORY) & "/DataFromExcel.xls"

 

[betwalk]

Hi Brendan-

Thanks for this reply. It's a lot to digest! I will take my time to
review this and see what I am capable enough to implement. I have, in
the meantime, come up with a solution to my situation that I believe
will work. It works when I test it on my PC and all I need to do is be
sure that I name the correct user when I code it on his PC. It looks
like this:

DoCmd.TransferSpreadsheet acImport, 8, "tblImportedFromExcel",
"C:\documents and settings\THIS IS WHERE HIS USERNAME WILL
GO\desktop\DataFromExcel.xls", True, ""

As long as I identify how his computer references his username, I'm
hoping this will be okay. It tests fine on my PC.

Do you see any gremlins in my approach?

 

[Guest]

Hi betwalk,

That will work but Brendan's would be the better approach unless you only
have one user using the db, plus when that person leaves you will have to
alter the code appropriately.

HTH

 

[JapieDar]

Hi betwalk,

That will work but Brendan's would be the better approach unless you only
have one user using the db, plus when that person leaves you will have to
alter the code appropriately.

HTH

DoCmd.TransferSpreadsheet acImport, 8, "tblImportedFromExcel",
"C:\documents and settings\%HOME%\desktop\DataFromExcel.xls", True, ""

 

[Brendan Reynolds]

I know the code looks long and complex, but all you need to do is copy and
paste it. You can then use it like so ...

DoCmd.TransferSpreadsheet acImport, 8, "tblImportedFromExcel",
fGetSpecialFolderLocation(CSIDL_DESKTOPDIRECTORY) & "\DataFromExcel.xls",
True, ""

(The above is a single line of code, though it may be broken into more than
one line in the newsgroup post.)

 

[Douglas J Steele]

DoCmd.TransferSpreadsheet acImport, 8, "tblImportedFromExcel",
"C:\documents and settings\%HOME%\desktop\DataFromExcel.xls", True, ""

First of all, you can't use environment variables like that in Access: it
isn't going to translate the %HOME% for you. You need to use the Environ
function to return the value of the environment variable:

DoCmd.TransferSpreadsheet acImport, 8, "tblImportedFromExcel",
"C:\documents and settings\" & Environ("HOME") &
"\desktop\DataFromExcel.xls", True, ""

However, that won't work for everyone. For example, I don't have an
environment variable named Home on my machine. In my case, it's USERPROFILE:

DoCmd.TransferSpreadsheet acImport, 8, "tblImportedFromExcel",
"C:\documents and settings\" & Environ("USERPROFILE") &
"\desktop\DataFromExcel.xls", True, ""

That's why using APIs, such as Brendan recommended, is preferable. You could
also use the WSHSpecialFolders collection. I showed how to do this in my
May, 2004 "Access Answers" column in Pinnacle Publication's "Smart Access".
You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[betwalk]

Hi Brendan-

I will try your suggestion. I'm sure it will give me a lot more
flexibility than hard coding the location as I have been doing so far.
Thanks for your encouragement!

Betsy