?
Solved

Correct syntax for user's desktop

Posted on 2011-04-20
13
Medium Priority
?
399 Views
Last Modified: 2013-11-05
I have a saved import that imports an Excel file.  But in the setup of the saved import I have this...

C:\Users\Steve\Desktop\EstimateWon.xls

Is there a way to make that generic so it will work for all users so I don't have to hard code their name for every user?

--Steve
0
Comment
Question by:SteveL13
  • 6
  • 5
  • 2
13 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35432128
test this

C:\Documents and Settings\%username%\Desktop\EstimateWon.xls
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35432136
or this

dim xlPath
xlPath=Environ("userprofile") & "\Desktop\EstimateWon.xls"
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35432384
Or you could use a Special Folders API call.  See: http://www.mvps.org/access/api/api0054.htm

This API has lots of predefined "special folders"
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:SteveL13
ID: 35432585
I tried the first one but changes it for Windows 7..
 
C:\Users\%username%\Desktop\EstimateWon.xls   but it doesn't work.  I like that one if I could get it to work becuase of the ease of setup...  see screen shot screen shot
0
 

Author Comment

by:SteveL13
ID: 35440109
So, if I want to use...

dim xlPath
xlPath=Environ("userprofile") & "\Desktop\EstimateWon.xls"

and in the onclick event of a command button right now I have...

DoCmd.RunSavedImportExport "ImportEstimateWon"

What do I replace the VBA code with?
0
 

Author Comment

by:SteveL13
ID: 35450226
Actually I need VBA code for both XP and Windows 7 users if anyone can come up with the VBA code.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35450852
The API call should work, unless your Windows 7 box is a 64 bit machine.  In that case, you may have to do one more thing.  Let me know if that is the case.

0
 

Author Comment

by:SteveL13
ID: 35450899
How do I use that to import the Excel file, EstimateWon.xls  ??
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35451073
You would need to copy the code (everything with the light blue background) and paste it into a code module in your application.  Then, to get the path you have indicated above you would do something like:

strDesktopPath = fGetSpecialFolderLocation(CSIDL_DESKTOPDIRECTORY)

This will return the path to your file, but you will have to append "\EstimateWon.xls", so:

strSaveTo = fGetSpecialFolderLocation(CSIDL_DESKTOPDIRECTORY) & "\EstimateWon.xls"


0
 

Author Comment

by:SteveL13
ID: 35453123
I'm sorry... I don't understand... when you say I need to copy the code (everything with the light blue background) and paste it into a code module in your application. ... What do you mean?  Do I create a new module and just paste this into it and save it?...

strDesktopPath = fGetSpecialFolderLocation(CSIDL_DESKTOPDIRECTORY)

Or where do I put it?

Or do I put it in the onclick event of the command button that imports the saved imports as I have it today?

--Steve
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 35454464
The code that is on the Access Web site (the hyperlink in my earlier message) should be copied.  Then create a new code module in Access and paste the code you copied in it.

Then you can call the fGetSpecialFolderLocation() function from anywhere in your application.  If your original intent was to put this in the Click event of a button, then that is where I would do it now.
0
 

Author Comment

by:SteveL13
ID: 35456280
I copied an pasted the code into a new code module.  Then I copied  

strDesktopPath = fGetSpecialFolderLocation(CSIDL_DESKTOPDIRECTORY)

into the onclick event of the command button and nothing happens.

???
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 35456372

Nothing will happen, if you don't have other code in that click event.

Your question was:

Is there a way to make that generic so it will work for all users so I don't have to hard code their name for every user?

I have answered that.  I don't believe you will be able to use this in the Saved Import.  You will have to write some code or record a macro to do that, using the TransferSpreadsheet method.  If you need help with that, I recommend that you post another question, specifically about that.  Or you could search the EE solutions, I'm sure there are probably 100 or more posts that provide code for selecting and importing an Excel file into an Access database.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question