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.

Explanation

The 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.

Code

Sub 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

Comments (1)

Is there any way that I could get this code to work with PHP?
Right now I'm trying to use PHP to get data from a database and put it into Excel. I can do that part easily enough. Now I want to be able to format cells and put in images and stuff and it seems like that would only be possible if I can somehow execute VBA through PHP. So far this code is the closest thing I've found to anything useful.
I know there are things like PHPExcel and others but I'd rather not have to use all that.

Add your comment

yvComment v.CommentVersion