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"

Click OK

Open code window and type VB code in General declarations:

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

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

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!