I'm just planning a facility to export varying query results as Excel worksheets. This will be implemented in an older VBA application and is focussed on end users. Therefore I would like to have a "ready" Excel sheet with appropriate column formats - e.g., when dealing with date columns, users should be able to sort chronologically without first having to format those columns as dates (vs. text).
Basically I seem to have at least those options with help of SQL Anywhere's features (with unsatisfying results so far):
Based on the very helpful suggestion by TDuemesnil, I re-organized my approach:
I compared a few facilities to export data from MS Access to MS Excel (both V2003) and found out that even then, the resulting Excel files are not well formatted IMHO. Whereas numerical columns are mostly o.k., dates are usually exported as text and therefore cannot by ordered as expected without first reformatting them. Given there are several builtin ways in MS Access to export to Excel (primarily DoCmd.TransferSpreadsheet and DoCmd.OutputTo), I was really surprised of the rather poor results.
So I decided that I should take the opposite direction and think this is the better way:
If I want an optimal Excel formatting, I should not rely on any RDBMS's Excel export. Instead it is better to use Excel VBA from the calling application to create an worksheet, fill it with the resultset (by means of Excel's Range.CopyFromRecordset method), and explicitely format the wanted columns with Excel's own tools.
So my solution now uses something like the following code snippet:
The mentioned VBA subroutine FormatExcelSheetColumns does the interesting formatting part:
Based on the resultset's column names and types, it creates a first row with the column headings and then formats the columns accordingly.
I have regular Spreadsheet that i need to fill with Data from our ASA Server. I use this VBA Script in Excel 2003 that connects with a ADODB connection, takes a connectionstring and a select statement.
Then i loop through the result set and push the Data in the correct fields. Existing Field formats will be preserved.
I think you can adopt this to your needs
answered 17 May '10, 11:00
Though the DBISQL option will work, I almost always do one of 2 things.
answered 14 May '10, 17:31