This code sample demonstrates how to export a MS-Access table to a MS-Excel Worksheet using OleDb data provider. The main intent is to export data without concern for formatting of cells which can’t be done unless using Excel automation. The underlying method to achieve this is a SQL statement that is very simple once seen but eludes developer who search the web for this method.

Most sample code found on the web will show this simple SQL statement using the older version (.xls) with MS-Access older version (.mdb) while a new version of Excel (.xlsx) and newer version of MS-Access (.accdb) need a different provider in the SQL statement. On top of this, you can’t export older MS-Access to a newer MS-Excel file using this method. You need to check for compatibility in that both Excel and Access are .mdb and .xls or .accdb and .xlsx.

A good deal of assertion is required up front if you want to allow users to dynamically select both the database and spreadsheet. For instance, not only must we check for the above but also if the sheet exists already along with proper names of columns and table names. This is all done in the sample code. One thing I left out was the ability to select both the MS-Access and MS-Excel files which in a application would be done via a FileDialog component, here I’m controlling the environment for proper results.

The database is Microsoft NorthWind and the Excel files are empty at the start.

If the export process is done without user intervention, then a lot less assertion is required. One would first check to see if the sheet already exists, in the solution there is code for checking if a sheet exists.

Solution is in VS2015 with both C# and VB.NET https://code.msdn.microsoft.com/Expo...le-to-e5931ec1

In the solution, projects in blue are support projects, the third and fourth projects are not needed. The others are broken down as they can be used for other things besides exporting data to Excel. The projects in red are two samples to export data to Excel, best to start with MulipleExports which first does a make table comprised of several tables in Access and exports them to Excel. The other two projects export a single table to Excel.

Name:  Solution.jpg
Views: 1256
Size:  42.8 KB