CODEWITHSUNDEEP
excelvbaoledb
steveo40
steveo40

Reputation: 931

Excel Querytable Refresh only works once

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

Upvotes: 4

Views: 8632

Answers (2)

steveo40
steveo40

Reputation: 931

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

Upvotes: 4

user2140261
user2140261

Reputation: 7993

This is UNTESTED but it still should work, it will check if the table is already in a refresh and if it is, it will wait 1 second and check again until it is no longer refreshing then it will continue

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"))
    With l
        .QueryTable.Refresh False

             Do while .Refreshing 
                 Application.Wait Now + TimeValue("00:00:01")
             Loop

         'this line causes an error
        .QueryTable.Refresh False

End With

Upvotes: 1

Related Questions