Extract, Get Data from MySQL PHP




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

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.

Comment made by: Francis , 2010-12-16 16:40:59


This VBA code displays the MySQL database tables nicely in Excel when it works. However I had to make a change to the code. The Dim cn As.. (connection) and Dim rs As...(recordset) are not recognised nor compiled by my version of VBA. Instead I had to create the ADODB connection and ADODB recordset ActiveX objects as follows: Sub ExtractDataFromMySQL() Dim Password As String Dim SQLStr As String 'OMIT Dim Cn statement Dim Server_Name As String Dim User_ID As String Dim Database_Name As String 'OMIT Dim rs statement Set rs = CreateObject("ADODB.Recordset") 'NEW STATEMENT 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 = CreateObject("ADODB.Connection") 'NEW STATEMENT ........[rest of code as in the original]. When this change was made the code worked fine.

Comment made by: Richard Waggett , 2012-05-08 11:55:17


sdfdsfdf

Comment made by: ranjit , 2012-06-19 12:14:30


according to this code , In my excel sheet all the data are displayin but what i need is only a certain set of data to be displayed !!!

Comment made by: vel , 2012-06-20 06:26:37


thanku so much

Comment made by: ranjit kumar , 2012-11-05 12:22:30


Hi There, I am trying to run extract-data-from-mysql.xlsm file on excel 2010, it's not working, can you pls help Regards, Sajjad mrsajjadAyahoo.com

Comment made by: Sajjad , 2014-02-18 11:42:55


Nice site! Thanks!

Comment made by: Alibaba , 2015-03-27 23:24:46


Does not display Row headers

Comment made by: Sandy , 2015-09-13 17:27:48


proba

Comment made by: edo , 2017-01-01 11:27:38


Write Comment:

Comment:

two + 3 minus 1=


Your name: