1

... or pull records from QueryTables.

M Garcia
  • 331
  • 3
  • 14
  • 1
    ADODB is not available for Mac. Most folks use QueryTables which aren't quite as nice, but they seem to do the job. – JNevill Oct 26 '18 at 19:10
  • You could theoretically write a [.NET Core](https://stackoverflow.com/q/38510740/1188513) library wrapping ADO.NET, and then make that library expose a [COM-visible](https://stackoverflow.com/q/44861236/1188513) API and then conceivably be able to reference it from VBA, assuming Mac-VBA lets you reference such type libraries, and assuming everything above works... but this is basically uncharted territory. Worth trying though. Disclaimer: I don't have a Mac, and build .NET on Windows... but .NET Core does run on a Mac, so.. – Mathieu Guindon Oct 26 '18 at 19:47
  • https://stackoverflow.com/questions/9707256/is-there-a-way-to-get-adodb-to-work-with-excel-for-mac-2011 – Tim Williams Oct 26 '18 at 21:54

1 Answers1

0

try this srting:

  1. SQLDatabase_VBA.bas connect SQL Database from ADODB or system with VBA in Excel

sconnect = "Provider=MSDASQL.1;DSN=your ODBC connection name; " & _ "UID=your user;PWD=your password;DBQ=your database" & DBPath & ";HDR=Yes';"

connection String for IBM/AS400: (without ADODB, maybe work on mac but only eith IBM server)

sconnect = "PROVIDER=IBMDA400;Data Source=servername; " & _ "DEFAULT COLLECTION=optional;USER ID=Username ;PASSWORD=KENNWORT"

SQLDatabase_VBA.bas :

Sub SQLDatabase_VBA()



On Error Resume Next



'Step 1: Create the Connection String with Provider and Data Source options

Public sSQLQry As String

Public ReturnArray



Public Conn As New ADODB.Connection

Public mrs As New ADODB.Recordset

Public DBPath As String, sconnect As String





'Step 2: Create the Connection String with Provider and Data Source options

ActiveSheet.Activate



DBPath = ThisWorkbook.FullName 'Refering the sameworkbook as Data Source



'You can provide the full path of your external file as shown below

'DBPath ="C:\InputData.xlsx"



sconnect = "Provider=MSDASQL.1;DSN=Connect_fromODBC;UID=your user name;PWD=your password;DBQ=database name" & DBPath & ";HDR=Yes';"

'If any issue with MSDASQL Provider, Try the Microsoft.Jet.OLEDB:

'sconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBPath _

    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"



'Step 3: set connection timeout Open the Connection to data source

Conn.ConnectionTimeout = 30

Conn.Open sconnect



'Step 4: Create SQL Command String MRFIRM, MRIDEN, MRSART,MRSRN,MRSRRF,MRDTB,MRUSER, MRSRNA as Serien_NR_Zugriff

 sSQLSting = "SELECT * From your database " & _

            " WHERE ------ " & _

            " Group by ----- "







'Step 5: Get the records by Opening this Query with in the Connected data source

 mrs.Open sSQLSting, Conn



 'Step 6: Copy the reords into our worksheet

 'Import Headers

    For i = 0 To mrs.Fields.Count - 1

        ActiveSheet.Range("B15").Offset(0, i) = mrs.Fields(i).Name

    Next i



'Import data to destination cell

ActiveSheet.Range("B15").Offset(1, 0).CopyFromRecordset mrs



 'Step 7: Close the Record Set and Connection

  'Close Recordset

  mrs.Close



  'Close Connection

  Conn.Close

  Set mrs = Nothing

  Set Conn = Nothing



End Sub
Suliman Farzat
  • 1,197
  • 11
  • 12