MS Excel COMAddins Manager VB6 app

[GS]

Hello,
I've been using my VB6 AddMan4XL.exe for some years now to manage
(load/unload/install/remove) VBA addins with the various installed versions I
use for testing. This app lists by ver each addin stored in each version HKCU
Registry key under...

Software\Microsoft\Office\n.0\Excel\Add-in Manager
and
Software\Microsoft\Office\n.0\Excel\Options

..where they are listed by version, and the former key lists installed while
the latter lists loaded. There's some shifting back and forth to load/unload
but for all intents and purposes if an addin isn't listed here then it's not
installed (registered) with Excel.

I'm trying to extend my app to include COMAddins management in the same fashion
but the logistics aren't as simple as they might seem. For instance, depending
on the scope (current user or all users) of a COMAddin when it's being
installed, it will be registered under HKCU or HKLM respectively. The path for
either is under...

Software\Microsoft\Office\Excel\Addins

..where just the COMAddin info is listed without respect to Excel version[s].
Unfortunately, each version COMAddin Manager dialog does not agree with the
registry lists nor with each of the other version lists! Also, each version
COMAddins Manager dialog uses a checked list to determine if the addin is
loaded or not at runtime.

What I need help with is finding where this info gets stored between runtime by
each Excel version so I can make my app work with COMAddins same as it does
with VBA Addins. Can anyone shed any light on this or point me where to find
good info?

TIA

--
Garry

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

[Arne Saknussemm]

:: On Mon, 02 Dec 2019 11:11:24 -0500
:: (comp.lang.basic.visual.misc,microsoft.public.vb.general.discussion)
:: <qs3d3g$nnt$1...@dont-email.me>

:: GS <g...@v.invalid> wrote:

> What I need help with is finding where this info gets stored between
> runtime by each Excel version so I can make my app work with
> COMAddins same as it does with VBA Addins. Can anyone shed any light
> on this or point me where to find good info?

I suppose you already saw this

https://support.microsoft.com/en-us/help/291392/excel-com-add-ins-and-automation-add-ins

at any rate, another possible way to find out informations may be using
the "Process Monitor" tool from SysInternals (now Microsoft)

https://docs.microsoft.com/en-us/sysinternals/downloads/procmon

run it (as admin), stop the capture (File, uncheck "capture..."), then
open the Filter->Filter menu and add one or more filters to only
include the desired process(es) e.g. "process name contains..." or
something like that, confirm the filter settings then in the toolbar
uncheck the event sources and only enable the registry, now, exit excel
(or whatever app you want to monitor), enable the capture (as above,
this time check "capture...") and start excel (or whatever), the log
window will fill up with entries, once done, stop the capture and
review the entries

[GS]

>>> On Mon, 02 Dec 2019 11:11:24 -0500
>>> (comp.lang.basic.visual.misc,microsoft.public.vb.general.discussion)
>>> <qs3d3g$nnt$1...@dont-email.me>
>>> GS <g...@v.invalid> wrote:
>
>> What I need help with is finding where this info gets stored between
>> runtime by each Excel version so I can make my app work with
>> COMAddins same as it does with VBA Addins. Can anyone shed any light
>> on this or point me where to find good info?
>
> I suppose you already saw this
>
> https://support.microsoft.com/en-us/help/291392/excel-com-add-ins-and-automation-add-ins
>

Yes, thanks, ..along with a multitude of others. I'm quite familiar with
COMAddins so their normal UI workings are not the issue; - it's what goes on
umder the hood I'm after!

> at any rate, another possible way to find out informations may be using
> the "Process Monitor" tool from SysInternals (now Microsoft)
>
> https://docs.microsoft.com/en-us/sysinternals/downloads/procmon
>
> run it (as admin), stop the capture (File, uncheck "capture..."), then
> open the Filter->Filter menu and add one or more filters to only
> include the desired process(es) e.g. "process name contains..." or
> something like that, confirm the filter settings then in the toolbar
> uncheck the event sources and only enable the registry, now, exit excel
> (or whatever app you want to monitor), enable the capture (as above,
> this time check "capture...") and start excel (or whatever), the log
> window will fill up with entries, once done, stop the capture and
> review the entries

I need the functionality for my AddinsManager.exe to run code on the Registry
and wherever else Excel stores its COMAddins.Collection data to populate the
app's listview AND manage (load/unload/install/remove) them outside of Excel.

[Arne Saknussemm]

:: On Wed, 04 Dec 2019 12:59:08 -0500
:: (comp.lang.basic.visual.misc,microsoft.public.vb.general.discussion)
:: <qs8s5f$j70$1...@dont-email.me>

:: GS <g...@v.invalid> wrote:

