Can only run macro by opening file a certain way

Hi MH,

I find your question very confusing, and perhaps others
do too. You say you have a macro that opens another file,
but then you go on to talk about several methods of opening
it manually (last opened list, Start menu). Also, what do
you mean by "browse to the main spreadsheet from within
Excel?" The main spreadsheet of what? The workbook you want
to open (in which case, how does one browse to a spreadsheet
of a closed workbook)? The workbook running the code?

A little clarification might be helpful.

Damon

Looks like a job for:

Application.defaultfilepath=thisworkbook.path

If you put that line in the Workbook_open() event for the main workbook, it won't matter how you open it.

The problem is that when you open a workbook from a shortcut, Excel does not set its default file path to the path of the file you open. So, when your macro calls another file, Excel looks in its default path, not the folder in which your workbooks live. When you browse to the main workbook via the Excel dialog, choosing the file sets the default file path automatically.

(Your post may have appeared confusing because Excel does not use "spreadsheets"; it uses workbooks (what you call a "spreadsheet") and worksheets (the spreadsheets within what you call a "spreadsheet")).

Fortuntately, I have encountered the problem you are facing, so I sort of know what you're asking without actually deciphering the words you used.

(I've never thought of shouting - has that worked before? Maybe it's an undocumented feature like the hidden Flight Simulator in XL 97.)

Re: Can only run macro by opening file from within Excel

OK. My appologies for any confusion. The main workbook is a form we use extensively throughout the group. Sheet 1 is the main form and has a series of buttons to run variuos macros, sheet 2 holds the form data in a single row which is then copied by a macro to a 2nd workbook called CRDATA.xls which keeps a history, and the last page of the workbook holds the workbook parameters. We do it this way so we can use the same workbook in all 20 of our companies making the minimal changes. I'd like to do it differently, eg in Access, but there you are. If I open the workbook from within Excel using File | Open, etc then all is sweet and rosy. If I open it from the Last Used list in Excel, or from Explorer or from Start | Documents, then I get an error 1004 if I try to run any of the macros that access the CRDATA file. I don't want to have to hard-code the path into the macro, I'd like to allow the users to put the files where they like. I've tried with and without .xls, i've tried ChDir in the code, I've tried recording the macro, and still get the same result. Is there a way I can make this easy so that the macro looks in the same folder it's stored in? If it does that by default, why can't it find CRDATA? Probably very simple, but I'm missing it.