Programmatically copy custom shortcut menu bars (command bars)?

[downwitch]

Hi all,

I've read through the archives on this, and scoured the web, to little
avail.

There has to be a way to move custom menu bars (or menubars, or command
bars, or popup command bars, or whatever) from one database to another,
because it's an interface import option. I'm happy to slog through
coding it myself, but I need a few pointers. Anyone out there done
this, or found code templates for it? Seems like the CommandBar object
belongs to the Office library--how do you attach that to a database
then, using code only?

Just in case: yes, I know about File > Get external data > Import as a
copying method. I wish to do this purely through automation and DAO,
so that's not an option.

Any help would be greatly appreciated.

[salad]

downwitch wrote:

Hi all,

I've read through the archives on this, and scoured the web, to little
avail.

There has to be a way to move custom menu bars (or menubars, or command
bars, or popup command bars, or whatever) from one database to another,
because it's an interface import option. I'm happy to slog through
coding it myself, but I need a few pointers. Anyone out there done
this, or found code templates for it? Seems like the CommandBar object
belongs to the Office library--how do you attach that to a database
then, using code only?

Just in case: yes, I know about File > Get external data > Import as a
copying method. I wish to do this purely through automation and DAO,
so that's not an option.

Any help would be greatly appreciated.

I did this a long time ago by importing the table MSysCmdbars. Of
course the mdb with the code is on my old computer which has been
disassemble so I don't have it. I can't remember what I did because
usually the system tables are hidden and I was able to unhide the table
then import or I was able to connect/link to the table and append to the
new database table or else delete the new database table and append in.
Whatever...it worked. I hope this gives you a starting point.

[downwitch]

Thanks, but I think MSysCmdbars disappeared after 97. In any case,
it's no longer visible, even with system tables visible.

[Lyle Fairfield]

The top couple are très ancient but might work in 97; the bottom
couple are not so très ancient and might work in later versions:
Sub test()
GetMenus "C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\northwind.mdb", _
"NorthwindCustomMenuBar"
End Sub

Sub GetMenus(ByVal dbsName As String, ParamArray MenuNames())
Dim bConfirmActionQueries As Boolean
Dim vMenuName As Variant
Dim rst As Recordset

On Error Resume Next

bConfirmActionQueries = GetOption("Confirm Action Queries")
If bConfirmActionQueries Then SetOption "Confirm Action Queries",
False

With DBEngine(0)(0)

Err = 0
.TableDefs("MSysCmdBars").Name = .TableDefs("MSysCmdBars").Name
If Err = 3265 Then 'table doesn't exist
.Execute "SELECT * INTO MSysCmdBars " _
& "FROM MSysCmdBars IN '" & dbsName & "' WHERE NO;"
.Execute "CREATE INDEX TbIndex ON MSysCmdBars (TbName) WITH
PRIMARY;"
End If
On Error GoTo 0
If UBound(MenuNames) = -1 Then 'no elements
.Execute "INSERT INTO MSysCmdBars " _
& "SELECT * FROM MSysCmdBars IN '" & dbsName & "';"
Else
For Each vMenuName In MenuNames
.Execute "INSERT INTO MSysCmdBars " _
& "SELECT * FROM MSysCmdBars IN '" & dbsName & "' " _
& "WHERE TBName = '" & vMenuName & "';"
Next vMenuName
End If

.TableDefs.Refresh
Set rst = .TableDefs("MSysCmdBars").OpenRecordset
With rst
If Not (.BOF = True And .EOF = True) Then
.MoveFirst
On Error Resume Next
Do
CommandBars.Add (!TBName)
.MoveNext
Loop Until .EOF
On Error GoTo 0
End If
End With
Set rst = Nothing
End With

If bConfirmActionQueries Then SetOption "Confirm Action Queries",
True

End Sub

Sub g()
With WizHook
.Key = 51488399
.WizCopyCmdbars "C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\ESO\EsoAdmin.adp"
End With
End Sub

Sub g1()
With WizHook
.Key = 51488399
.WizCopyCmdbars "C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\northwind.mdb"
End With
End Sub

[downwitch]

Well... That felt very exciting for a moment. I'd never heard of the
fancy WizHook, the undocumented tool! The gold mine, the magic key!

But no. The first bit of code founders because there's no MSysCmdBars
past 97. And according to the little bit of doc I found on the web,
2002 is the first version that supported the WizHook method.

I, naturally, am using 2000. So I get the dreaded Error 438 Object
doesn't support this property or method. So thanks Lyle, but no dice.

Did make me wonder, however, whether there might not be some shadow
documentation out there of the wizard mdes-- acwztool.mde,
acwzmain.mde, and acwzlib.mde--and whether command-bar stuff might live
in there.

Any other ideas?

[Lyle Fairfield]

