Run an Add-in from another workbook

[TheCman81]

Hi All,

Thanks in advance.

Excel 2007

I'm looking for a macro to run the same Add-In on various different workbooks. Add-In name is "HRwsBTH"

I have about 20+ workbooks that I need to open and manually refresh my Add-In and it's very time consuming.

So what I would like to achieve with the macro is the following:

1. Open a file
2. Run the Add-In
3. Save the file
4. Close the workbook

I need these steps repeated for each workbook

Thanks

Colin

 

[wigi]

Hello Colin

What is your code to do it on 1 workbook?

 

[TheCman81]

Hello Colin

What is your code to do it on 1 workbook?

Hiya. There's no code at all, starting from scratch

 

[wigi]

What macro do you execute in the addin?
Did you use the macro recorder to explore some generated code?

 

[TheCman81]

There's isn't a macro to run the add-in, the add-in has a built in refresh button that you select

 

[wigi]

In that case, you could try to see what macro is behind the refresh button.
Or is it merely a recalculation that is triggered?

 

[TheCman81]

The actual code behind the add-in is password protect so can't view it but I've just been told this bit of code can activate the refresh part of the add-inn


<code>Run ("</code>HRwsBTH<code>.xla!Refresh"), True</code></pre>

 

[wigi]

In that case, loop over the files (Dir function) and execute that line of code:

Sub GetFilesInLoop()

Dim FileName As String


'Edit this declaration to your folder name
FilePath = "C:\myfiles"


FileName = Dir(FilePath & "\*.xls")


Do While FileName <> vbNullString


    With Workbooks.Open(FilePath & "\" & FileName)
        Run "HRwsBTH.xla!Refresh"
        .Save
        .Saved = True
        .Close 0
    End With
    FileName = Dir()
Loop


End Sub

 

[TheCman81]

Would I be right in thinking since I have 20+ workbooks the code would look like this:

Sub GetFilesInLoop()

Dim FileName As String


'Edit this declaration to your folder name
FilePath = "C:\myfiles"


FileName = Dir(FilePath & "\Workbook1.xls")
FileName = Dir(FilePath & "\Workbook2.xls")
FileName = Dir(FilePath & "\Workbook3.xls")
FileName = Dir(FilePath & "\Workbook4.xls")

'and so on....


Do While FileName <> vbNullString


    With Workbooks.Open(FilePath & "\" & FileName)
        Run "HRwsBTH.xla!Refresh"
        .Save
        .Saved = True
        .Close 0
    End With
    FileName = Dir()
Loop


End Sub

 

[wigi]

No, that is not right. The Dir() function and the Do While Loop take care of searching the files.
Within that loop, each file is opened and the one statement you already had, is executed.
You have to provide the folder name, not all individual file names.