How to: Export the list to Excel

Write the contents of a tree list to an Excel workbook, with the tree as an outline, plus formats and totals, even on computers that have only the Access Runtime and no Excel. The same data can also go to XML or JSON for other programs.

Requires WKL Forms Object Library 1.23 or later.

ExportToXlsx writes the workbook itself (Office Open XML); ExportToXml and ExportToJson write the columns, the records as a tree with their values, and the totals; and ExportToFile picks the format from the file name extension (.xlsx, .xml, .json). Users get the same options with Export … on the shortcut menu (they choose the format in the Save dialog box). AllowExport, ExportMode, and ExportRows control this command, also on the designer's Grid, grouping and export page.

With Me!List1.Object
    .ExportToXlsx CurrentProject.Path & "\Orders.xlsx", AutoStart:=True   ' open afterwards
    .ExportToXlsx "Visible.xlsx", Rows:=werVisible                       ' relative: folder of the database
    .ExportToXlsx "Raw.xlsx", RawValues:=True                            ' stored values of lookup columns
    .ExportToFile "Orders.xlsx"                                          ' format by extension
    .ExportToXlsx "View.xlsx", Mode:=wemWysiwyg                         ' as shown: bands, rule colors
    .ExportToJson "Orders.json"                                          ' data for other programs
    .ExportToFile "Orders.xml"                                           ' XML by extension
End With

• The visible columns are exported in display order with their values and data types; numbers and dates keep the column format (currency, date, and Yes/No as in Access), and lookup columns export their displayed text unless you specify RawValues:=True.
• The tree becomes an Excel outline with the summary rows at the top; nodes collapsed in the list are collapsed in Excel too. Excel supports up to seven outline levels.
• Footer totals are written as SUBTOTAL formulas along with their values; the header row is frozen and repeated on every printed page.
• Rows:=werAll (the default) includes collapsed nodes; with LazyLoad, it reads all records once. werVisible exports only the rows that are shown.
• The file is written under a temporary name and then moved into place, so a failed export never leaves a half-written file. A missing folder raises wkeInvalidValue.
• Mode:=wemWysiwyg exports the view as shown: bands and multiline records become merged cells, format rules keep their colors and fonts, and gridlines are drawn. Values keep their data types; footer totals are plain values, because a formula over an Excel column would include the cells of other record lines. Data bars and icon sets aren't exported.
• XML and JSON contain the same records as a tree (children nested inside their parent) with one value per column; JSON uses JSON numbers, true/false, and ISO dates, while XML names the VBA data type of each value. Both export the list's data view; the as-shown mode applies only to the workbook. The file layout is described under ExportToXml and ExportToJson. Other extensions raise wkeInvalidValue.

• API reference: ExportToXlsx, ExportToFile
• WklTreeList overview