> > I suppose you already saw this
> >
> > https://support.microsoft.com/en-us/help/291392/excel-com-add-ins-and-automation-add-ins

> Yes, thanks, ..along with a multitude of others. I'm quite familiar
> with COMAddins so their normal UI workings are not the issue; - it's
> what goes on umder the hood I'm after!

And that's why I suggested you to try the "procmon" tool, it may help
you finding what happens when you "manipulate" the addins; monitoring
the registry (and filesystem) activity from excel may help finding out
what happens when you perform a given operation on an addin, at that
point you may be able to replicate the operations in your manager

[GS]

I see! I didn't think of that; - do you think it might show how/where it gets
the data to populate its built-in COMAddins Manager dialog?

[Arne Saknussemm]

:: On Thu, 05 Dec 2019 03:57:27 -0500
:: (comp.lang.basic.visual.misc,microsoft.public.vb.general.discussion)
:: <qsagpq$35n$1...@dont-email.me>

:: GS <g...@v.invalid> wrote:

> > And that's why I suggested you to try the "procmon" tool, it may
> > help you finding what happens when you "manipulate" the addins;
> > monitoring the registry (and filesystem) activity from excel may
> > help finding out what happens when you perform a given operation on
> > an addin, at that point you may be able to replicate the operations
> > in your manager

> I see! I didn't think of that; - do you think it might show how/where
> it gets the data to populate its built-in COMAddins Manager dialog?

Well, a try won't hurt, I think :D

[GS]

I'll let you know...

[GS]

NICE TOOL!
Big THANKS...

[Arne Saknussemm]

> > https://docs.microsoft.com/en-us/sysinternals/downloads/procmon
> >
> > run it (as admin), stop the capture (File, uncheck "capture..."),
> > then open the Filter->Filter menu and add one or more filters to
> > only include the desired process(es) e.g. "process name
> > contains..." or something like that, confirm the filter settings
> > then in the toolbar uncheck the event sources and only enable the
> > registry, now, exit excel (or whatever app you want to monitor),
> > enable the capture (as above, this time check "capture...") and
> > start excel (or whatever), the log window will fill up with
> > entries, once done, stop the capture and review the entries

> NICE TOOL!
> Big THANKS...

Let me know if it will help sorting out your issue, notice that it
could be a good idea to have a look at the other tools on that site,
they may come really useful at times, then, for more tools, also have a
look at the Nir Sofer website https://www.nirsoft.net/ - notice that
some of the tools offered may be flagged by some AVs as "dangerous",
this does NOT mean they're malware or whatever, just that they allow to
perform operations which a regular user won't usually need to perform :)

[GS]

oKAY, THANKS!

[Arne Saknussemm]

:: On Thu, 5 Dec 2019 14:36:25 +0100
:: (comp.lang.basic.visual.misc,microsoft.public.vb.general.discussion)
:: <20191205143...@eternal-september.org>

:: Arne Saknussemm <idm.10...@mamber.net> wrote:

> Let me know if it will help sorting out your issue, notice that it
> could be a good idea to have a look at the other tools on that site,
> they may come really useful at times, then, for more tools, also have
> a look at the Nir Sofer website https://www.nirsoft.net/ - notice that
> some of the tools offered may be flagged by some AVs as "dangerous",
> this does NOT mean they're malware or whatever, just that they allow
> to perform operations which a regular user won't usually need to
> perform :)

Also, and since we're at it, you may consider the idea of picking this
simple (yet useful) tool

https://docs.microsoft.com/en-us/sysinternals/downloads/debugview

at that point, in your own VB code, add a bas module containing the
following code

' ====================================================================
Private Declare Sub OutputDebugString Lib "kernel32"
Alias "OutputDebugStringA" _
(ByVal lpString as String)



Public Sub DbgPrint(ByVal sTxt As String)
Dim sStr As String

sStr = "[" & App.ExeName & "] " & sTxt & vbCrLf & Chr(0)
Call OutputDebugString(sStr)
End Sub
' ====================================================================

now, throughout your code, whenever you'll want to have some diagnostic
message, just add a call to the DbgPrint sub, at this point, to view
the messages, even in the compiled (exe or dll) program, just fire the
debugview tool (link above) set the filter to only show messages from
your application (that is "[youappname]") and start (or load) your
application :)

[GS]

