21061

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.

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

Recommend

  • How to execute a package in 32-bit mode using dtexec.exe?
  • Creating an Excel Workbook via OLEDB without headers
  • How to Disable all AutoNum in Access 2003
  • Reading CSV into using OLEDB
  • C# and read values from an Excel file
  • How to calculate the highest score when taking a path through a grid?
  • C# parameterized query with Domain Aggregate functions (DMin)
  • ORACLE SDO_GEOMETRY ToString()?
  • Which is the fastest way to upload the excelsheet having more than 100,000 lines of code in C#?
  • Given the square grid coordinates, how to calculate the data from a different table that falls under
  • import data from excel 2003 to dataTable
  • how to add horizontal scroll bar to a panel In ExtJs?
  • Reading Excel files in a locale independent way
  • Delphi XE3, ugly StringGrid's borders
  • Execute Success but num_rows return 0 [PHP-MySQL]
  • How to export MS Access table into a csv file in Python using e.g. pypyodbc
  • Update recordset without updating database
  • Displaying Data From Multiple MySQL Tables
  • Align Excel cell to center VB - xlCenter is not declared
  • Dynamically set LESS variables from user settings
  • Relative paths. baseUrl and paths not working on ionic2 - angular2
  • Converting query results into DataFrame in python
  • powershell Get-Counter -ComputerName parameter on Windows 7
  • Xamarin MonoAndroid Azure mobile service InsertAsync
  • Using Laravel 5.4 pusher
  • Private IP address in reserved subnet range
  • How To Customize ASP.NET Chart Databound To SqlDataSource
  • Azure table store snapshot/backup capability
  • Bigquery event streaming and table creation
  • Oledb connection string for excel files
  • PHP CURL timing out but CLI CURL works
  • With Hadoop, can I create a tasktracker on a machine that isn't running a datanode?
  • Reading JSON from a file using C++ REST SDK (Casablanca)
  • Linq Objects Group By & Sum
  • FB SDK and cURL: Unknown SSL protocol error in connection to graph.facebook.com:443
  • Counter field in MS Access, how to generate?
  • Large data - storage and query
  • Arrays break string types in Julia
  • Is there a mandatory requirement to switch app.yaml?
  • UserPrincipal.Current returns apppool on IIS