script to auto-install Excel addins

[GS]

I'm looking for a script I can use with my installer that will enum the
*Options* key of an installed Excel version for all *OPEN* entries to
see if my addin exists. If exists then update its value. If not found
then add it.

This will require looping each *Office* key for all versions of Excel
my addin supports. Currently this is 11,12,14,15, but I only need a
script for one version's registry key as it simply repeats for each
additional version.

My goal is to automatically install my addin in each Excel version
installed on the target machine.

Example Registry path:

HKCU\Software\Microsoft\Office\11.0\Excel\Options

Any help/pointers are appreciated...
--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

[GS]

For clarity.., I've found lots of scripts that do this via Excel
automation using CreateObject(). This only installs for the default
version. I want to edit Registry key for all installed versions, thus
not having to open Excel.
--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

[GS]

I managed to put together a VB[A] version using my favorite Windows
service. I can easily convert this to VBS but I'd appreciate any
feedback regarding a better or more reliable method.

Sub WMI_InstallAddin()
Dim sAddinPath$, sKey$, sOpen$, n&, i&
Dim vSZ, oWMI, vVer, vValNames, vValTypes

Const sVers$ = "11.0,12.0,14.0,15.0"
Const sOfficeKey$ = "Software\Microsoft\Office\"
Const sOptsKey$ = "\Excel\Options"
Const sAddinMngr$ = "Excel\Add-in Manager"
Const HKCU = &H80000001
Const REG_vSZ = 1
sAddinPath = Chr(34) & "C:\MyFolder\APP\app.xla" & Chr(34)

Set oWMI = GetObject("winmgmts:root\default:StdRegProv")
For Each vVer In Split(sVers, ",")
sKey = sOfficeKey & vVer & sOptsKey
oWMI.EnumValues HKCU, sKey, vValNames, vValTypes
If IsArray(vValNames) Then '//confirm version is installed
n = 1: sOpen = "OPEN"
For i = LBound(vValNames) To UBound(vValNames)
oWMI.GetStringValue HKCU, sKey, vValNames(i), vSZ
If Left(vValNames(i), 4) = "OPEN" Then
n = n + 1
If vSZ = sAddinPath Then
n = 0: Exit For
End If
End If 'Left(vValNames(i), 4) = "OPEN"
Next 'i

If n > 0 Then
n = n - 1
If n > 0 Then sOpen = sOpen & n
oWMI.SetStringValue HKCU, sKey, sOpen, sAddinPath
'Make sure to remove it from Add-in Manager
sKey = sOfficeKey & vVer & sAddinMngr
oWMI.SetStringValue HKCU, sKey, sAddinPath
End If 'n > 0
End If 'If IsArray(vValNames)
Next 'vVer
Set oWMI = Nothing
End Sub

[GS]

Post by GS
'Make sure to remove it from Add-in Manager
sKey = sOfficeKey & vVer & sAddinMngr
oWMI.SetStringValue HKCU, sKey, sAddinPath

This should read...

'Make sure to remove it from Add-in Manager
sKey = sOfficeKey & vVer & sAddinMngr
oWMI.DeleteValue HKCU, sKey, Replace(sAddinPath, Chr(34), "")

..where the function *SetStringValue* I forgot to change to
*DeleteValue* after copy/paste. Also, entries in Add-in Manager aren't
wrapped in double quotes and so need to be removed.

[Mayayana]

Well, look who the WMI dragged in. :)

I can't say anything about MS Office Registry
keys specifically, but a couple of things that
might be useful for VBS Registry access:

The "normal" way is WScript.Shell, which is fairly
simple but undependable. The WMI method you're
using is the best I know of. Unfortunately, WMI
StdRegProv is a mess in terms of design. I have
a class I use that might be worth a look. I wrote
it to encapsulate the WMI muck and provide clear,
simple methods like EnumValues, WriteValue, etc.

http://www.jsware.net/jsware/scrfiles.php5#classpk

The other down side with WMI, which you probably
know, is that it's not necessarily running. The service
runs by default, but I would personally have it disabled
if I didn't use it myself. It offers nothing but risk to
most people. Maybe most corporate IT people use it
and have it running; I don't know.


"GS" <***@somewhere.net> wrote in message news:lgjtu6$d1d$***@dont-email.me...
|> 'Make sure to remove it from Add-in Manager
| > sKey = sOfficeKey & vVer & sAddinMngr
| > oWMI.SetStringValue HKCU, sKey, sAddinPath
|
| This should read...
|
| 'Make sure to remove it from Add-in Manager
| sKey = sOfficeKey & vVer & sAddinMngr
| oWMI.DeleteValue HKCU, sKey, Replace(sAddinPath, Chr(34), "")
|
| ..where the function *SetStringValue* I forgot to change to
| *DeleteValue* after copy/paste. Also, entries in Add-in Manager aren't
| wrapped in double quotes and so need to be removed.
|
| --
| Garry
|
| Free usenet access at http://www.eternal-september.org
| Classic VB Users Regroup!
| comp.lang.basic.visual.misc
| microsoft.public.vb.general.discussion
|
|

[GS]

