Rather basic but I realise I don't know how Personal.XLS works.
1. How do you stop/start it opening when the main file opens both manually
and programatically?
2. What workbook index is it given 1 or 2? I am getting some confusion
when I run with multiple books open.
3. Where is the best place to put code so that all users on a network
address the same code. Does the source have to be open?
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?

Sorry - I realise this is beginners stuff

Mervyn

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.)

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.)

Thanks Hank ! all that was very usefull and informative - only one
question - How do you call a macro that's in an XLA ? I couldn't figure out
how to do that!
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.)

On Mon, 26 Jan 2004 14:58:42 -0000, "Mervyn Thomas"
wrote:

Thanks Hank ! all that was very usefull and informative - only one
question - How do you call a macro that's in an XLA ? I couldn't figure out
how to do that!
Mervyn

Sorry for the delay; I still have a few residual computer problems and
wasn't on line yesterday.

There are a number of ways of doing this. If you manually type the
macro name into the appropriate box, you can run the macro straight
from the Run Macro dialog. You won't be able to see the name in the
list, but if you enter (for example)

TestAddIn.xla!MyProcedure

into the "Macro Name" text box and click the [Run] button, you'll be
able to run it. (In most cases it won't be necessary to qualify the
procedure name with the name of the add-in and the exclamation mark.
However if there are other macros with the same name around the place,
that would ensure that you run the correct one. For example, if you
have a Sub in the current workbook which is ALSO called MyProcedure,
then if you just type the procedure name into the run macro dialog
that'll be the one to run. Add the reference to the .xla as I've
shown, though, and the procedure in THAT file will run instead.)

You can also assign the macro to (say) a command button from the Forms
toolbar in the same way through the Assign Macro dialog. The only
thing to watch out for here is that even if you qualify the macro with
the .xla name, it won't stick; if the button finds a local procedure
with the same name as the one in your add-in, it will always run the
local one in preference. (If you look at the "Assign Macro" dialog,
you'll see why; it reverts to the unqualified macro name every time.)

To run the procedure from another VBA project (say, from the Click
event of a command button from the Control Toolbox toolbar, or even
from a standard procedure in a workbook), probably the simplest way is
to use Application.Run. For example:

Application.Run ("TestAddIn.xla!MyProcedure")

Hope this helps.

---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *