UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Simple Export To Excel, Any Version    
 
   
CyberCow
post Aug 10 2013, 02:13 PM
Post#1


UdderAccess Admin + UA Ruler
Posts: 19,555
Joined: 27-April 02
From: Upper MI


There are many occasions when a developer needs to export a table, query or a form's current RecordSet to Excel. However, the "ExportTo" or "Transfer" methods just fall short of the criteria for which such an export needs to take place. First, note please that this demo is NOT for Web apps.
After seeing ipisors' (Isaac's) "Generic Output to Excel Function", I was inspired to take it fews steps further. I basically took his slick concept, re-wrote the code and wrapped it all up into a callable form in Dialog mode.
WHAT IT DOES:
Basically, this demo allows a user to select an existing table or query and export it to a specifically named Excel file in a specifically selected folder. The Excel ouput centers and 'bolds' the header (column names) and a font may be selected. (Currently, the font selection list is just 'Arial', 'Calibri' & 'Times' and is just a Value List in the Row Source property of the font selection combobox. You may add more at your leisure.)
It also has code that can be added to a new command button on a form that will set that form's underlaying recordset as the source data for the Excel export. To call from a form, add a new command buton and in the new command button's "On_Click" event procedure, simply use: DoCmd.OpenForm "dlgExportToExcel", , , , , , Me.Form.Name
By using the Me.Form.Name, that action will pass the calling form's name to the dlgExportToExcel form's 'txtFrmNm' text box as an 'OpenArgs' value and hide the table/query selector combobox and provide the calling form's current recordset, including any filters (and I assume any sorting as well) that have been applied at the moment the dlgExportToExcel form was so opened.
Calling the dlgExportToExcel form without an OpenArgs value will simply open the form and allow the user to select a table or query.
After clicking the "Export" button, the user will see a message stating the full name and path of the saved Excel spreadsheet; then it displays the spreadsheet in Excel, bring the focus to the Excel window. This behavior is modify-able.
WHAT'S IN IT:
In this demo are two tables (one to house the list of "Special Folders" for the current user's setup and another "USysObjectCodes" to order the table & queries in one of the comboboxes), a simple form and a couple of modules to be able to select a specific target folder, and provide a list of the current machine's set of "Special Folders" that are based on the current user's settings, (stored in the tblSpecialFolders table.).
• tblSpecialFolders (table)
• USysObjectCodes (table)
• dlgExportToExcel (form)
• modAPIgetSpecialFolders (module)
• modAPI_SelectFolder (module)
• modObjExists (module)
This attachment contains demo files for Access 97, 2000, 2003 & 2007 - all have been tested in their respective versions and the Access 2007 version has been tested in Access 2010 and 2013.
HOW TO MAKE IT WORK FOR YOUR PROJECT:
1) Import all the objects if this demo into your project. (The dlgExportToExcel form can be simply opened to select a table or query for export.)
2) To call the dlgExportToExcel form from another form for the purpose of exporting a form's current recordset, create a new command button on the form you want to export data from, and in the command button's On_Click procedure use:
CODE
DoCmd.OpenForm "dlgExportToExcel", , , , , , Me.Form.Name
When the command button on the form is clicked, the dlgExportToExcel form will open with the name of the form calling the dlgExportToExcel form into the 'Currently Opend Form' text box.
3) That's pretty much it.
I could probably have employed "Public Types" for a couple of things code-wise, but I just wanted to generate a working concept demo.
Comments are not very prolific and the error handling is minimal, and what error handling is there can easily be stripped to replace with your own.
Please feel free to tweak it to your heart's content.
THere are some screenshots . . .
Attached File  exp01.PNG ( 20.25K )Number of downloads: 59

~~~
Attached File  exp02.PNG ( 25.45K )Number of downloads: 31

~~~
Attached File  exp03.PNG ( 29.19K )Number of downloads: 25

~~~
Attached File  exp04.PNG ( 20.91K )Number of downloads: 16

~~~
Attached File  exp05.PNG ( 26.04K )Number of downloads: 11

~~~
Attached File  exp06.PNG ( 26.69K )Number of downloads: 15

~~~
Attached File  exp07.PNG ( 20.2K )Number of downloads: 11

~~~
Attached File  exp08.PNG ( 29.1K )Number of downloads: 14

~~~
Attached File  exp09.PNG ( 21.54K )Number of downloads: 28

And finally, the zipped attachment:
Attached File  ExportToExcel.zip ( 212.89K )Number of downloads: 546

Enjoy! :hattip:
Go to the top of the page
 

Posts in this topic



Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 06:56 AM