Write To MySQL database PHP




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

Comments

Hi, I downloaded the Write-To-MySQL-Database-PHP.xls , but the buttons in menu sheet are locked. How can I unlock them? How do you associate these buttons to the code? If I create a new button I can only associate to a macro, not on a specific VBA code. Thanks

Comment made by: Paolo , 2011-02-24 15:57:41


The file works fine! Try to activate the sheet.

Comment made by: Uldis , 2011-06-15 08:53:30


Hi thanks for this very useful code. Users may need to update the connection string in the code - I could not get it to work until I replaced your {MySQL ODBC 3.51 Driver}with {MySQL ODBC 5.1 Driver} as I am using the latest 5.1 MySQL driver. All the best.

Comment made by: Frenzoid , 2011-10-21 13:15:42


Hi, this is exactly what I have been looking for! Thank you for sharing this with everyone! However I can't connect to localhost (gives error 10061) Do you know what could be the problem?? I've doublechecked the tablename, db name, user and password, everything is correct! Take care Adam

Comment made by: Adam , 2012-06-19 22:56:18


Thanks so much for this code. It is very usefull. My one problem is the use of apostrophes in Lastnames. Do you know of a way I could overcome this in your code as it currently falls down on coming across one of these. Thank you

Comment made by: Seamus , 2012-09-17 11:13:58


Thank you for this info!

Comment made by: Abraham , 2013-10-10 12:35:18


Thank you for this info!

Comment made by: Abraham , 2013-10-10 12:35:27


Thank you for this info!

Comment made by: Abraham , 2013-10-10 12:35:31


Great site

Comment made by: John , 2013-10-10 12:38:46


Great site

Comment made by: John , 2013-10-10 12:38:49


I keep getting a compile error. User-defined type not defined. Is the ODBC Connector working properly, i have installed the 3.51 as you had it and also updated it to 5.1 as well, neither work for me. can you please assist? I updated the fields to be the same name as the ones i created in my database, that is what i was supposed to do correct? I also checked my setting : NameOfDatabase NameOfTable Password ServerName UserId, and i created in my table the ID that you said always must be there. This data i have in the workbook is all entered correct, so what else can be the problem?

Comment made by: Robert , 2014-04-18 21:46:55


It stops on this line of code everytime. Dim Cn As ADODB.Connection I need to see what might be going wrong. I think this is a great code to use but cant get it to work properly. all my server setting and data to login are accurate as i used the MySQL Workbench software to confirm it was up and running, etc..It logs in just fine. How do i confirm the VBA Macro code is enabled for Microsoft ActiveX Data Objects X.X "what ever" Library? I am able to get the macro to start running so would this even be a part of the problem? Kinda lost of what to try next, can anyone help?

Comment made by: Robert , 2014-04-18 21:56:26


Robert.LocklearAtpscomm.com, Please help if you can, thnx.

Comment made by: Robert , 2014-04-18 21:56:59


Thanks for best vba codes ever!

Comment made by: Uncoder! , 2015-04-02 21:38:21


Thanks for best vba codes ever!

Comment made by: Uncoder! , 2015-04-02 22:06:25


Thanks for best vba codes ever!

Comment made by: Uncoder! , 2015-04-02 22:08:32


Thansk!

Comment made by: ben , 2015-04-02 22:32:43


I have a MySQL table with some fields as dates. The write to database code fails on this table. Does something need to be added to the VBA code to handle dates?

Comment made by: Jim Walsh, jswlashAcwcruises.net , 2015-05-10 23:05:57


Like John above, I keep getting I keep getting a compile error. User-defined type not defined. Is there a fix for this? Using Excel 2007.

Comment made by: Andrew , 2017-02-06 23:39:16


Write Comment:

Comment:

two + 3 minus 1=


Your name: