Previous Topic

Next Topic

Book Contents

Book Index

Export to Excel

Type of Excel Interface

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

Bulk exporting

Yes

Much faster than above. Data is exported as a group of row as a time

Native export

No

Exports directly to Excel files. Doesn't require Excel on your PC. The fastest export method.

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 occasionally problems with this interface if Excel is unresponsive on in an odd state. Also Excel can sometimes be left in an odd state after the AQT export has completed.

Native Export writes directly to Excel files, so Excel is not invoked during the export. It is the option we recommend.

Options

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.

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.

Start Cell

Specifies the cell in the worksheet where AQT will start to write the results.

Use bulk exporting of data

When this option is checked, AQT will load data using a bulk loading method. This is a lot faster than loading the data a cell at a time.

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.

Native Export

Specifies that Native Export is used.

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.

After it has finished writing the data, AQT will automatically save the workbook and leave it open for you to view.

Do not use Excel while this is running....

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.

Exporting LOBs

If your data has LOBs (such as images or ducuments) AQT can export these to separate files and create hyperlinks to them from your Excel worksheet. See Exporting LOBs for more on this.

Hyperlinks

If AQT sees that a column value starts with http:// it will assume that this is a url and will create a hyperlink to that address.

Excel Crash when using exporting large columns

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.

Exporting to Excel 2007 Files and XML Worksheets

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.

Row Limit

Example of an Excel Macro

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