Refresh single table with external data source

[MurrayBarn]

Hi There

I have an Excel add-in that pulls data from the online accounting package, Xero, into Excel and the resulting data gets put in a table with a name like "XeroCoreTables_Invoices_08311f4f_3c8a_4066_9520_efce9b606a15". The add-in has a Refresh All and a Refresh Selected option. What this means is that if one has multiple tables pulling data from mulitple companies from Xero, you can choose to refresh the table that has focus or to refresh all the tables.

Below is some example code (most is not applicable for this query but I have left in in case it could help someone see what needs to be done) the app designer provided to see how the macros could work with his app. I am struggling to get hold of him and was wondering whether my struggle is just an Excel syntax issue or if I need to get hold of him to actually get some code from him to make it work.

Near the bottom of the code is the line "automationObject.RefreshAll" which I have successfully run to refresh all the add-in's tables, but I have about 10 tables and this takes up to ten minutes to refresh so want to write a short macro to refresh selected tables. Any ideas on how to use this macro to just refresh say three selected tables?

The tables appear to be just a normal Excel table once the refresh has been done and does not have a live link to Xero. When the Refresh option is triggered, the add-in appears to use info stored in the table to go and pull the data from Xero and then repopulate the table.

Sub GettingStarted()
On Error GoTo ErrorHandler
Dim addin As Office.COMAddIn
Dim TxtRng As Range
Dim automationObject As Object
Set addin = Application.COMAddIns("ExcelIntegrationTools")
Set automationObject = addin.Object

' ''Hello World!
'automationObject.DisplayMessage

' ''Get a list of all Connections
Set TxtRng = ActiveWorkbook.ActiveSheet.Cells(1, 15)
Dim connections() As String
connections = automationObject.GetConnections

Dim i As Integer

For i = 0 To UBound(connections)
Set TxtRng = ActiveWorkbook.ActiveSheet.Cells(i + 1, 15)
TxtRng.Value = connections(i)
Next i

''Set the current connection to Demo Company (AU) - This is case sensitive
automationObject.SetConnection ("Demo Company (AU)")

''Get the current Connection
Set TxtRng = ActiveWorkbook.ActiveSheet.Cells(1, 17)
TxtRng.Value = automationObject.GetConnection

''Refresh all tables with data from the current connection
automationObject.RefreshAll

MsgBox "Done!"
Exit Sub
ErrorHandler:
MsgBox Err.Number

'MsgBox ("Done.")


End Sub

 

[mrmmickle1]

Normal Syntax to refresh a single pivot table looks like this:

[COLOR=#333333]ActiveSheet.PivotTables(1).PivotCache.Refresh[/COLOR]

If you're referencing a ListObject table this thread may be of use:

http://www.mrexcel.com/forum/excel-questions/513699-visual-basic-applications-code-refresh-query-table.html

or perhaps this:

https://msdn.microsoft.com/en-us/library/office/ff834459.aspx

or:

http://www.mrexcel.com/forum/excel-...ng-listobjects-visual-basic-applications.html

 

[MurrayBarn]

Thanks Matt. I had a look at the links you posted but from trying what they say, I think I need more info on how to trigger the app to only refresh one table instead of all.

The code I posted above successfully refreshes all the tables by using the app to get the data from Xero but I cant find a way to get it to refresh table X

 

[mrmmickle1]

Sorry I can't be of more help. Not to familiar with COM Add-ins. May be a little out of my league. I don't have much experience with Visual Studio, C# or VB