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?

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 anyone have any suggestions?
Thanks in advance for any suggestions
Eric

 

[Gary Brown]

Hi Eric.
1st you have to know what the name of that 'additional bar' is.
Go up to the bars at the top and right-single-click. A listing of ALL the
command bars should show up. The one's that are checked are the active ones.
Hopefully, you can tell what the name of the 'additional bar' is. Write it
down EXACTLY as you see it. If one of the letters is underlined, you need an
amperstand before that letter. For example, if your 'additional bar' said
"MyBar" and the 'B' was underlined, you want to write down... My&Bar

Using that example, your macro should look something like...

'/======================================
Public Sub Test_Menu_Item_Run()
Dim cmdBarItem As CommandBarButton

On Error Resume Next

Set cmdBarItem = _
Application.CommandBars("My&Bar"). _
Controls("ABC").Controls("Download / Refresh data")

If Not cmdBarItem Is Nothing Then cmdBarItem.Execute

Set cmdBarItem = Nothing

End Sub
'/======================================

Remember, the spelling and capitalization MUST be identical.

 

[Eric]

Thank you very much for suggestions

Hi Eric.
1st you have to know what the name of that 'additional bar' is.
Go up to the bars at the top and right-single-click. A listing of ALL the
command bars should show up. The one's that are checked are the active ones.
Hopefully, you can tell what the name of the 'additional bar' is. Write it
down EXACTLY as you see it. If one of the letters is underlined, you need an
amperstand before that letter. For example, if your 'additional bar' said
"MyBar" and the 'B' was underlined, you want to write down... My&Bar

Using that example, your macro should look something like...

'/======================================
Public Sub Test_Menu_Item_Run()
Dim cmdBarItem As CommandBarButton

On Error Resume Next

Set cmdBarItem = _
Application.CommandBars("My&Bar"). _
Controls("ABC").Controls("Download / Refresh data")

If Not cmdBarItem Is Nothing Then cmdBarItem.Execute

Set cmdBarItem = Nothing

End Sub
'/======================================

Remember, the spelling and capitalization MUST be identical.

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

 

[Eric]

I would like to add mroe coding for this master.xls file
1) opening file1.xls
2) refreshing all from external links within file1.xls
3) performing following task, which have been coded
4) close file1.xls
5) open file2.xls
6) running specific macro under this worksheet, which will be closed
automatically
7) close this master file

Do you have any suggestions?
Thank you very much for any suggestions
Eric