Export access query to excel in console application

Question

I have a query being created using OLEDB from access and need to get the information to an already existing excel spread sheet. I am using VS 2010 with VB.Net, MS Access and Excel are 2010. I have been running in circles for the last two days and cannot figure out, how to get the data from a DataReader into excel?

This is a VB.Net console application, so no web tables or grids can be used.

Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data    Source=C:\Pureshow\Pureshow.mdb;")
Dim cmd As New OleDb.OleDbCommand("SELECT PWTC08.Lot AS LotID, PWTC08.Reg AS OfficialID FROM ((PWTC08 INNER JOIN SWTC08 ON cint(LEFT(PWTC08.Lot, InStr(PWTC08.Lot, '-') - 1)) = SWTC08.FileId) INNER JOIN BWTC08 ON PWTC08.Buyer = BWTC08.FileId)", conn)

    Try
        conn.Open()
        Dim read As OleDb.OleDbDataReader = cmd.ExecuteReader()
        If read.HasRows Then
            While read.Read()
            End While
        Else
        End If

        read.Close()

    Catch ex As Exception

    Finally
        conn.Close()
    End Try

I am almost to the point of just outputting to a CSV and telling the user to import into excel each time. I can export to excel, if I put it in a WebForm, but I can't figure out how to do using a console app?

Thanks for any help.

Fionnuala · Accepted Answer · 2012-06-27 15:45:18Z

2

You can execute a query against an MS Access connection that creates an Excel file:

SELECT t.ID, t.AText, t.ADate 
INTO [Excel 8.0;HDR=YES;DATABASE=Z:\Docs\New2.xls].[Sheet1]
FROM Table1 As t

answered Jun 27, 2012 at 15:45

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Primetime Over a year ago

This worked like a charm, wish I had known this a couple days ago. I learn something new every day. Thanks for the help.

Collectives™ on Stack Overflow

Export access query to excel in console application

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related