Crosstab Export

The Crosstab Export in Enviro Data Version 2012 B 1.987 and Version 8 uses Microsoft Visual Basic for Applications (VBA) in Excel to create the final spreadsheet report. When the export is run, a VBA macro attempts to set or get the printer page setup properties for the sheet(s) in the Excel workbook. If a printer is not instaled, an error will occur as Excel cannot set the page setup properties. See here for more details

run time error1004

To correct this error when you already have a printer installed, you need to make that printer the default. If you do not have an installed printer, add a printer to Windows and make it the default printer. We recommend setting as a printer the Microsoft Print to PDF.

To change or set the printer In Windows 10 open Settings --> Devices --> Printers & Scanners and select/install a printer. Generally you should let Windows 10 manage the default printer. To allow this, check the check box shown at the bottom of the figure below.

printers scanner settings

In older versions of Windows, the settings to add a printer and make it the default may be found in the Control Panel.

set up printer


When retrieving a large number of samples to produce a crosstab, you may encounter the following error. 

Visual Basic Run-Time Error 3047 Record is too large.

A query can be only 1000 character wide. When the crosstab export runs it attempts to group column information and build queries to fall within this limit. The text in a query can be only 1000 character wide. Currently the crosstab export pulls 150 columns for each query. So if you select a large number of samples, the export may actually run 3 queries to put the data on the Excel sheet.

This problem can occur in cases where the FieldSampleIDs and/or other information are very long, or if you have a lot of reg limit exceedances, since the background color is part of the data export.

The solution is to reduce the number of samples selected, check field sample IDs, and other items to reduce the size of the query.

Other factors may also be in play in that the VIEWER may need some maintenance. To correct this go to Maintain Database --> and click Clear Temporary Tables then on the Access menu go to Database Tools and click Compact and Repair Database.

The crosstab report will display up to 12 limits but to display multiple regulatory limits in a crosstab or other report that supports them, you must first setup a regulatory limit group and then add the limits you require to this group. When creating groups, we recommend naming your group in a way you can identify it as a group such as  using an “_” (underscore character) in front of the name to differentiate a single limit from a group.

Additionally, you must have checked the Display Reg Limits check box shown below. 

display reg limits in crosstab

For more information see the FAQ on Why are my regulatory limits not showing up in reports?