Thanks - very informative answer
Mervyn

"Hank Scorpio" wrote in message
...
On Sat, 24 Jan 2004 11:49:39 -0000, "Mervyn Thomas"
wrote:

Rather basic but I realise I don't know how Personal.XLS works.

Personal.xls is a standard (well, almost standard) Excel workbook. The
only thing that's a little unusual about it is that:
- it's hidden by default (though like any workbook, it doesn't HAVE
to be); and
- it's one of the standard places that you can elect to have code
stored in when you record macros using the Macro Recorder.

The first time you use the macro recorder to record a macro into
Personal.xls, Excel will create the file if it doesn't already exist.

The intention of Personal.xls is to store VBA code which doesn't
necessarily relate to any particular workbook, but which rather has a
generic application... OR code that you want to be able to run without
manually opening the workbook containing that code first.

1. How do you stop/start it opening when the main file opens both
manually
and programatically?

Excel stores the Personal.xls workbook in a folder called XLStart, the
location of which will vary depending on your version of Excel or
Windows. Any workbooks which are in that folder are loaded
automatically when Excel starts. I'm not sure what you mean by "the
main file" unless you're talking about Excel itself. If you don't want
Personal.xls to load when Excel starts, the easiest way is to remove
it from XLStart first.

2. What workbook index is it given 1 or 2? I am getting some confusion
when I run with multiple books open.

I think you would be singularly unwise to assume that it will always
have the same index. It depends on what other files are in the XLStart
(or alternative startup) folder, and what order they load in. In any
case, why would you need to? You can always refer to the workbook by
name rather than index.

3. Where is the best place to put code so that all users on a network
address the same code.

I wouldn't use Personal.xls for this. It's intended to be just that;
PERSONAL to each user. I would suggest that common code be put in an
add-in (an Excel workbook which is saved (through File - Save As)
with an .xla extension). Typically, add-ins are also hidden workbooks.
The add-in can be placed on a network drive, and users can have it
load automatically through their Tools - Add-ins menu.

Does the source have to be open?

Buh????

If you're asking what I think you're asking, then yes, the file
containing the relevant VBA needs to be loaded in one form or another.
It does not, however, have to be visible to the user.

4. And related - what is the syntax to do a CHDIR when you don't know
the
root drive letter? (I usually use CHDIR "C:\whatever"
Will CHDIR "../A Folder below existing one" work?

Tested in my Immediate window:
? curdir
C:\Documents and Settings\Hank Scorpio\My Documents
chdir "test"
? curdir
C:\Documents and Settings\Hank Scorpio\My Documents\test

So yes, ChDir followed by the lower folder name will work.

Sorry - I realise this is beginners stuff

No problem.

---------------------------------------------------------
Hank Scorpio
- Yes, yes, I know I've been missing for a while. Long story. And don't
ask what the size of my inbox is at the moment either... 8^
scorpionet who hates spam is at iprimus.com.au (You know what to do.)