X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Simple Export To Excel, Any Version    
post Aug 10 2013, 02:13 PM

UdderAccess Admin + UA Ruler
Posts: 19,557
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.
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.
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.
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:
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: 69

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

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

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: 29

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

Enjoy! :hattip:
Go to the top of the page
post Mar 15 2017, 10:09 AM

Posts: 18,324
Joined: 29-March 05
From: Wisconsin

I noticed that the form only allows you to select tables that reside within the file itself, but ignores linked tables. If you want to export a linked table from the form without creating a query based on it, you can alter the form's combobox to include them like this:

Open the form dlgExportToExcel in Design Mode, move the textbox "txtFrmNm" away from the combobox "cboQuery" so you can select the combobox. Change the combobox's rowsource to this:

SELECT Name, IIf([Type]=1,"Table",IIf([Type]=6,"Linked Table","Query")) AS ObjType
FROM MsysObjects WHERE Type In (1,5,6) AND Left$([Name],1)<>"~" AND Left$([Name],4)<>"Msys" AND Flags>=0
ORDER BY Type, Name;

Put the textbox back on top of the combobox where it was, close the form and save changes.

You should be able to export data from linked tables now.

Go to the top of the page
post Apr 6 2017, 09:32 AM

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

Yeah, I initially overlooked the entire concept that the USysObjects table is hidden and will not import as easily as originally suggested.

NOTE: Tables that have the prefix "MSys" or "USys" are by default hidden tables. Those that begin with "MSys" are usually generated andf managed by Access. The "USys" prefix is a means for developers to create tables that are hidden by default without confusing them with the system tables created and used by Access.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    21st March 2019 - 05:15 AM