Wizhook was definitely in AC2K. Peter Walker was the first to post
about it shortly after AC2K was shipped. I can verify that
(.WizCopyCmdBars) exists and works in Ac2002 and Ac2003. It seems then
that it's missing from AC2K; arrrrrrrgggggggghhhhhh.
****
If we are in hack land this is quite clumsy but TTBOMK one can create
CmdBars in AC97 then copy MSysCmdBars from the AC97 db to an AC>97 db.
Limited testing shows that the toolbars now work and can be transferred
to and from AC>97 dbs
(and updated) using the MSysCmdBars table. My guess is that MS removed
the table without removing the default use of the table. But this is on
the cusp of being ridiculous.
****
Life is grand.

[Sky]

"downwitch" <do*******@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Any other ideas?

You could start with something like below.
- Steve

' Copy a command bar or toolbar to a new one
' Example: CommandBarCopy "Form View", "tbrFormViewNew"

Public Function CommandBarCopy(strCbrOrigName As String, strCbrNewName As
String) As Boolean
Dim cbrNew As CommandBar, ctl As CommandBarControl
On Error GoTo Problem
Set cbrNew = CommandBars.Add(strCbrNewName)
cbrNew.Visible = True
For Each ctl In CommandBars(strCbrOrigName).Controls
ctl.Copy cbrNew
Next ctl
Set ctl = Nothing
Set cbrNew = Nothing
Exit Function
Problem:
MsgBox Err.Description, vbCritical, "CommandBarCopy Error"
End Function

[downwitch]

Wow, a 97 pass-through. I never would have thought of that! Nor, I
think, have I the patience.

But I agree, life *is* grand.

Lyle Fairfield wrote:

Wizhook was definitely in AC2K. Peter Walker was the first to post
about it shortly after AC2K was shipped. I can verify that
(.WizCopyCmdBars) exists and works in Ac2002 and Ac2003. It seems then
that it's missing from AC2K; arrrrrrrgggggggghhhhhh.
****
If we are in hack land this is quite clumsy but TTBOMK one can create
CmdBars in AC97 then copy MSysCmdBars from the AC97 db to an AC>97 db.
Limited testing shows that the toolbars now work and can be transferred
to and from AC>97 dbs
(and updated) using the MSysCmdBars table. My guess is that MS removed
the table without removing the default use of the table. But this is on
the cusp of being ridiculous.
****
Life is grand.

[downwitch]

Thanks Steve, but that actually only copies a command bar within the
current application. I'm trying to move it from one to another, and
since CommandBar belongs to Office (as opposed to Application or some
useful between-db library), you can't automate it or anything.

Sky wrote:

You could start with something like below.
- Steve

' Copy a command bar or toolbar to a new one
' Example: CommandBarCopy "Form View", "tbrFormViewNew"

<snip>

[Jeff Conrad]

Haven't tried this myself, but have you seen this?:

http://smsconsulting.spb.ru/shamil_s...s/copycmdb.htm

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conrad...essjunkie.html
http://www.access.qbuilt.com/html/articles.html
"downwitch" wrote in message:
news:11**********************@g44g2000cwa.googlegr oups.com...

Thanks Steve, but that actually only copies a command bar within the
current application. I'm trying to move it from one to another, and
since CommandBar belongs to Office (as opposed to Application or some
useful between-db library), you can't automate it or anything.

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

[downwitch]

Huh, that mostly works. Meaning CommandBars *does* belong to
Application, even if it doesn't expose that way. Shoulda tried that
first I guess, rather than believing the object browser and CTL+SPACE.

Now I just need to figure out how to copy images (that code doesn't
seem to...) and I'll be set. Thanks to all who pitched in.
Jeff Conrad wrote:

Haven't tried this myself, but have you seen this?:

http://smsconsulting.spb.ru/shamil_s...s/copycmdb.htm

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conrad...essjunkie.html
http://www.access.qbuilt.com/html/articles.html

[downwitch]

Follow-up: how to copy "button image" from one commandbarcontrol to
another:

Sub uCommandBarCtl_CopyImage(pctlCbrSource As CommandBarControl, _
pctlCbrNew As CommandBarControl)
On Error Resume Next

'get commandbar button image (custom or built-in) and paste
pctlCbrSource.CopyFace
pctlCbrNew.PasteFace

End Sub

[Jeff Conrad]

Thanks for the update and additional code sample.

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conrad...essjunkie.html
http://www.access.qbuilt.com/html/articles.html

"downwitch" wrote in message:
news:11*********************@z14g2000cwz.googlegro ups.com...

Follow-up: how to copy "button image" from one commandbarcontrol to
another:

Sub uCommandBarCtl_CopyImage(pctlCbrSource As CommandBarControl, _
pctlCbrNew As CommandBarControl)
On Error Resume Next

'get commandbar button image (custom or built-in) and paste
pctlCbrSource.CopyFace
pctlCbrNew.PasteFace

End Sub

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----