Updating existing data in a MySQL database is easily done by using
this VBA Macro code. Many are using the methodology when working with
websites developed in PHP and MySQL.
' For detailed description visit http://www.vbaexcel.eu/
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
User_ID = Range("h1").Value 'i d user or username
Password = Range("e3").Value ' Password
Server_Name = Range("e4").Value ' IP number or servername
Database_Name = Range("e1").Value ' Name of database
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 = "UPDATE " & Tabellen & " SET " & TextStrang & "WHERE " & Cells(5, 1) & " = '" & Cells(6 + rad, 1) & "'"
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
Explanation
This VBA Macro code is optimized for updating an exsisting MySQL database. You need a connector, ODBC for latest version mysql.com download the excel file ate the bottom of this page or copy and paste the code directly. In the file there are some data that needs to be added according to your set up of the MySQL database and connection. Fill in the data and add the required fileds that you are going to update. Push the buttom and you will be updating existing data in your MySQL database. MySQL is one of the most effective databases and the best thing is that it is used free of charge.Code
Sub UpdateMySQLDatabasePHP()' For detailed description visit http://www.vbaexcel.eu/
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
User_ID = Range("h1").Value 'i d user or username
Password = Range("e3").Value ' Password
Server_Name = Range("e4").Value ' IP number or servername
Database_Name = Range("e1").Value ' Name of database
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 = "UPDATE " & Tabellen & " SET " & TextStrang & "WHERE " & Cells(5, 1) & " = '" & Cells(6 + rad, 1) & "'"
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
No comments:
Post a Comment