Simple Export To Excel, Any Version
Aug 10 2013, 02:13 PM
UdderAccess Admin + UA Ruler
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:
DoCmd.OpenForm "dlgExportToExcel", , , , , , Me.Form.NameWhen 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 . . .
exp01.PNG ( 20.25K )Number of downloads: 45
exp02.PNG ( 25.45K )Number of downloads: 23
exp03.PNG ( 29.19K )Number of downloads: 20
exp04.PNG ( 20.91K )Number of downloads: 12
exp05.PNG ( 26.04K )Number of downloads: 8
exp06.PNG ( 26.69K )Number of downloads: 12
exp07.PNG ( 20.2K )Number of downloads: 7
exp08.PNG ( 29.1K )Number of downloads: 8
exp09.PNG ( 21.54K )Number of downloads: 20
And finally, the zipped attachment:
ExportToExcel.zip ( 212.89K )Number of downloads: 483
Mar 15 2017, 10:09 AM
Joined: 29-March 05
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.
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Apr 6 2017, 09:32 AM
UdderAccess Admin + UA Ruler
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.
_Cybercow (moo) _______ Microsoft Access MVP
_ "Don't let your talent take you where your character can't keep you."
_ Carpe notitia ● How to Post a Good Question ● Newcomer's Reading List ● Differences in Access Versions
_ There are two kinds of people in the world: those who can extrapolate data from incomplete information.
|Search Top Lo-Fi||27th April 2017 - 11:43 AM|