Saving a File to the Current Directory Using a Macro

I have created a macro that creates/saves a text file. The macro will
run on various people's computers and need to create/save the file in
directory that the macro file is located in. By default Excel saves
the file in the directory Excel has been told to put files in.

How do I cause the macro to save the file to the directory the
spreadsheet containing the macro is in?

jim

Use Thisworkbook.Path to determine macro workbook's directory

HTH
--
AP


"jim evans" <[email protected]> a écrit dans le message de news:
[email protected]...
>I have created a macro that creates/saves a text file. The macro will
> run on various people's computers and need to create/save the file in
> directory that the macro file is located in. By default Excel saves
> the file in the directory Excel has been told to put files in.
>
> How do I cause the macro to save the file to the directory the
> spreadsheet containing the macro is in?
>
> jim

On Sat, 17 Jun 2006 09:21:44 +0200, "Ardus Petus"
<[email protected]> wrote:

>Use Thisworkbook.Path to determine macro workbook's directory

For some reason I can't get this to work. Instead or returning the
directory where the spreadsheet is located, it returns Excel's default
data file directory.

jim

On Sun, 18 Jun 2006 08:54:37 -0500, jim evans
<[email protected]> wrote:

>On Sat, 17 Jun 2006 09:21:44 +0200, "Ardus Petus"
><[email protected]> wrote:
>
>>Use Thisworkbook.Path to determine macro workbook's directory
>
>For some reason I can't get this to work. Instead or returning the
>directory where the spreadsheet is located, it returns Excel's default
>data file directory.

Well, I think I've solved this problem, but I think it may be a bug in
Excel. If there's a spreadsheet in Excel's default data file
directory with the same name as the one you are running in another
random directory, Thisworkbook.Path sometimes returns the Excel's
default data directory rather than the current directory for the
spreadsheet being run. It seems to be intermittent -- that is, it
works right occasionally but not most of the time.

jim