Trying to create a macro that is able to go within an installed add-in

**jmuduke08** · 07-31-2012, 10:37 AM

Hello all,

First, let me state that I am a beginner in terms of VBA language and macros but let me attempt to explain what I am trying to do in Excel 2007.

I want to record/create a macro that goes to a specific add-in tab and within the specific addin tab, refreshes the entire worksheet (via the add-in).

So for example, I use an Add-in that I pull historical data from. In order to update this historical data (the manual way), I have to open excel, click over to the actual add in tab, and then click refresh, then refresh entire workbook (an option within this particular add-in). What I want to do, is be able to just click a button that does these three manual steps for me each time.

If I am still somewhat unclear, its almost similar to recording a macro that would go and click file-print each time I click a button (instead of having to manually click file-print) (and I am able to do this). However, my button will not work when I try to record it, going within another add-in.

Hope this makes sense and thanks for the help.

**jmuduke08** · 07-31-2012, 11:44 AM

if anyone wants more clarity on my OP, let me know. I can see how it is a bit confusing

**Alf** · 07-31-2012, 04:49 PM

To activate a tab in Excel one can use a macro like this:

Option Explicit

Sub Activate_Tab()

Dim AltKey As String

AltKey = “%”

SendKeys AltKey & “(N)”, False

SendKeys AltKey & “”, False

End Sub

This will activate the “Insert” tab. To see the other letters for Excel’s tabs press the “Alt” button.

If your “add-in” tab is represented by a letter substitute “N” with the appropriate letter and test if it will activate the “right” tab.

and then click refresh, then refresh entire workbook (an option within this particular add-in).

Have you tried to record a macro while doing these last two steps? As I don't know what add-in you are using it's difficult to give any advice but since it can be intergrated with Excel I would ask the vendor for help.

Alf

**jmuduke08** · 07-31-2012, 04:56 PM

Thanks Alf! Two questions (since I am a total n00b with writing these):

1) How do I find out what the name of my third party add-in is (within the VBA writing)?

2) In your example, you go within the Insert tab. How do I write the macro in order to go within the tab and actually perform a function located within that tab (i.e if I used your example and on the Insert tab wanted to insert a shape or something)?

thanks!

**Alf** · 08-01-2012, 11:27 AM

1) How do I find out what the name of my third party add-in is (within the VBA writing)?

Did you press the ”Alt” button? See Excel2.jpg

On the other hand I’m not sure you need to activate the add-in tab. In Excel these tabs are just a handy help for the user.

To insert a new sheet in Excel I could start by activating the “Home” tab, click on “Insert” and select “Insert Sheet”.

A macro like the one below would just as easily add a new sheet and it you don’t need to activate the “Home” tab first.

Sub Add_sheet()
Sheets.Add
End Sub

With a keyboard shortcut you can call this macro from any of Excels tabs so normally you don’t have to activate an Excel tab when you are using macros.

Since your third party add-in seems to be able to integrate with Excel I would think that it is possible to write macros to automate processes. Since I don’t know what add-in you are using I’m unable to give you any advice. Get in touch with the supplier of this software

Alf