How can VBA connect to MySQL database in Excel?

by

This piece of vba worked for me:

Sub connect()
    Dim Password As String
    Dim SQLStr As String
    'OMIT Dim Cn statement
    Dim Server_Name As String
    Dim User_ID As String
    Dim Database_Name As String
    'OMIT Dim rs statement

    Set rs = CreateObject("ADODB.Recordset") 'EBGen-Daily
    Server_Name = Range("b2").Value
    Database_name = Range("b3").Value ' Name of database
    User_ID = Range("b4").Value 'id user or username
    Password = Range("b5").Value 'Password

    SQLStr = "SELECT * FROM ComputingNotesTable"

    Set Cn = CreateObject("ADODB.Connection") 'NEW STATEMENT
    Cn.Open "Driver={MySQL ODBC 5.2.2 Driver};Server=" & _ 
            Server_Name & ";Database=" & Database_Name & _
            ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    rs.Open SQLStr, Cn, adOpenStatic

    Dim myArray()

    myArray = rs.GetRows()

    kolumner = UBound(myArray, 1)
    rader = UBound(myArray, 2)

    For K = 0 To kolumner ' Using For loop data are displayed
        Range("a5").Offset(0, K).Value = rs.Fields(K).Name
        For R = 0 To rader
           Range("A5").Offset(R + 1, K).Value = myArray(K, R)
        Next
    Next

    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

More Related Contents:

  1. How to reduce the size of google spreadsheet using SQL?
  2. How to import an excel file in to a MySQL database
  3. Exporting results of a Mysql query to excel?
  4. How to save MySQL query output to excel or .txt file? [duplicate]
  5. MySQL export into outfile : CSV escaping chars
  6. i have an error which i dont understand. can you help me? [closed]
  7. Loop through files in a folder using VBA?
  8. How do I find the last column with data?
  9. How to group by week in MySQL?
  10. Can I use a function for a default value in MySql?
  11. Mysql order by specific ID values
  12. How do I check if a column is empty or null in MySQL?
  13. MySQL versus PDO [duplicate]
  14. Row number per group in mysql
  15. Resetting ROOT password in MySQL 5.6
  16. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  17. Field ‘id’ doesn’t have a default value?
  18. How to break long string to multiple lines
  19. Can Advanced Filter criteria be in the VBA rather than a range?
  20. Escaping the colon character ‘:’ in JPA queries
  21. How to export / dump a MySql table into a text file including the field names (aka headers or column names)
  22. Excel Useform: How to hide application but have icon in the taskbar
  23. AWS Aurora MySQL serverless: how to connect from MySQL Workbench
  24. Create Chart from Array data and not range
  25. How to store data with dynamic number of attributes in a database
  26. What happens when I exhaust a bigint generated key? How to handle it?
  27. MySQL Auto increment primary key increases by 10
  28. How do I activate a specific workbook and a specific sheet?
  29. Delete all data rows from an Excel table (apart from the first)
  30. ReDim Preserve “Subscript Out of Range”

Leave a Comment