Excel VBA: Import data from MySQL database without additional references or add-ins

Question

I would like to import a dataset from a MySQL database into Excel not using additional references or add-ins (so colleagues can use it without changing anything in their setup). The solutions I have found so far all use additional references or things that are not active by default.

The database contains a growing number of datasets all named in a standardised way and the user should be able to choose which dataset to import. I am a VBA-semi-noob and have managed to get the basic idea working for one specific dataset (using macro editor) , but I am unable to get it working with variable dataset names.

What works so far is the following (dataset name in this example is "scada_pl_oxidation_study_14102020", database is currently local but will change to remote in future)

'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

My initial idea was to use a Userform to just type the name of the dataset to be imported, but replacing the "scada_pl_oxidation_study_14102020" with a variable based on the Userform input does not seem to work. A solution where the user can choose from a list of datasets contained in the database would be preferred, but that is way beyond my capabilities. Can anybody help me with this?

How exactly did you use the variable you reference? Did it fall inside the quotes, or was it concatenated with `&`? — BigBen, Apr 08 '21 at 01:36
I tried to use it concatenated with `&` as inside the quotes the database is also referenced, so i.e. `ActiveWorkbook.Queries.Add Name:= _ "cndatabase "&variable` — Tom7omtom, Apr 08 '21 at 01:42
Always helps to `Debug.Print` the actual `String` and examine the output in the Immediate Window to see if it is as expected. — BigBen, Apr 08 '21 at 01:44
the output is exactly as expected, a string containing only the characters specified, to stick with the above dataset the output is "scada_pl_oxidation_study_14102020" I can also post the not working code I have so far, if that may help? — Tom7omtom, Apr 08 '21 at 02:04
I also meant the output of the entire string after `Formula:= _` — BigBen, Apr 08 '21 at 02:06
makes sense, sorry about that, the output after `Formula:= _` is (the variable name is UserInput) ` `let Source = MySQL.Database("localhost", "cndatabase", [ReturnSingleDatabase=true]), cndatabase_&UserInput = Source{[Schema="cndatabase",Item="UserInput"]}[Data] in cndatabase_scada_pl_oxidation_study_14102020` ` It seems the variable is not always correctly used? But if I change/remove quotes from the code I get error messages — Tom7omtom, Apr 08 '21 at 02:19
If the string you're trying to build up contains quotes, then you need to double them up as demonstrated [here](https://stackoverflow.com/questions/9024724/how-do-i-put-double-quotes-in-a-string-in-vba). Another option is to just use `Replace`, replacing some placeholder text with the `UserInput` variable (might save some typing). — BigBen, Apr 08 '21 at 02:21
Thank you!!! that worked like a charm, it gets a bit confusing doubling up a lot of quotes but with 'debug.print' I could work my way through it, thanks a lot! — Tom7omtom, Apr 08 '21 at 06:33

score 0 · Accepted Answer · answered Apr 08 '21 at 17:04

"A solution where the user can choose from a list of datasets contained in the database would be preferred"

Create a UserForm with a ListBox and CommandButton and put this code on the form. When the form initializes it populates the list box with all the tables in the database that start with the word "scada". Select a table and press the button it should populate the "Raw Data" sheet with records from the selected table. You will have to amend the DSNless connection details to the driver you have.

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

Excel VBA: Import data from MySQL database without additional references or add-ins

1 Answers1