...或从QueryTables中提取记录。

最佳答案

试试这个srting:


SQLDatabase_VBA.bas
使用Excel中的VBA从ADODB或系统连接SQL数据库



  sconnect =“ Provider = MSDASQL.1; DSN =您的ODBC连接名称;”&_
              “ UID =您的用户; PWD =您的密码; DBQ =您的数据库”&DBPath&“; HDR =是';”


IBM / AS400的连接字符串:(没有ADODB,也许可以在Mac上运行,但只能在IBM服务器上运行)


  sconnect =“ PROVIDER = IBMDA400;数据源=服务器名;”&_
              “默认集合=可选;用户标识=用户名;密码= 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

关于mysql - Mac 2016的Excel中是否有ADODB.connection ADODB.recordset的等效项?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/53014926/

10-13 03:19