How to display data in Excel spreadsheet from MySQL. Part 2

By Sergey Skudaev

+++++

This page continues tutorial about displaying data from MySQL database in MicroSoft Excel spreadsheet using ADODB. Read a Part 1 if you missed it.

<<Part 1<<

Records are inserted in the credit_card table. Note, that in Windows database, table, and field names are not case sensitive. In Unix or Linux they are case sensitive.

Open MS Excel spread sheet. Click Tools, macros, VB editor. VB editor opens.

Click Tools, references and select "Microsoft ActiveX Data Objects 2.8 Library"

Visual Basic: references tab

Click OK

Open code window and type VB code in General declarations:

Public conn As ADODB.Connection

Public rsCard As ADODB.Recordset

Excel MySQL:ADODB.Recordset

Select General drop down list and select Worksheet object. Select Sheet1 in VBA project.

Excel MySQL:Worksheet object

Select Activate event from the right drop down list. Private Sub Worksheet_Activate () subroutine displays.

Visual Basic: Worksheet_Activate routine

Type VB code that will be executed on Worksheet Activate event. Worksheet activate event occurs when you select a worksheet and it displays on the screen.

   Private Sub Worksheet_Activate ()

   Dim sh1 As Sheet1

   Dim j As Integer

   Dim SQL As String

   Dim c as Integer

   On Error GoTo error_handler

   conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost; DATABASE=credit;UID=root;PWD=; OPTION=3"

If your web server and MS Excel located on different machines, type IP address of
the server machine in place of localhost. For example:

 'conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};
'SERVER=192.168.1.101; DATABASE=credit;UID=root;PWD=secret; OPTION=3"

   conn.open

   Set sh1 = Excel.Worksheets(1)

    Set conn = New ADODB.Connection

   Set rsCard = New ADODB.Recordset

    j = 0

    'clear 50 rows of spreadsheet

    While (j < 51)

      Sh1.Cells(j, 1).Value = ""

      Sh1.Cells(j, 2).Value = ""

      Sh1.Cells(j, 3).Value = ""

      Sh1.Cells(j, 4).Value = ""

      Sh1.Cells(j, 5).Value = ""

       Sh1.Cells(j, 6).Value = ""

      Sh1.Cells(j, 7).Value = ""

       Sh1.Cells(j, 8).Value = ""

       Sh1.Cells(j, 9).Value = ""

       Sh1.Cells(j, 10).Value = ""

       j = j + 1

     Wend

      'create column Headers:

       Sh1.Cells(1, 1).Value = "Card Name"

      Sh1.Cells(1, 2).Value = "Type"

       Sh1.Cells(1, 3).Value = "Expired"

      Sh1.Cells(1, 4).Value = "Number"

       Sh1.Cells(1, 5).Value = "Credit"

      Sh1.Cells(1, 6).Value = "Phone"

       Sh1.Cells(1, 7).Value = "Address"

      Sh1.Cells(1, 8).Value = "City"

       Sh1.Cells(1, 9).Value = "State"

       Sh1.Cells(1, 10).Value = "Zip"

      SQL="select * from credit_card"

      c=0

       rsCard.CursorLocation = adUseServer

       rsCard.Open SQL, conn

       rsCard.MoveFirst

      Do Until rsCard.BOF Or rsCard.EOF

       Sh1.Cells(c, 1).Value = rsCard. Fields(0)

       Sh1.Cells(c, 2).Value = rsCard. Fields(1)

       Sh1.Cells(c, 3).Value = rsCard. Fields(2)

       Sh1.Cells(c, 4).Value = rsCard. Fields(3)

       Sh1.Cells(c, 5).Value = rsCard. Fields(4)

       Sh1.Cells(c, 6).Value = rsCard. Fields(5)

       Sh1.Cells(c, 7).Value = rsCard. Fields(6)

       Sh1.Cells(c, 8).Value = rsCard. Fields(7)

       Sh1.Cells(c, 9).Value = rsCard. Fields(8)

       Sh1.Cells(c, 10).Value = rsCard. Fields(9)

       rsCard.MoveNext

       c = c + 1

      If rsCard.EOF Then

       rsCard.MoveFirst

     Exit Sub

    End If

      Loop

    error_handler:

      MsgBox "Error:" & Err.Description, vbCritical, "Credit Card"

   End Sub

Save changes. Open spreadsheet and you will see a table with data from MySQL. We are done!

Did you find information useful?
Send to your friend a link to this page

If you like this page click +1 button.

Please rate the tutorial

1 2 3 4 5 6 7 8 9 10



How to Build Your Own Web Site from Scratch [Kindle Edition] $2.99

Earn Money on Internet as an Affiliate [Kindle Edition] $0.99

Comments
 
Register to add comments ( 1000 char ) for excel_mysql.php.