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