Saving a File to the Current Directory Using a Macro

[jim evans]

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

 

[Ardus Petus]

Use Thisworkbook.Path to determine macro workbook's directory

HTH

 

[jim evans]

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

 

[jim evans]

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