Excel VBA:从MySQL数据库导入数据而不附加引用或外接程序。

Question

我希望将数据集从MySQL数据库导入到Excel中，而不使用其他引用或外接程序(这样同事就可以在安装过程中不更改任何内容)。到目前为止，我找到的解决方案都使用额外的引用或默认情况下没有活动的东西。

数据库包含越来越多的数据集，它们都是以标准化的方式命名的，用户应该能够选择要导入的数据集。我是一个VBA半noob，并设法使一个特定的数据集(使用宏编辑器)的基本想法，但我无法让它使用可变数据集名称。

到目前为止工作的是以下内容(本例中的数据集名称是"scada_pl_oxidation_study_14102020"，数据库目前是本地的，但将来将改为远程)

'Insert table from MySQL database
       Application.CutCopyMode = False
       Sheets("Raw Data").Select
       Range("A1").Select
    ActiveWorkbook.Queries.Add Name:= _
        "cndatabase scada_pl_oxidation_study_14102020", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = MySQL.Database(""localhost"", ""cndatabase"", [ReturnSingleDatabase=true])," & Chr(13) & "" & Chr(10) & "    cndatabase_scada_pl_oxidation_study_14102020 = Source{[Schema=""cndatabase"",Item=""scada_pl_oxidation_study_14102020""]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    cndatabase_scada_pl_oxidation_study_14102020"
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""cndatabase scada_pl_oxidation_study_14102020"";Extended Pr" _
        , "operties="""""), Destination:=Range("'Raw Data'!$A$3")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT * FROM [cndatabase scada_pl_oxidation_study_14102020]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "cndatabase_scada_pl_oxidation_study_14102020"
        .Refresh BackgroundQuery:=False
    End With

我最初的想法是使用Userform只键入要导入的数据集的名称，但是将"scada_pl_oxidation_study_14102020“替换为基于Userform输入的变量似乎不起作用。用户可以从数据库中包含的数据集列表中进行选择的解决方案将是首选的，但这远远超出了我的能力。有人能帮我吗？

Answer 1

“用户可以从数据库中包含的数据集列表中进行选择的解决方案是首选的”

使用UserForm和ListBox创建一个CommandButton，并将这些代码放在表单上。当表单初始化时，它会用数据库中以单词"scada“开头的所有表填充列表框。选择一个表并按下按钮，它应该用所选表中的记录填充“原始数据”表。您必须将DSNless连接详细信息修改为您所拥有的驱动程序。

Option Explicit

Private Sub UserForm_Initialize()
    Const FILTER = "scada*"

    Dim conn, cmd, rs
    Set conn = DbConnect()

    Set cmd = CreateObject("ADODB.Command")
    With cmd
        .CommandType = 1 'adCmdText
        .CommandText = "SHOW TABLES"
        .ActiveConnection = conn
    End With

    ' populate list box
    UserForm1.ListBox1.Clear
    Set rs = CreateObject("ADODB.Recordset")
    Set rs = cmd.Execute
    rs.MoveFirst
    While Not rs.EOF
        If LCase(rs(0)) Like LCase(FILTER) Then
            UserForm1.ListBox1.AddItem rs(0)
        End If
        rs.MoveNext
    Wend
    conn.Close

End Sub

' select table
Private Sub CommandButton1_Click()

    Dim ws As Worksheet
    Dim i As Long, sTable As String
    Dim conn, cmd, rs
    
    ' select table
    For i = 0 To ListBox1.ListCount
        If ListBox1.Selected(i) Then sTable = ListBox1.List(i)
    Next

    If Len(sTable) = 0 Then Exit Sub
    
    ' connect to db
    Set conn = DbConnect()
    Set cmd = CreateObject("ADODB.Command")
    With cmd
        .CommandType = 1 'adCmdText
        .CommandText = "SELECT * FROM " & sTable
        .ActiveConnection = conn
    End With

    ' run query
    Set rs = CreateObject("ADODB.Recordset")
    Set rs = cmd.Execute
    
    ' dump data to sheet
    Set ws = ThisWorkbook.Sheets("Raw Data")
    ws.Cells.Clear ' clear sheet
    ws.Range("A3").CopyFromRecordset rs
    conn.Close

End Sub

Function DbConnect() As Object
   
   Const SERVER = "127.0.0.1" 'localhost
   Const DB = "cndatabase"
   Const UID = "****" ' user I suggest with SELECT only privilidges
   Const PWD = "****" ' password

   Set DbConnect = CreateObject("ADODB.Connection")
   DbConnect.ConnectionString = "Driver={MySQL ODBC 8.0 ANSI Driver};" & _
    "UID=" & UID & "; PWD=" & PWD & ";" & _
    "SERVER=" & SERVER & ";" & _
    "DATABASE=" & DB & ";" & _
    "PORT=3306;" & _
    "Initial Catalog=" & DB

   DbConnect.Open
  
End Function