Post by Mayayana
Well, look who the WMI dragged in. :)
I can't say anything about MS Office Registry
keys specifically, but a couple of things that
The "normal" way is WScript.Shell, which is fairly
simple but undependable. The WMI method you're
using is the best I know of. Unfortunately, WMI
StdRegProv is a mess in terms of design. I have
it to encapsulate the WMI muck and provide clear,
simple methods like EnumValues, WriteValue, etc.
http://www.jsware.net/jsware/scrfiles.php5#classpk

I'll definitely check this out...

Post by Mayayana
The other down side with WMI, which you probably
know, is that it's not necessarily running. The service
runs by default, but I would personally have it disabled
if I didn't use it myself. It offers nothing but risk to
most people. Maybe most corporate IT people use it
and have it running; I don't know.

Thanks!
I plan to use this as VBScript in my installer. I switched from using
Wise and so had to revise the script I used there, but it's VB and I
need it to be a stand-alone VBS that I can implement via the
installer's *Run Script* action.

[GS]

Post by Mayayana
Well, look who the WMI dragged in. :)

Well I was really hoping you'd come along and clean my VB code up so
it's pure VBScript.

I looked at your link and downloaded a few items beside the ClassPack.
Since I'm not familiar with VBS, I'm not sure my installer will like
the class. The script gets inserted 'raw' into a textbox, and so is not
a script.vbs!

[Mayayana]

| Well I was really hoping you'd come along and clean my VB code up so
| it's pure VBScript.
|

:) That's actually not very difficult. Remove all
datatype indicators. In your case that's easy. Just
remove all "$". There are a few other pesky oddities,
but not much in a typical script.

There's no Mid statement.

You can't use string-datatype functions: Chr() is fine but not Chr$()

If you want to access an array member you should assign
it first. I've never tested this thoroughly, and I can't recall
offhand the exact problems I've run into, but the way it
seems to work is that in VB you can work with a string
array member directly, but in VBS you sometimes need to
put it into a variable first:

s = A1(i)
' do stuff with s
A1(i) = s

Also, you can't assign non-variants as array members.
Everything is a variant. The one exception is that you
can sometimes call COM objects requiring strong types
as long as you don't need to receive a strong type:

DoSomeOCXCall Clng(x), Clng(y)

I don't use constants, since the whole thing is usually
in a single file, anyway, but you can use constants in VBS.

Also, see my class for minor details. For instance,
EnumValues returns an error code that can be useful.
If it returns 0 the last two parameters will be arrays,
but they may not contain data, having a UBound of -1.


| I looked at your link and downloaded a few items beside the ClassPack.
| Since I'm not familiar with VBS, I'm not sure my installer will like
| the class. The script gets inserted 'raw' into a textbox, and so is not
| a script.vbs!
|

I'm afraid I don't know much about MS Office and
I don't know what your installer is doing. If it
runs via WScript as a .vbs file at install then you
can just paste the class at the end of your script
and create an instance in your code.
If it's some sort of custom interpreter then it's anyone's
guess what it can handle.

It might be easier, in any case, not to use the
class. I wrote it to make StdRegProv intuitive for
any use. If you're only using it for a few, specific
things then it could be easier just to deal with
StdRegProv quirks.

[GS]

Big thanks! Lots of uncertainty coming across; perhaps better to use as
is in a VB6.exe and install/run/delete. This is tested code and so I
wouldn't want to break it messing around with VBScript. I could just
clean up the datatype symbols and run it as InstallAddin.vbs and see
how it behaves! I will play and report back...

[Evertjan.]

Post by Mayayana
There's no Mid statement.

eh, in VBS?

[Evertjan.]

Post by Evertjan.

Post by Mayayana
There's no Mid statement.

eh, in VBS?

Yes, of course, you are right.

There is the Mid function,
not the Mid statement that exist[ed] in William Gates' Basic.

[Mayayana]

| There is the Mid function,
| not the Mid statement that exist[ed] in William Gates' Basic.
|

It's unfortunate. There is a Mid statement in
VB, and it's very efficient. At one point I spent
a lot of time optimizing a complex tokenized
string function. I couldn't find any method
faster for rebuilding a string than to start with
a large empty string and write the new string
to it in pieces using Mid. In VBS, without having
that, the fastest I can find is to start with a
large array, add strings, and then use Join to
get the final string.

[Evertjan.]

Post by Mayayana
| There is the Mid function,
| not the Mid statement that exist[ed] in William Gates' Basic.
|
It's unfortunate. There is a Mid statement in
VB, and it's very efficient. At one point I spent
a lot of time optimizing a complex tokenized
string function. I couldn't find any method
faster for rebuilding a string than to start with
a large empty string and write the new string
to it in pieces using Mid. In VBS, without having
that, the fastest I can find is to start with a
large array, add strings, and then use Join to
get the final string.

Fake it:

function midStatement (theStr,position,length,stringToBeInserted)
midStatement = left(theStr,position) & stringToBeInserted &
mid(theStr,position+length)
end function

str = midStatement(str,2,2,"abc")

[Mayayana]

