All geek questions in one place

Excel Querytable Refresh only works once

I am adding ListObject to an Excel 2007 workbook using VBA. ListObject must have a QueryTable behind it, referencing the Access database. The code is as follows:

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 update the table more than once. The Refresh button on the Data and Tabular design ribbons is grayed out. I tried similar code without using Listobjects (i.e. Just QueryTables) and get the same problem. I tried updating the base connection object and getting the same problem again.

I spent all morning at Googling to no avail.

Is this a bug, a designed behavior, or (most likely) am I doing something stupid?

Thank you very much in advance,

Steve

+4
source share
2 answers

Ok, I earned it. The macro recorder (thanks for the Dick suggestion) was really useful 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
+3
source

This is UNTESTED , but it should still work, it checks if the table is already in the update, and if so, it will wait 1 second and check again until it stops updating. he 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
+1
source

Source: https://habr.com/ru/post/1479899/

More articles:


All Articles