Link to home
Create AccountLog in
Avatar of stltodaycom
stltodaycom

asked on

Export data to Excel using Visual Basic 2005 Express Edition

The following code only export the last field name in my query (in this example 'State').
How do modify this code to export all of the field names in the query?
Also how do I modify the code to export this data in different columns (and also with column titles)?

================================================================================
Dim objExcel As New Excel.Application
Dim wks As Excel.Worksheet
Dim dbCommand As OleDbCommand
Dim dbDataReader As OleDbDataReader
Dim myConn As OleDbConnection = New OleDbConnection
Dim strTempQuery As String
Dim i As Integer
       
        strTempQuery = "Select LastName, FirstName, City, State From Student"
        Connection.Open()
        dbCommand = New OleDbCommand(strTempQuery, dbConnection)
        dbDataReader = dbCommand.ExecuteReader()

        With objExcel
            .DisplayAlerts = False
            .Visible = True
            .Workbooks.Add()
            wks = .ActiveSheet

            Do While dbDataReader.Read

                For i = 1 To dbDataReader.FieldCount - 1
                    .ActiveCell.Value = dbDataReader(i)
                Next
                .ActiveCell.Offset(1, 0).Select()
            Loop
            End With
===================================================================================

As always, any help is appreciated -
DK
ASKER CERTIFIED SOLUTION
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
If you are just inserting records into an Excel worksheet, you might be able to use OleDb, and not Excel automation.

Bob
Avatar of stltodaycom
stltodaycom

ASKER

I had to tweak webtubbs code a little bit for it to work, but here it is:
======================================================================
Dim objExcel As New Excel.Application
        Dim wks As New Excel.Worksheet
        Dim dbCommand As OleDbCommand
        Dim dbDataReader As OleDbDataReader
        Dim myConn As OleDbConnection = New OleDbConnection
        Dim strTempQuery As String
        Dim i As Integer

        strTempQuery = "Select LastName, FirstName From Student"
        Connection.Open()
        dbCommand = New OleDbCommand(strTempQuery, dbConnection)
        dbDataReader = dbCommand.ExecuteReader()


        With objExcel
            .SheetsInNewWorkbook = 1
            .Visible = True
            .Workbooks.Add().Worksheets(1).Select()

            For i = 0 To dbDataReader.FieldCount - 1
                .Cells(1, i + 1).Value = dbDataReader.GetName(i).ToString
            Next
            Dim r As Integer = 2
            Do While dbDataReader.Read
                For c As Integer = 0 To dbDataReader.FieldCount - 1
                    .Cells(r, c + 1).Value = dbDataReader(c).ToString
                Next
                r += 1
            Loop
        End With
============================================================================

Thanks for the help, and sorry for the delay.

DK
Thank you again!