Arne,
Not sure I follow; - I already implement a central error handling system that
writes to error.log so what useful purpose would a runtime messaging system
serve? (I haven't read the link yet!)

[Arne Saknussemm]

:: On Thu, 05 Dec 2019 15:58:16 -0500
:: (comp.lang.basic.visual.misc,microsoft.public.vb.general.discussion)
:: <qsbr1c$879$1...@dont-email.me>
:: GS <g...@v.invalid> wrote:


> > https://docs.microsoft.com/en-us/sysinternals/downloads/debugview

> > '
> > ====================================================================
> > Private Declare Sub OutputDebugString Lib "kernel32" Alias
> > "OutputDebugStringA" _ (ByVal lpString as String)
> >
> >
> >
> > Public Sub DbgPrint(ByVal sTxt As String)
> > Dim sStr As String
> >
> > sStr = "[" & App.ExeName & "] " & sTxt & vbCrLf & Chr(0)
> > Call OutputDebugString(sStr)
> > End Sub
> > '
> > ====================================================================

> Arne,
> Not sure I follow; - I already implement a central error handling
> system that writes to error.log so what useful purpose would a
> runtime messaging system serve? (I haven't read the link yet!)

Central error handler and logging is fine, but the above allows you to
have something similar to "Debug.Print" even inside the compiled
program, at that point, firing the debugview tool you'll be able to see
the debug messages you inserted into the code, it's useful to diagnose
issues which only surface in the compiled program and not in the IDE or
to trace/time the code to improve/optimize it; a better module is the
class below, the code and usage should be pretty straightforward, you
declare the class as a global in your project and instance it, then,
set the desired logging types (or no logging) whose value may (e.g.) be
stored and retrieved from your application config data, at that point,
wherever you'll need to send a "debug/trace" message you invoke the
DbgPrint() method of the class passing it the message and the message
type, the method will internally check if logging is enabled and if
that message type is enabled and, if so, will send the message to the
debugger console (if running, if not the API call returns immediately)



'
' CDbgLog.cls - class to send debug/trace messages to a debug console
' like the "debugview" from SysInternals
'
' the class allows to specify a bitmask to enable/disable selected
' message types, for example one may set the LogLevel property like
'
' gCDbg.LogLevel = mtError + mtWarning + mtDebug
'
' the above will only enable error, warning and debug message and will
' ignore (drop w/o sending to the debugger) all the others
'

Option Explicit

' message types
Public Enum enMsgType
mtNone = 0
mtError = 1
mtWarning = 2
mtInformation = 4
mtVerbose = 8
mtDebug = 16
End Enum

' Debugger API
Private Declare Sub OutputDebugString Lib "kernel32" _

Alias "OutputDebugStringA" _
(ByVal lpString as String)

' private workareas
Private mbIsIDE As Boolean
Private mnLogFlags As enMsgType

' initializes the class module
Private Sub Class_Initialize()
On Local Error Resume Next
mnLogFlags = mtError
mbIsIDE = False
Err.Clear
Debug.Print 1/0
If Err.Number <> 0 Then
mbIsIDE = True
End If
Err.Clear
End Sub

' set the logging flags
Public Property Let LogFlags(ByVal nFlags As enMsgType)
mnLogFlags = nFlags
End Property

' reat the logging flags
Public Property Get LogFlags() As enMsgType
LogFlags = mnLogFlags
End Property

' sends a message to the debugger
Public Sub DbgPrint(ByVal sStr As String, _
Optional ByVal nType As enMsgType = mtInformation)
Dim sType As String, sMsg As String

' check if logging/message type is enabled
If (mnLogFlags = mtNone) Or ((mnLogFlags And nType) = 0) Then
Exit Sub
End If

' initialize message
sMsg = MsgType(nType) & " " & sStr

' if in IDE, send to immediate
If mbIsIDE Then
Debug.Print sMsg
End If

' compose the debugger message
sMsg = "[" & App.ExeName "]" & sMsg & vbCrlf & Chr(0)

' send to debugger (if any)
Call OutputDebugString(sMsg)
End Sub

' decode a message type to string
Private Function MsgType(ByVal nType As enMsgType) As String
Dim sType As String

Select Case nType
Case mtError
sType = "ERROR "
Case mtWarning
sType = "WARNING "
Case mtInformation
sType = "INFORMATION"
Case mtVerbose
sType = "VERBOSE "
Case mtDebug
sType = "DEBUG "
Case Else
sType = "UNKNOWN "
End Select
MsgType = "[" & sType & "]"
End Function

[GS]

Got it!
Thanks a lot; - that's very handy to have.

[Arne Saknussemm]

:: On Fri, 06 Dec 2019 04:47:59 -0500
:: (comp.lang.basic.visual.misc,microsoft.public.vb.general.discussion)
:: <qsd84j$7n4$1...@dont-email.me>

:: GS <g...@v.invalid> wrote:

> Got it!
> Thanks a lot; - that's very handy to have.

Y/W, I slightly modified/improved the code to speed it up a little bit
and to make it a bit more readable, by the way, feel free to modify it
as you want :)


' =====================================================================

' CDbgLog.cls - class to send debug/trace messages to a debug console
' like the "debugview" from SysInternals
'
' the class allows to specify a bitmask to enable/disable selected

' message types, here's an usage example
'
' Set gCDbg = New CDbgLog

' gCDbg.LogLevel = mtError + mtWarning + mtDebug

' gCDbg.DbgPrint "The application is starting", mtInformation
' gCDbg.DbgPrint "Current date is " & Now(), mtDebug
'
' the above will only enable error, warning and debug messages and will
' ignore (drop w/o sending to the debugger) all the others, so running
' the code you'll only see the "current date" message and not the other
' notice that omitting the message type, it defaults to "information"
' =====================================================================

Option Explicit

' message types (and log level bitmask)
Public Enum enMsgType
mtNone = 0 ' no messages at all
mtError = 1 ' error
mtWarning = 2 ' warning
mtInformation = 4 ' information
mtVerbose = 8 ' detailed information
mtDebug = 16 ' debugging information
mtUserDef = 32 ' user defined
mtEverything = 255 ' any message

End Enum

' Debugger API
Private Declare Sub OutputDebugString Lib "kernel32" _
Alias "OutputDebugStringA" _
(ByVal lpString as String)


' private workareas

Private msAppName As String ' application name
Private mbIsIDE As Boolean ' true=in IDE
Private mnLogFlags As enMsgType ' enabled message types bitmask

' initializes the class module
Private Sub Class_Initialize()
On Local Error Resume Next

' setup basic informations
msAppName = "[" & App.ExeName & "]"
mnLogFlags = mtError

' set the "in IDE" flag

mbIsIDE = False
Err.Clear
Debug.Print 1/0
If Err.Number <> 0 Then
mbIsIDE = True
End If

' all done
Err.Clear
End Sub

' set the logging flags (bitmask)

Public Property Let LogFlags(ByVal nFlags As enMsgType)
mnLogFlags = nFlags
End Property

' reat the logging flags (bitmask)

Public Property Get LogFlags() As enMsgType
LogFlags = mnLogFlags
End Property

' send a message to the debugger

Public Sub DbgPrint(ByVal sStr As String, _
Optional ByVal nType As enMsgType = mtInformation)
Dim sType As String, sMsg As String

' check if logging for this message type is enabled
If ((mnLogFlags And nType) = 0) Then
Exit Sub
End If

' base message type and text
sMsg = MsgType(nType) & sStr

' check if IDE or runtime
If mbIsIDE Then
' if in IDE, send to immediate window
Debug.Print sMsg
Else
' not in IDE compose and send to the debugger
sMsg = msAppName & sMsg & vbCrlf & Chr(0)
Call OutputDebugString(sMsg)
End If

End Sub

' decode a message type to string
Private Function MsgType(ByVal nType As enMsgType) As String
Dim sType As String

' check the type and set the string

Select Case nType
Case mtError
sType = "[ERROR ] "
Case mtWarning
sType = "[WARNING ] "
Case mtInformation
sType = "[INFORMATION] "
Case mtVerbose
sType = "[VERBOSE ] "
Case mtDebug
sType = "[DEBUG ] "

Case mtUserDef
sType = "[USERDEFINED] "
Case Else
' unknown, use the type number (hex)
sType = Left(Right("0000" & Hex(nType), 4) & Space(11), 11)
sType = "[" & sType & "]"
End Select

' all done
MsgType = sType
End Function

[GS]

Nice!
Just curious.., why the padding for sType?

[Arne Saknussemm]

> Just curious.., why the padding for sType?

Nothing special, it's just for readability, see the dbgview tool offers
an option to also save the logged informations to a file, in such a
case it's nice to have a fixed width "record heading" :)

[GS]

Makes sense!

[ObiWan]

:: On Mon, 09 Dec 2019 03:52:03 -0500
:: (comp.lang.basic.visual.misc,microsoft.public.vb.general.discussion)
:: <qsl1vk$im2$1...@dont-email.me>

:: GS <g...@v.invalid> wrote:

> >
> >> Just curious.., why the padding for sType?
> >
> > Nothing special, it's just for readability, see the dbgview tool
> > offers an option to also save the logged informations to a file, in
> > such a case it's nice to have a fixed width "record heading" :)
>
> Makes sense!

Getting back on topic, did the procmon tool help you in finding the
addin infos you wanted ?

[GS]

Not yet; - I'm going to have to study using it more than I have; - so far it
shows the same Registry keys as I currently query, but also shows some DLL
activity to load its COMAddins Manager dialog. I have to learn how to examine
that better to see what it's doing under-the-hood!