Power Query Update - Mac

[Herbiec09]

Goodmorning All

I have a small dilemma and was looking for ideas on a work around.

There is an Excel report that I have built on our shared drive. This report is based on a table created from a power query link into our company database.

I have inserted a macro so that when the workbook is opened the connection to the database is refreshed so that the latest data is pulled in.

The dilemma is that I use a windows based machine and this report is for my boss who is on a mac. Apparently Mac's do not support power query so this kicks of an error as "the connection is not supported"

Any ideas on how I can work round this and still achieve an automated solution?

Thank you

Herbz

 

[RoryA]

Have you tried using MSQuery instead of PQ? That should be supported on the Mac.

 

[Herbiec09]

Thanks for the suggestion

I have never used MS Query, I use Microsoft Office 365 how do I use it from Excel 2016?

Thanks

 

[Herbiec09]

Not to worry RoryA,

I have figured it out, many thanks.

VBA code that I was using to update the links on opening when I was using PQ was:

Private Sub Workbook_Open()

' Macro to update my Power Query script(s)

Dim lTest As Long, cn As WorkbookConnection
On Error Resume Next
For Each cn In ThisWorkbook.Connections
lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
If Err.Number <> 0 Then
Err.Clear
Exit For
End If
If lTest > 0 Then cn.Refresh
Next cn

End Sub

Would you be able to assist with an equivalent when using MS Query.

many many thanks

 

[RoryA]

You should be able to just set the table to refresh when the workbook is opened in the table properties?