Generic file path using vba

[dwake]

I have a vba code that opens a file placed on my desktop, but this vba code
must be used in several pc's, and in every pc the desktop path is different
due to different user names.

I would like to now if it's possible to write a "generic" path for the vba
to find the files with no matter which of the pc desktops the macro is being
ran at. The files will be placed always in the desktop folder.

here is the string I am using to open the file.
Workbooks.Open "C:\Documents and Settings\[Username]\Desktop\Data_Export.xls"

Any advice would be appreciated

 

[Mike H]

No need for another thread, you have an answer in the other one in general
questions

 

[Chip Pearson]

At http://www.cpearson.com/Excel/SpecialFolders.aspx I have a module
you can import into your project that allows you to get the actual
path locations of about 50 predefined directories associated with a
user (e.g,. Desktop, My Documents, Favorites, My Music, etc). Download
the file from the page above, unzip it into some folder (it doesn't
matter what folder), and then in VBA, go to the File menu, choose
Import File, and select the bas file you just unzipped. That module
has constant names for all the files (e.g., CSIDL_DESKTOP = &H0) and a
function named GetSpecialFolder that will take a CSIDL value and
return the folder name. E.g.,

Dim S As String
S = GetSpecialFolder(CSIDL_DESKTOPDIRECTORY)
Debug.Print S

which on my machine is "C:\Users\Pearson\Desktop".

No modifications are required to the imported module. Just call the
GetSpecialFolder function passing it the appropriate CSIDL value, all
of which are constants in the imported file.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)