VBA Code to refresh data no longer works

[MR Campbell]

Ok ... for some time I have had a puzzling issue with an Excel macro enabled workbook that contains a single SQL query to another database. I cannot stress more that for YEARS it worked with no problems at all. Then one day it just does not work anymore .... the code runs but it does not retrieve the latest data. No error messages are generated, so you are led to thinking that it has updated but it hasn't updated the data at all. The only way I can get it to work now is to physically click on Data then click on the REFRESH ALL. This workbook only has ONE query to a database.

This is the code I used for years (then one day in 2016 it did not work .. no error message at all):
Sheets("Data").Range("a2").QueryTable.Refresh BackgroundQuery:=False

Then I tried this and it worked for a while (this was obtained from the macro recorder):
ActiveWorkbook.Connections("Query from SynergyOne").Refresh

This failed (still no error message ... it just didn't do anything).
Then I tried (this worked for about 6 months)

ActiveWorkbook.RefreshAll

This worked up until July 2016.

So at the moment to get the workbook to execute the refresh of the query, I have to physically click on REFRESH ALL. I would be extremely grateful to anyone who can shed some light on this problem.

 

[Yongle]

Try this instead

Sheets("Data").ListObjects(1).Refresh

 

[MR Campbell]

I will try this. What is your idea as to why the code that DID work no longer works?

 

[MR Campbell]

Try this instead

Sheets("Data").ListObjects(1).Refresh

I have just tested this and YES it works! Many thanks!!
Do you know why I was having the problems I was experiencing?

 

[Yongle]

Do you know why I was having the problems I was experiencing?

- a minor corruption?
- a compatability issue? (was this an xls that is now xlsm?)

These all work for me

    ActiveWorkbook.RefreshAll
    ActiveWorkbook.Connections(1).Refresh
    ActiveWorkbook.Connections("Query - From SynergyOne").Refresh
    Sheets("Data").ListObjects(1).Refresh
    Sheets("Data").ListObjects("From_SynergyOne").Refresh
    Sheets("Data").Range("A2").ListObject.Refresh

This does not work for me either - if it worked for you in the past, then Microsoft have changed something

    Sheets("Data").Range("A2").QueryTable.Refresh BackgroundQuery:=False

but the same syntax using ListObject (ie an Excel Table) does work

    Sheets("Data").Range("A2").ListObject.Refresh

Running this may possibly tell you something (but I suspect not)

Sub ListConnections()
    Dim a As Long, aList As String, c As Long
    With ActiveWorkbook
        c = .Connections.Count
        For a = 1 To c
         aList = aList & vbCr & .Connections(a)
        Next a
    End With
    MsgBox aList, , c & "Connection(s)"
End Sub

Given the age of your workbook, coupled with the issues you are having, my advice would be to recreate your workbook from a totally CLEAN start and all should be well

 

[MR Campbell]

This does not work for me either - if it worked for you in the past, then Microsoft have changed something

    Sheets("Data").Range("A2").QueryTable.Refresh BackgroundQuery:=False

but the same syntax using ListObject (ie an Excel Table) does work

    Sheets("Data").Range("A2").ListObject.Refresh

I think that Microsoft changing something is at the heart of my problems i.e. code that once worked now doesn't.
My file has always been an .xlsm file.

Many thanks for your generous support with this. I greatly appreciate it.