Write To MySQL database PHP

To be able to write data to a database and in this case a MySQL database is an efficient way of automating tasks that normally is very time consuming. This VBA Macro code writes new data to am existing MySQL database.

Explanation

The VBA Macro code useful for updating MySQL databases for example if you have website that is developed in PHP the standard database to use is MySQL. In order to make the connection between excel and MySQL you need an ODBC connector for the latest driver check out mysql.com. In the attached excel file available at the bottom of this page there are columns in the file where you add field names. Not all field names need to be added just the ones you are going to write to. The first id field always needs to be there. Fill in data regarding, database name, server name, user id, password and name of table. Add the field names and beneath the data you are going to write to the database. Push the button and if you have installed the ODBC driver correctly and set up the MySQL database correctly you will start writing data to your MySQL database. Enjoy!

To be able to run the VBA Macro code make sure you have enabled the Microsoft ActiveX Data Objects X.X Library.

Code

Sub WriteToMySQLDatabase()

' For detailed description visit http://www.vbaexcel.eu/

Dim rs As ADODB.Recordset
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim Password As String
Dim SQLStr As String

Dim User_ID As String

Set rs = New ADODB.Recordset
       
Server_Name = Range("e4").Value             ' IP number or servername
Database_Name = Range("e1").Value         ' Name of database
User_ID = Range("h1").Value                      'id user or username
Password = Range("e3").Value                    'Password
Tabellen = Range("e2").Value                     ' Name of table to write to
       
rad = 0
While Range("a6").Offset(rad, 0).Value <> tom
    TextStrang = tom
    kolumn = 0
    While Range("A5").Offset(0, kolumn).Value <> tom
        If kolumn = 0 Then TextStrang = TextStrang & Cells(5, 1) & " = '" & Cells(6 + rad, 1)
        If kolumn <> 0 Then TextStrang = TextStrang & "', " & Cells(5, 1 + kolumn) & " = '" & Cells(6 + rad, 1 + kolumn)
        kolumn = kolumn + 1
    Wend
    TextStrang = TextStrang & "'"
    SQLStr = "INSERT INTO " & Tabellen & " SET " & TextStrang
    Set Cn = New ADODB.Connection
    Cn.Open "Driver={MySQL ODBC 3.51 Driver};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"
    Cn.Execute SQLStr
    rad = rad + 1
Wend

Set rs = Nothing
Cn.Close
Set Cn = Nothing

End Sub

Download excel file! Write-To-MySQL-Database-PHP.xls