location for XLA's that I can easily get to

[Maury Markowitz]

I have two small XLA's that I would like to load whenever a user opens
Excel. These are periodically updated, and will have to be copied down
every so often. To ease this task, and initial setup, I want to have
an application that will copy these off a network drive onto the local
machine. I have already made something similar to update an Access
app.

The only thing that's stumping me is where to put these. I think I
want to put them in the XLSTART folder - right? Will XLA's in the
XLSTART automatically load when Excel is opened?

If this is the right place, how do I find _this users_ XLSTART? I used
this for the Access app:

pth = Nz(Environ("USERPROFILE"), "C:\Documents and Settings\All
Users")
desktopPath = pth & "\Desktop\"

But that's because we wanted it on everyone's desktop. What's the
corresponding entry for "this users XLSTART"?

Thanks!

Maury

 

[Maury Markowitz]

Actually, thinking about it now it seems that if I could put this into
All Users it would be even better. But when I looked inside:

C:\Documents and Settings\All Users\Application Data\Microsoft\OFFICE

I did not see an EXCEL folder. If I make a

C:\Documents and Settings\ All Users\Application Data\Microsoft\Excel
\XLSTART

will Excel look there?

Maury

 

[Don Guillett]

You can actually put an add in in any folder desired as long as excel knows
where it is. Have you noticed the browse option?

 

[Jon Peltier]

Not XLSTART. Everything in XLSTART is opened, and the user might not like
that. The default directory for Excel add-ins is

C:\Documents and Settings\[username]\Application Data\Microsoft\Add-Ins

or

C:\Program Files\Microsoft Office\OFFICE11\Library

But Excel doesn't care where an add-in is installed from, so you could
create a directory within

C:\Documents and Settings\All Users\Application Data\

for your add-ins.

- Jon

 

[Maury Markowitz]

Not XLSTART. Everything in XLSTART is opened, and the user might not like
that. The default directory for Excel add-ins is

The goal is absolutely to open these files, every time. They contain
code that is needed for everything we do.

I was able to write a little code to move these over using FileCopy.
Unfortunately, FileCopy won't copy the file if the _source_ is busy -
which it always is. Is there an analog to FileCopy that doesn't care
about the status of the source?

Maury

 

[Maury Markowitz]

You can actually put an add in in any folder desired as long as excel knows
where it is. Have you noticed the browse option?

Which would be great, except my 15+ users would have to know how to
use this. They don't.

Maury

 

[Jon Peltier]

The source is busy because you're copying the copy that's open in Excel.
Have your code to a SaveCopyAs to save the workbook/add-in to another
folder.

FileCopy will also choke if the user has open the file it is trying to
replace.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______

Not XLSTART. Everything in XLSTART is opened, and the user might not like
that. The default directory for Excel add-ins is

The goal is absolutely to open these files, every time. They contain
code that is needed for everything we do.

I was able to write a little code to move these over using FileCopy.
Unfortunately, FileCopy won't copy the file if the _source_ is busy -
which it always is. Is there an analog to FileCopy that doesn't care
about the status of the source?

Maury

 

[Maury Markowitz]

Ok, I have this working, to some useful degree anyway.

Indeed, XLA's placed in the user's XLSTART work exactly as you would
want them too.

The trick to moving them there is to ignore the FileCopy command and
use FileSystem.CopyFile, which doesn't care if the source is busy.

The only remaining problem is that if any copy of excel is open, the
destination is busy and the copy will fail. That's OK, I wrote the
updater code in Access to avoid this.

I'll happily provide the code if anyone is interested.

Maury