423,103 Members | 1,428 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,103 IT Pros & Developers. It's quick & easy.

Compile Error in Mac

P: 4
Hi all,
I am brand new to VBA/T-SQL so apologies if anything is unclear or it is a stupid question.
I have the following simple Macro to refresh data in a query table that runs fine on Excel 2016 on my PC:

Expand|Select|Wrap|Line Numbers
  1. Sub RefreshVIPData()
  2. '
  3. ' RefreshVIPData Macro
  4.  
  5. '
  6. ThisWorkbook.Queries.FastCombine = True
  7. Application.DisplayAlerts = False
  8. '
  9.     Range("Query1[[#Headers],[AnimRecNo]]").Select
  10.     Application.CutCopyMode = False
  11.     Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
  12.     Application.DisplayAlerts = True
  13. End Sub
  14.  
When I try to run the Macro via a form control button on a colleague's Mac I get the following:
'Compile error:
method or data member not found'
and the debugger highlights the word 'Queries' in the FastCombine command.

Is this a problem with opening the file on a Mac? Or some other issue within the VBA itself?
Thanks in advance for any guidance.
2 Weeks Ago #1
Share this Question
Share on Google+
6 Replies


Nauticalgent
P: 64
I don't really know much about running VBA on a MAC, in fact I didn't think it was possible with some sort of shell program.

What I do kind curious is how that particular line is the one that showed on error because it is commented out...
2 Weeks Ago #2

P: 4
Hi Nauticalgent, thanks for your reply.
Apologies I think the ' was just bad copy & pasting!! I have edited the original post to reflect this (thanks for noticing).
2 Weeks Ago #3

Nauticalgent
P: 64
I use Office 2013 at work and I brought up a blank worksheet and invoked the VBE to test. Intellisense did NOT offer Queries as a available Method after I typed in ThisWorkBook.

I did a quick google search and it appears that the Queries Method for ThisWorkBook is only available in Excel 2016, which would explain things.

Here is the link:
https://blogs.technet.microsoft.com/...in-excel-2016/

As a test, on a new line, type in "ThisWorkBook." and see what methods intellisense offers. If "Queries" is not one of them, then there is the problem. What Office for Mac version are you using?
2 Weeks Ago #4

P: 4
Yes - 'Queries' did appear from intellisense when running your test in Excel 2016.
The Mac is also using Office 365, Excel 2016.
I have not tried to set up any kind of ODBC between the two - could that be the problem?
Thanks.
2 Weeks Ago #5

Nauticalgent
P: 64
Not sure of the ODBC connection. I doubt if that is the issue since the error specifically mentions a Method not found.

It appears you did the test on the PC (which we already know works) and not the MAC. If this is accurate, try it on the Mac. If intellisence offers the Queries Method than I am out of ideas...
2 Weeks Ago #6

P: 4
Oops sorry!
No it does not offer Queries on the Mac so I think you have found the problem - thank you!
This link states that Power Query is not supported on a Mac so perhaps I'm out of luck :(

https://support.office.com/en-us/art...f-f2e1bfa0cb16
2 Weeks Ago #7

Post your reply

Sign in to post your reply or Sign up for a free account.