| >| There is the Mid function,
| >| not the Mid statement that exist[ed] in William Gates' Basic.
| >|
| >
| > It's unfortunate. There is a Mid statement in
| > VB, and it's very efficient. At one point I spent
| > a lot of time optimizing a complex tokenized
| > string function. I couldn't find any method
| > faster for rebuilding a string than to start with
| > a large empty string and write the new string
| > to it in pieces using Mid. In VBS, without having
| > that, the fastest I can find is to start with a
| > large array, add strings, and then use Join to
| > get the final string.
|
| Fake it:
|
| function midStatement (theStr,position,length,stringToBeInserted)
| midStatement = left(theStr,position) & stringToBeInserted &
| mid(theStr,position+length)
| end function
|
| str = midStatement(str,2,2,"abc")
|

That looks clean but it's very inefficient. With
a long string, concatenation becomes slower
and slower. Just adding one character to a very
long string will be slow because it requires allocation
of a new very long string. So the more concatenation
you do, the slower it gets. I've found with VBS that
putting lots of strings into an array and then using
Join is notably more efficient when dealing with long
strings and/or multiple concatenations. According to
Matthew Curland, one of the VB developers, that's
to be expected because Join works by first measuring
all strings that need to be joined and then doing a
single allocation -- similar to the efficiency of Mid.

What I've used the Mid statement for most is not
concatenation but rather string building. The time
I got researching it I was trying to write a tokenizing
routine in VB. I needed to do color syntax highlighting
in a text file, converting it to RTF text. That meant
innumerable inserts of RTF tags. I needed to process
a file up to 100 KB in under 1/4 second to avoid an
appearance of lagging. It turned out that builing the
new string by using Mid on a very large, empty string
was by far the fastest option. It seems to write the
bytes directly to the memory offset in the string, with
no allocation of new memory required. In other words,
I can convert "abcdefg" into "ab---cd---ef---g" by just
writing each section to a new string, with no concatenation.

[GS]

Well I'll be darned! It works same as in VB[A]! Hooray!

All I did was copy/paste what was inside the procedure into TextPad,
remove all datatype symbols (Those were there because of Option
Explicit) using Find/Replace, and save as InstallAddin.vbs!

Next I added the addin to the Add-in Manager key and ran the script.
The addin appeared in Options as the next OPEN# and it was removed from
Add-in Manager.

How this works in Excel is when you check the addin in the Addins
dialog, Excel moves it from Add-in Manager to Options as an OPEN file
(ergo '.Installed=True'). When you clear the check Excel removes it
from Options and puts it back into Add-in Manager as a closed file
(ergo '.Installed=False').

What this script does is get it listed in the Addins dialog as checked.

[Evertjan.]

Post by GS
I'm looking for a script I can use with my installer that will enum the
*Options* key of an installed Excel version for all *OPEN* entries to
see if my addin exists. If exists then update its value. If not found
then add it.
This will require looping each *Office* key for all versions of Excel
my addin supports. Currently this is 11,12,14,15, but I only need a
script for one version's registry key as it simply repeats for each
additional version.
My goal is to automatically install my addin in each Excel version
installed on the target machine.
HKCU\Software\Microsoft\Office\11.0\Excel\Options
Any help/pointers are appreciated...

Perhaps you want VBA scripting, this NG is about VBS.

[GS]

Post by Evertjan.
Perhaps you want VBA scripting, this NG is about VBS.

No, I want a vbs version of the example I made in VB[A]. Is there
something better or is this good to convert to vbs?
--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

[Evertjan.]

Post by GS
No, I want a vbs version of the example I made in VB[A]. Is there
something better or is this good to convert to vbs?

I don't know VBA, for VBS you would have to state the engine used,
perhaps cscript?

Post by GS
---
This email is free from viruses and malware because avast! Antivirus
protection is active. http://www.avast.com

However, this is not email, but usenet.

[GS]

Post by Evertjan.

Post by GS
This email is free from viruses and malware because avast!
Antivirus
protection is active. http://www.avast.com

However, this is not email, but usenet.

Don't know why that's there because it's turned off for nttp!

[Evertjan.]

Post by GS

Post by Evertjan.

Post by GS
This email is free from viruses and malware because avast!
Antivirus
protection is active. http://www.avast.com

However, this is not email, but usenet.

Don't know why that's there because it's turned off for nttp!

;-)

[Dave "Crash" Dummy]

Post by GS

Post by Evertjan.

Post by GS
This email is free from viruses and malware because avast! Antivirus
protection is active. http://www.avast.com

However, this is not email, but usenet.

Don't know why that's there because it's turned off for nttp!

Yeah, but this is nntp, not nttp. Anyway, you probably can't turn off
the advertising.

[GS]

Post by Dave "Crash" Dummy

Post by GS

Post by Evertjan.

Post by GS
This email is free from viruses and malware because avast!
Antivirus
protection is active. http://www.avast.com

However, this is not email, but usenet.

Don't know why that's there because it's turned off for nttp!

Yeah, but this is nntp, not nttp. Anyway, you probably can't turn off
the advertising.

Thanks for the correction, Dave! As you can see.., I was able to turn
it off no problem!!!