I am adding a ListObject to an Excel 2007 Workbook using VBA. The ListObject is to have a QueryTable behind it, linking to an Access database. The code looks like this:
Dim l As ListObject
Dim c As ADODB.Connection
Dim r As ADODB.Recordset
Set c = New ADODB.Connection
c.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myAccessDatabasePath;Persist Security Info=False;"
Set r = New ADODB.Recordset
r.Open "Select * From myTable", c
Set l = ActiveSheet.ListObjects.Add(xlSrcQuery, r, True, xlYes, Range("A1"))
l.QueryTable.Refresh False
'this line causes an error
l.QueryTable.Refresh False
Essentially the problem is that I cannot refresh the table more than once. The Refresh button on both the Data and the Table Design ribbons is greyed out. I have tried similar code without using Listobjects (i.e. just QueryTables) and get the same problem. I have tried refreshing the underlying connection object and again, get the same problem.
I've spent all morning Googling to no avail.
Is this a bug, designed behaviour or (most likely) am I doing something stupid?
Many thanks in advance,
Steve
Ok, I got it to work. The macro recorder (thanks for the suggestion Dick) was actually useful for once.
Dim s As Worksheet
Dim l As ListObject
Set s = ActiveSheet
Set l = s.ListObjects.Add(xlSrcExternal, "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myDatabasePath;", True, xlYes, Range("A1"))
l.QueryTable.CommandType = xlCmdTable
l.QueryTable.CommandText = "mytable"
l.QueryTable.Refresh False
'this now works!
l.QueryTable.Refresh False