Code for Correct location of Add-ins for all versions of Excel

[Charles Jordan]

Hi - we are writing an application for users who may have any version
of Excel : 97,2000,2002, 2003 etc, and we are developing on several
machines, all running XP, but with XL 2002 on one machine, Xl2003 on
another.

Our install programme has the following code:
DestPname$ = Application.LibraryPath & "\" etc etc

and the result is to install the .xla file as follows:-

In Excel 2000: C:\Program Files\Microsoft Office\Office\Library
In Excel 2003: C:\Program Files\Microsoft Office\Office11\Library

But when developing the .xls/xla further on one of these machines and
saving it directly, as opposed to using the above Install routine,
the .xla then comes to rest in:

C:\Documents and Settings\Administrator\Application
Data\Microsoft\Addins

Please what is the correct code, if possible for all versions of
Excel, and the correct location ie final destination of the .xla ,
please

TIA

Charles

 

[Bob Phillips]

Charles,

I have to ask why not just save the file back to where it came from, but if
you continue this approach, look at

Application.UserLibraryPath

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

Just for information, not available in Excel 97. Must have been introduced
in xl2000

 

[Bob Phillips]

Thanks, that makes sense as it was then (?) that the user profile area
started to be used rather than the Office Excel area. Do we know the 97
path?


Bob

 

[Tom Ogilvy]

I suspect it is what he has:

? Application.LibraryPath
C:\PROGRAM FILES\MICROSOFT OFFICE\OFFICE\LIBRARY

 

[Bob Phillips]

To the OP,

This all means that you need to test the version and use a different path.
It can be done with conditional compilation like so

#If VBA6 Then
myPath = Application.UserLibraryPath
#Else
myPath = Application.LibraryPath
#End If


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Charles Jordan]

Bob and Tom . Pretty good service for a Sunday afternoon! I am most
grateful. Looks like we had it right first time.
Tom, I am under the impression you are in the US - but are you actually
in Britain ? We are in Scotland...
Best regards - Charles

 

[Tom Ogilvy]

I am in Virginia, USA, but I believe my ancestors on my Father's side
(Great-GrandFather) came from Scotland. Bob's in the UK I believe.

Regards,
Tom Ogilvy

 

[Bob Phillips]

I am in Virginia, USA, but I believe my ancestors on my Father's side
(Great-GrandFather) came from Scotland.

Ogilvy is certainly a good Scottish name. A quote from
http://www.electricscotland.com/webclans/ntor/ogilvy2.html makes for good
reading

.... The Ogilvies are one of the most distinguished families in Scotland and
take their name from Gillibride, second son of Ghillechriost, Earl of Angus.
He bestowed the lands of Ogilvie and Easter Powrie on his younger son
Gilbert in 1177 having been granted a Barony of the lands by William the
Lion around 1163. Patrick de Ogilvy appears on the Ragman Rolls swearing
fealty to Edward I in 1296 but his two sons both supported King Robert the
Bruce. Patrick obtained the lands of Kettins in Angus and his descendant Sir
Walter, having wed the heiress of the Ramsays of Auchterhouse, obtained her
barony....

I always thought you (Tom) were further West than that, judging by your
posting times.

Bob's in the UK I believe.

Spot on, about as far from Scotland as you can get in the UK, Dorset, so I
have probably not been there much more than Tom (unfortunately).

 

[Myrna Larson]

Hi, Bob:

Maybe you can satisfy my curiosity: why is it 'Robert THE Bruce' instead of
just 'Robert Bruce', etc.?

 

[Gord Dibben]

Myrna

Maybe a later transmogrification of Robert de Brus?

From Normandy in France, the name probably comes from "Brix" near Cherbourg.
Like many of the early Scots nobility, the first Robert de Brus came over with
William the Conqueror in 1066. A later Robert de Brus accompanied King David I
when he returned to Scotland in 1124 from his sojourn in England. The family
became established in Annandale in what is now Dumfries and Galloway. The
family also owned extensive lands in England. As a result of marriage to the
daughter of King David, Robert de Brus became Earl of Huntingdon.

At one point, when King David invaded England, Robert de Brus gave up his
holdings in Annandale to his son and joined the English forces. He later
captured his son at the Battle of the Standard in 1138. Generations after
that, Robert the Bruce, Earl of Annandale, became ruler of an independent
Scotland and achieved the great victory at Bannockburn over Edward II in 1314


Gord

 

[Myrna Larson]

Thanks for the history lesson, Gord. That helps!

Myrna

Maybe a later transmogrification of Robert de Brus?

From Normandy in France, the name probably comes from "Brix" near Cherbourg.
Like many of the early Scots nobility, the first Robert de Brus came over with
William the Conqueror in 1066. A later Robert de Brus accompanied King David I
when he returned to Scotland in 1124 from his sojourn in England. The family
became established in Annandale in what is now Dumfries and Galloway. The
family also owned extensive lands in England. As a result of marriage to the
daughter of King David, Robert de Brus became Earl of Huntingdon.

At one point, when King David invaded England, Robert de Brus gave up his
holdings in Annandale to his son and joined the English forces. He later
captured his son at the Battle of the Standard in 1138. Generations after
that, Robert the Bruce, Earl of Annandale, became ruler of an independent
Scotland and achieved the great victory at Bannockburn over Edward II in 1314


Gord

 

[Charles Jordan]

Hi, Bob:

Maybe you can satisfy my curiosity: why is it 'Robert THE Bruce' instead of
just 'Robert Bruce', etc.?

Tom - we are in Aberdeenshire, just over the mountain from the county
of Angus - perhaps an hour away. Why don't you come and see us one day
and we can extend some good Scottish hospitality, especially since you
have helped us so many times. And Ogilvy is a very good name around
these parts - our royal family even married into it not long ago.
Regards

Charles Jordan