How to do it in macro?

[Eric]

I get a third party add-on package for Excel within additional bar, whenever
I use it by clicking pull down menu and select "download / refresh data". I
would like to set macro to perform this task, and I tried creating a macro to
record this action, but it record nothing. Does anyone have any suggestions
on any trick to do it within Excel 2003?
Thanks in advance for any suggestions
Eric

 

[Matthew Herbert]

Eric,

You've probably noted that Excel adheres to a hierarchy (e.g. application,
workbook, worksheet, range for a Range Object). CommandBars are similar in
the sense that you have to drill down from the highest to the lowest level of
the control. The code below is one way to specifically reference a control
on a CommandBar. (Obviously, the code below is for Thomson Reuters, a
third-party data service provider). I would also look up FindControl(s) in
the VBE Help as well because you may find it useful for what you are doing
(or at least I have found it to be useful). As a side note, depending on the
events that are tied to executing the button on the CommandBar, you may need
DoEvents.

Best,

Matthew Herbert

Dim cBarBtn As CommandBarButton

Set cBarBtn = Application.CommandBars("Worksheet Menu
Bar").Controls("Thomson Reuters").Controls("&Refresh
Data").Controls("&Selected Cell(s)")
If Not cBarBtn Is Nothing Then cBarBtn.Execute

 

[Eric]

Let me interpret more details about the command bar, which just like the bar
changing the size and font within Excel. Within this bar, there is a "ABC"
button, once clicking this button, and sub-menu pull down, and select the
function Download / Refresh data. This is what it looks like when I manually
go through this process. Does it look like what your coding is about?
Thank you very much for any suggestions
Eric

 

[Eric]

I get an error 438, which related to some property.
Is the name of "Worksheet Menu Bar" replaced by the specific barname?
If the name of the bar is MyBar, should I replace "Worksheet Menu Bar" with
"MyBar". A pull down menu is shown by clicking "ABC" button, "Download /
Refresh Data" function name should be selected. Should I change the code as
shown below? I have tried it, it return error 438.
Do you have any suggestions?
Thank you very much for any suggestions
Eric

Dim cBarBtn As CommandBarButton

Set cBarBtn =
Application.CommandBars("MyBar").Controls("ABC").Controls("&Download /
Refresh Data").Controls("&Selected Cell(s)")
If Not cBarBtn Is Nothing Then cBarBtn.Execute

 

[Matthew Herbert]

Eric,

Your code looks fine (minus the "Worksheet Menu Bar"); however, if you're
simply running off of what is "visible" via the menu drop-down, then there
could potentially be a misspelling, missing space, missing "...", etc. I
tried putting together a recursive process to loop through the CommandBars
object, but since objects are passed ByRef, I couldn't get the results I was
looking for in a timely manner. I decided to forego the recursive object
approach and use the "three-level" system. As far as I know, Excel 2003 can
handle only three sub-menu levels. The code below reflects this idea (though
not fully tested). The code below is giving me some unexpected results;
however, it should work for now (without me spending more time on it).

Run "MenuLoop" and see what comes up under your "MyBar" menu. Keep in mind
that I added some leading spaces before each .Caption to create indenting. I
used three spaces for the first level, six spaces for the second level, nine
.... Again, check the spelling.

Also, try doing the following (i.e. add the "Worksheet Menu Bar"):

Set cBarBtn =
Application.CommandBars("Worksheet Menu
Bar").Controls("MyBar").Controls("ABC").Controls("&Download / Refresh
Data").Controls("&Selected Cell(s)")

Best,

Matt

Sub MenuLoop()

Dim CtrlsMenu As CommandBarControls
Dim CtrlsSubMenuOne As CommandBarControls
Dim CtrlsSubMenuTwo As CommandBarControls
Dim CtrlsSubMenuThr As CommandBarControls

Dim lngCntMenu As Long
Dim lngCntSubMenuOne As Long
Dim lngCntSubMenuTwo As Long
Dim lngCntSubMenuThr As Long

Dim cBar As CommandBar
Dim strCaption As String
Dim Wks As Worksheet
Dim lngCnt As Long
Dim intCnt As Integer

Set Wks = Worksheets.Add

Set cBar = Application.CommandBars("Worksheet Menu Bar")

intCnt = 0

With Wks
'loop menus
For lngCntMenu = 1 To cBar.Controls.Count
'Debug.Print "cBar.Name:"; cBar.Name
lngCnt = 1
intCnt = intCnt + 1
.Cells(lngCnt, intCnt).Value = cBar.Controls(lngCntMenu).Caption
lngCnt = lngCnt + 1

Set CtrlsMenu = cBar.Controls(lngCntMenu).Controls
'loop sub menu (1st level)
For lngCntSubMenuOne = 1 To CtrlsMenu.Count
strCaption = CtrlsMenu(lngCntSubMenuOne).Caption
If strCaption <> "" Then
.Cells(lngCnt, intCnt).Value = " " & strCaption
lngCnt = lngCnt + 1
End If
On Error Resume Next
Set CtrlsSubMenuOne = CtrlsMenu(lngCntSubMenuOne).Controls
On Error GoTo 0

If Not CtrlsSubMenuOne Is Nothing Then
'loop sub menu (2nd level)
For lngCntSubMenuTwo = 1 To CtrlsSubMenuOne.Count
strCaption = CtrlsSubMenuOne(lngCntSubMenuTwo).Caption
If strCaption <> "" Then
.Cells(lngCnt, intCnt).Value = " " & strCaption
lngCnt = lngCnt + 1
End If
On Error Resume Next
Set CtrlsSubMenuTwo =
CtrlsSubMenuOne(lngCntSubMenuTwo).Controls
On Error GoTo 0

If Not CtrlsSubMenuTwo Is Nothing Then
'loop sub menu (3rd level)
For lngCntSubMenuThr = 1 To CtrlsSubMenuTwo.Count
strCaption =
CtrlsSubMenuTwo(lngCntSubMenuThr).Caption
If strCaption <> "" Then
.Cells(lngCnt, intCnt).Value = " " &
strCaption
lngCnt = lngCnt + 1
End If
Next lngCntSubMenuThr
End If

Next lngCntSubMenuTwo
End If

Next lngCntSubMenuOne
Next lngCntMenu
End With

Set CtrlsMenu = Nothing
Set CtrlsSubMenuOne = Nothing
Set CtrlsSubMenuTwo = Nothing
Set CtrlsSubMenuThr = Nothing
Set cBar = Nothing
Set Wks = Nothing

End Sub