Extract, Get Data from MySQL PHP
This VBA Macro code extracts data from a MySQL Database and writes the data to an excel file. Many use this for large quantities of data control for PHP web development.
ExplanationThe approach is straight forward. Download the file fill in the data regarding set up of MySQL connection. Push the button and all data from the selected table will be displayed. This program is good to use if you have a MySQL database from a website for example and you need to perform a massive amount of data update. Simply automate the process and get the data you need. To be able to run the VBA Macro code make sure you have enabled the Microsoft ActiveX Data Objects X.X Library. Also a ODBC connector check mysql.com needs to be installed on your computer.
CodeSub ExtractDataFromMySQL()
Dim Password As String
Dim SQLStr As String
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim User_ID As String
Dim Database_Name As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Range("a5:bb60000").ClearContents
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
SQLStr = "SELECT * FROM " & Tabellen
Set Cn = New ADODB.Connection
Cn.Open "Driver={MySQL ODBC 3.51 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
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
Download excel file! extract-data-from-mysql.xls |
