1

I have write a macro in Excel 2016 on a windows pc via oledb connection. But it doesn't work in my Mac. How can I run this? Normally, in windows, you choose tools > references and check oledb connection but there isn't a tools option in Excel Mac. I don't know code is important here but I'll share it anyway.

Dim sorgu1 As String
Dim con As Object, rs As Object
    Set con = CreateObject("adodb.connection")
    Set rs = CreateObject("adodb.recordset")
    con.Open "provider=microsoft.ace.oledb.12.0;data source=" & _
    ThisWorkbook.FullName & ";extended properties=""Excel 12.0;hdr=yes"""

    makine = Sheets("Dashboard").Cells(2, 11).Value

    sorgu1 = "select [Resource Id], [Order No], Sum(([Bitim Zamani]-[Basl Zamani])*1440)" 'as Plansiz, Sum(([Bitim Zamani]-[Basl Zamani])*1440)as Planli, Sum([Qty]) as Uretim"
    sorgu1 = sorgu1 + " from [Data$] where [Resource Id] = " + "'" + makine + "'"
    sorgu1 = sorgu1 + "GROUP BY [Resource Id], [Order No], [Duru Kodu]"

    Set rs = con.Execute(sorgu1)
    Sheets("Dashboard").Range("B65536").End(3).Offset(1, 0).CopyFromRecordset rs
    Set rs = Nothing
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
ali srn
  • 573
  • 5
  • 24

1 Answers1

5

Your code uses late-binding and doesn't require a "Reference" so long as the library is available on the machine. In your case on Mac OS, the library doesn't exist and is, as far as I know, explicitly not allowed.

From Microsoft (emphasis added):

To import data from a database, such as Microsoft SQL Server, you must have an ODBC driver that is compatible with Microsoft Query installed on your computer. Compatible ODBC drivers are available from third-party vendors. For more information, see ODBC drivers that are compatible with Excel for Mac. For more information about installing ODBC drivers, see Microsoft Query Help.

Importantly, for your question, OLE DB is explicitly not supported on Mac OS if using Excel 2011 for Mac.

Excel 2011 for Mac cannot import data from OLE DB or OLAP data sources.

So, to answer your question there is no way to get your SQL query to work on Mac, you need to use something else entirely.

HOWEVER, as noted, if you're using Excel 2016 for Mac, you can do it:

This version of Excel does provide an ODBC driver for connecting to SQL Server Databases. On the Data tab, click New Database Query > SQL Server ODBC. Then use the dialog boxes to import the data.

If you are connecting to other ODBC data sources (for example, FileMaker Pro), then you'll need to install the ODBC driver for the data source on your Mac. Drivers that are compatible with Excel for Mac are available from these companies:

Alternatively, this answer to a similar question looks promising:

https://stackoverflow.com/a/25126885/1467082

Community
  • 1
  • 1
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • This answer is not a helpful answer, SQL Server queries are available natively on Office 2016 for Mac, which is the version the OP was referring to. This just says you can't do it in 2011. – TravisPUK Oct 04 '17 at 13:44
  • @TravisPUK OP indicates Excel 2016 on PC, no indication given as to what version of Excel for Mac is used. In any case, I've revised the answer (and the linked MSDN article *does* explain that it's available on 2016 Excel for Mac, also). – David Zemens Oct 04 '17 at 13:50
  • Not Correct. OP specifically says "I have write a macro in Excel 2016 on a windows pc via oledb connection. But it doesn't work in my Mac." – TravisPUK Oct 04 '17 at 13:51
  • 1
    "Excel 2016 on a windows PC" seems to suggest the version of Excel is 2016 Windows. He further states "it doesn't work on my Mac". No indication given what version. In any case, the revision to my answer should now qualify it as "helpful"... – David Zemens Oct 04 '17 at 13:52
  • 1
    IOW, the way I've interpreted the question is: "I recorded this macro in Excel 2016 Win, but when I try to port it over to my Mac it doesn't work". Unfortunately, OP didn't indicate which version of Excel for Mac. You're welcome to infer that he's using 2016 on both, but there's no concrete evidence for that, either in the question text, or in the tags. – David Zemens Oct 04 '17 at 13:55
  • 1
    I would agree with that David. – TravisPUK Oct 04 '17 at 13:57