How to connect MySQL by VBA in Excel?

To connect to a MySQL database using VBA in Excel, you can use the ADO (ActiveX Data Objects) library. ADO provides a set of objects and methods that allow you to interact with databases. Here’s an example of how you can establish a connection to MySQL using VBA:

Step-by-step guide

1.- Set up the MySQL Connector/ODBC driver Before you can connect to MySQL from Excel, you need to install the MySQL Connector/ODBC driver. You can download it from the official MySQL website and follow the installation instructions. Download the driver here: https://dev.mysql.com/downloads/connector/odbc/ 2.- Enable the Microsoft ActiveX Data Objects Library Open the Excel workbook in which you want to connect to MySQL.

Add a reference to the Microsoft ActiveX Data Objects Library:
Press Alt+F11 to open the VBA editor.
Go to Tools -> References.

3.- In the VBA editor, insert a new module or open an existing one. Write the following code to establish a connection to MySQL.

VBA Code

VBA

Sub ConnectToMySQL()
    Dim conn As Object
    Dim serverName As String
    Dim dbName As String
    Dim userName As String
    Dim password As String
    Dim connectionString As String
    
    ' Set the connection parameters
    serverName = "localhost" ' Replace with your server name or IP address
    dbName = "your_database" ' Replace with your database name
    userName = "your_username" ' Replace with your MySQL username
    password = "your_password" ' Replace with your MySQL password
    
    ' Build the connection string
    connectionString = "Driver={MySQL ODBC 8.0.33 Driver};" & _
                       "Server=" & serverName & ";" & _
                       "Database=" & dbName & ";" & _
                       "User=" & userName & ";" & _
                       "Password=" & password & ";"
    
    ' Create a new connection object
    Set conn = CreateObject("ADODB.Connection")
    
    ' Open the connection
    conn.Open connectionString
    
    ' Check if the connection is successful
    If conn.State = 1 Then
        MsgBox "Connected to MySQL successfully!"
    Else
        MsgBox "Failed to connect to MySQL."
    End If
    
    ' Close the connection
    conn.Close
    
    ' Clean up memory
    Set conn = Nothing
    
End Sub

In the code above, you need to replace the placeholder values with your actual MySQL server name or IP address, database name, username, and password. The connection string is built using the MySQL ODBC driver and the provided connection parameters. The code creates a new connection object using CreateObject(“ADODB.Connection”) and opens the connection using the Open method. After opening the connection, it checks the connection state and displays a message box indicating whether the connection was successful or not. Finally, the connection is closed and the memory is cleaned up.

facebooklinkedin