excel - VBA will not Refresh Queries

Question

Backstory: I am currently working on a "make-shift" database using excel. I will have one central excel workbook which will house the tables on separate sheets storing data. Other workbooks will interface with this database excel workbook and will input new lines or replace existing lines in the tables by each user interface. The user-interface workbooks will have query tables pulling from the database workbook tables as needed to provide the users with information. I am sure there must be a better way of doing this. Any tips tricks or pointers are much appreciated.

Problem: I have written in my VBA Database Input code to Refresh All queries however it does not update the queries. I have made a separate sub to update queries but when it is called by the Databse Input code it does not.

Sub Refresh_Queries

Dim ActWB as Object
Set ActWB = ThisWorkbook
ActWB.RefreshAll

End Sub

标签： exceldatabasevbadatatable

Answer 1

Are these actual query objects, or something else, perhaps? Make sure you are acting on the correct type of object. Maybe these are workbook connections. Just making a guess here...

Sub Something()

Dim Connection As Variant
For Each Connection In ActiveWorkbook.Connections
    Connection.OLEDBConnection.BackgroundQuery = False
    Connection.Refresh

Next Connection

End Sub

Or...

Sub Workbook_RefreshAll()
    ActiveWorkbook.RefreshAll
End Sub

I hav seen some weird things in the past, like doing a single refresh doesn't work, but doing it a second time works perfectly fine. See the link below for some additional ideas of what you can do to refresh queries, connections, and the like.

https://analysistabs.com/vba-code/workbook/m/refreshall/