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.
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