RE: Macro question

From: Jim Thomlinson (JimThomlinson_at_discussions.microsoft.com)
Date: 03/23/05 

Date: Wed, 23 Mar 2005 09:57:02 -0800

You could use either thisworkbook.path or application.path... Probably
thisworkbook, which is the directory where they have saved your workbook. If
it is not saved then you could run into a problem... In that case
application.path which returns where the excel program is saved... End users
may not like this...

HTH

"SJC" wrote:

> Thank you for your advice. Do you have any other suggestions on a universal
> place to put this file? Unfortuneately, there will be about 200 users from
> dozens of organizations, so I will not be able to confirm the administration
> of the c: drive for all of them. Is there a way to get to the "My Documents"
> folder or "Desktop" that is universal to all operating systems?
>
> "Jim Thomlinson" wrote:
>
> > You can use the dir function to determine if a file (or directory) exists.
> > One thing to be aware of is that it is possible that some users will not be
> > able to create files in their root of C. Often administrators lock the root
> > down to keep users from accidentally mucking up their systems. That is
> > something to confirm before you begin.
> >
> > HTH
> >
> > "SJC" wrote:
> >
> > > I have created a workbook that contains several spreadsheets--one for each
> > > month of the year. I have about 200 novice users that will be recording some
> > > monthly data and sending the report to me. Within each spread***, I have
> > > included a macro which will export what data I need and save it into a folder
> > > so that they can easily find the file and email it to me. The users have
> > > operating systems anywhere from Windows 95 to XP, so it was difficult to find
> > > a folder to have these files saved to within the macro that fit all of the
> > > operating systems. So, in the macro, I opened a new folder and saved the
> > > file under the new folder to the C:\ drive, which would be universal to all
> > > operating systems. So the way I have it set up is to have the May file be
> > > the macro that opens up the new folder and stores the first file. Then
> > > future month macros saves the new files to that same folder. The problem is
> > > that if I have a user that doesn't use the program until August, that macro
> > > obviously won't create the folder and cannot find the right path. Is there a
> > > way to program the macro to create the new folder only if it hasn't been
> > > previously created? Any other options to solve this problem? I apologize if
> > > I am sounding confusing--I am fairly new to this macro business. Please
> > > offer any recommendations. Thanks.