Excel QueryTable does not work on Excel for Mac in Version 16.9.
Excel QueryTable does not work on Excel for Mac in Version 16.9.
See example from Microsoft here: https://msdn.microsoft.com/en-us/library/office/aa203721(v=office.11).aspx
Section "Using a Static Web Query" describes a way to get data from a webserver.
If you Change the used link from http://quote.money.cnn.com/quote/quote?symbols=msft
to https://go.microsoft.com/fwlink/?linkid=859725
the example loads a web table and displays it in a Excel worksheet.
This example does not work on Mac with Excel Version 16.9. With Version 15.X on Excel the query executes successfully like on Windows.
Here is the Code snippet for reproducing the bug:
Sub RunWebQuery()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;<a rel="nofollow noreferrer" href="https://go.microsoft.com/fwlink/?linkid=859725"">https://go.microsoft.com/fwlink/?linkid=859725"</a>, _
Destination:=Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub
If you Change the Line
.Refresh BackgroundQuery:=False
to
.Refresh BackgroundQuery:=True
the behavior is different. It seems as if synchron web queries are not implemented correctly.

13 comments
Comments are closed-
Rick Oosterholt commented
Version Mac Excel 16.11 (180311) indeed fixes this issue
-
Jordan Owens commented
Just an update...
Excel 16.11.0 was released today and included the fix for this issue.
-
Jordan Owens commented
I just tested insider fast build 16.12 (Build 180305) and this bug appears to be fixed. Hopefully it makes its way into the next stable build.
-
Dirkrlu commented
I escalated the issue to technical support. The support engineer managed to reproduce the bug. So pls reopen the ticket.
-
Jordan Owens commented
As the OP noted .Refresh BackgroundQuery:=True does work, but
.Refresh BackgroundQuery:=False
does not.
-
Rick Oosterholt commented
Exactly. It is an issue as reported. Please reopen
-
Jordan Owens commented
Steve, it should work, but it doesn't. The point was that this is a bug that needs fixed.
-
It should work if you just put the refresh in the final step.
Sub RunWebQuery()
Dim myConn As QueryTable
Set myConn = ActiveSheet.QueryTables.Add(Connection:="URL;https://go.microsoft.com/fwlink/?linkid=859725", Destination:=Range("A1"))With myConn
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.SaveData = True
.Refresh BackgroundQuery:=True
End WithEnd Sub
Thanks, Steve K [MS Excel]
-
Jonathan Hardis commented
This bug was also reported at: https://excel.uservoice.com/forums/304933-excel-for-mac/suggestions/33023611-excel-16-9-macintosh-update-killed-my-vba-macro
-
Jordan Owens commented
I've run into the same issue as of version 16.10. I really hope this is fixed soon!
-
Rick Oosterholt commented
Thanks Dirkrlu. do you have an example on how to do that?
-
Dirkrlu commented
The only workaround I found is to use the background query set to true.
You have to attach the events raised by the QueryTable object -
Rick Oosterholt commented
I'm facing the same issue in the latest version of Mac Excel. Does anyone have a solution yet?