|
|
|
|
|
AQT offers three different methods for exporting to Excel:
Normal |
Requires Excel on your PC? |
Comments |
Normal |
Yes |
The "traditional" export method. Slow and can sometimes give problems |
Native export |
No |
Exports directly to Excel files. Doesn't require Excel on your PC. The fastest export method. |
Bulk exporting |
Yes |
Similar to, but much faster than, Normal. Data is exported as a group of row as a time |
Both Normal and Bulk export work by using a COM interface to Excel. Excel is invoked on your PC and commands are passed to it. There are sometimes problems with this interface - this can cause the export to fail or Excel being left in an unresponsive state.
Native Export writes directly to Excel files, so Excel is not invoked during the export. We recommend that this option is used.
Options you can specify for Export to Excel are:
Option |
Description |
Worksheet |
The name of the Worksheet the data is to be exported to. If this worksheet doesn't exist, it will be created. If it does exist, the Replace Worksheet or Append Data options will control how whether this worksheet is replaced or appended to. In other words, for Excel, Replace / Append operates at the Worksheet level rather than at the file level. |
Start from Cell |
Specifies the cell in the worksheet where AQT will start to write the results. |
Use Native file Export |
Specifies that Native Export is used. |
Use bulk exporting of data |
When this option is checked, AQT will load data using a bulk loading method. This method may not be available for older versions of Excel. If you are having problems exporting data to Excel, you may need to de-select this option. |
Use bulk formatting of dates |
This option is only used when Use bulk exporting of data is selected. By default, date values are formatted individually depending on the date value - e.g. whether it contains the time component and/or millisecond component. This is a very slow process as formatting each cell individually is very time consuming. When this option is selected, AQT will use a much faster method. The first 100 values of the column are scanned to determine the format of the date values, and the entire column is formatted in this way. When exporting a large amount of data, this is a considerably faster, though for some sets of data the formatting may be less reliable. |
Format numeric values as numbers |
This option relates to the case of numeric values being held in a character column. By default, these will be exported to Excel as a text values. When this option is select, these values will be exported as numeric values. Note: this option can be very slow when used in conjunction with Use Bulk Exporting. This is because AQT will format each cell individually, which will be slow when you have a large amount of data. |
Excel File Format |
When Native Export is used, this gives the format of the Excel file created |
Run Macro at End |
This specifies the name of a macro to be run after the export has completed. This macro might, for instance, format the data. Example code for a macro is given at the end of this topic. If the macro doesn't exist, you will not get an error. This option is not available for a Native Export. |
Worksheet password |
This sets a password on the exported worksheet. The worksheet can not be amended or overwritten until you Unprotect the worksheet (you do this within Excel). |
Overwrite worksheet |
Use this option if you wish to overwrite an existing password-protected worksheet. Specify, in worksheet password, the existing password for the worksheet. Once the export has completed, the worksheet will be protected with the password again. |
After it has finished writing the data, AQT will automatically save the workbook and leave it open for you to view.
When the export starts, AQT will start Excel and open the Excel file. Do not switch to Excel and edit this worksheet while the export is running!
Editing the worksheet will lock the worksheet, preventing AQT from writing to it. The export will fail with code 0x800AC472.
If your data has LOBs (such as images or documents) AQT can export these to separate files and create hyperlinks to them from your Excel worksheet. See Exporting LOBs for more on this.
If AQT sees that a column value starts with http:// or https:// it will assume that this is a url and will create a hyperlink to that address.
Some users have experienced crashes of Excel when exporting very large column columns (several thousand bytes). This problem only happens when Bulk Exporting has been selected. To prevent this problem from happening, de-select Use bulk exporting of data.
AQT supports both these types of Excel files. When you select an Excel file to save, enter a file name with an extension of:
If you are creating a new file, the Windows "Save as" dialog will not add this file extension to your file name, even if you have selected this in the "Save as Type" dropdown. You must manually include this file extension in your file name.
Native Export can export to Excel files in a number of different formats. This is specified in the Excel File Format dropdown.
You can avoid Row Limit problems by using Exporting to Multiple Files.
The following is an example of macro that can be run after the export has completed. This will sum a column (column 1 in this example) and format the worksheet.
Sub format_wb()
Dim xs As Worksheet
Dim r As Range
Dim lastcell As Range
Set xs = ActiveSheet
'--This will Sum a particular column
Dim column As Integer
column = 1
Set lastcell = xs.Cells(xs.Rows.Count, column).End(xlUp)
Set r = xs.Range(Cells(2, column), lastcell)
lastcell.Offset(1, 0) = WorksheetFunction.Sum(r)
'--Set the Font for all the Cells
Set r = xs.Range(xs.Cells(1, 1), xs.Cells.SpecialCells(xlCellTypeLastCell))
r.Font.Name = "Arial"
r.Font.Size = 8
' Set a background color for the Header Row and set font to Bold
Set r = xs.Range(xs.Cells(1, 1), xs.Cells(1, xs.Columns.Count).End(xlToLeft))
r.Font.Bold = True
r.Interior.ColorIndex = 40
End Sub