smithvoice.com
 Y'herd thisun? 

“Remote to linux or mac machine from windows... or from linux or mac with SSH and SCP!”
-Smith


from this page

Controlling Excel exports and more

TaggedCoding, Database

This page builds off of the SmithVoice.com Export To Anything basics page.  Look there for the starting stuff, this page is devoted to workarounds for specific odd Excel errors.

A refresher for Excel conversions:

  • If you have DAO 3.51 (or higher) then Excel 5/95 (specifier Excel 5.0;) and Excel 97 (specifier Excel 8.0;) workbooks are made for you just like all other types.   Simply use the fully qualified path to the workbook you wish to create as the DATABASE element.  If the workbook file already exists the code will add a new worksheet file to the workbook.  If you do not have the most up to date MSExclXX.dll then a new workbook will not be created, but you still will be able to add a new worksheet to any existing high version workbook file.   Click the source code link at the bottom of this page, there is a full source code Excel<>Jet demo that covers each specifier.

  • Excel versions lower than 8.0 (97) have a limit of 16,384 rows per worksheet (256 Columns) , if you're exporting over the worksheet maximum rows you'll hit Err 3047 ("SreadSheet is Full).  Up the version to 8.0 to allow up to 65,536 rows (256 columns) or set a limit in the SQL with "Top 16384" in the SELECT Clause.  (I don't run Excel2000, has the limit been upped to 2 billion yet like all the good 3rd party grids have?   Let us know)

Have we found a bug in the MS Y2K Fix?

Brian Beverage, maker of the cool PlanetSourceCode Export app based on the Export To Anything techniques, let us know about an odd issue.

When exporting Jet to Excel he was hitting error #3349 “Numeric Field Overflow”.  At first it appeared to that the problem was due to null fields, but on field by field debugging he found that the real cuplrit was a Date type field value earlier than January 1, 1000.  For some reason the app did not catch a date entry of #02/01/999# and allowed that value to get into the database.  (This stuff happens to everyone at one time or another so keep it in mind).

When the Export To Anything SQL was run on the field, the error failed the export.  As soon as he removed that one value (or changed it to the correct value of 02/01/1999) the export ran fine.

Here's the funny part:  When we used the MS-Access IDE to do the same export, the error was not generated ... and the Access export routine automatically replaced the value with a null ... without giving any messages about it's doing so!

(NOTE: if you use Access's File|Save/Export option against the basetable instead of against thequerydef Access will create a tbl_ExportErrors table and show that particular row number as having a "Date Out of Range" error.  Still, we think that this sould not be an error per se)

That is a bit odd and scary, because some professions require databases and spreadsheets to use 3 digit year values ... ask any geneologist or historical researcher.

Here's the deal:

If you use Access to do the same stuff we are doing with Jet code (and our export technique is Jet stuff, it works for VB4 and higher, VC5 and higher and Delphi 4 and higher either via DAO or ADOx2.1) then you see how MS's Access automatically handles the error you hit:

1) Create the same SELECT query of all fields in the Access Query Builder

2) Verify that all of the records are pulled with the query

3) look and see that your value of 02/01/999 is in the query results

4) save the Querydef (qryMyTest)

5) Exit the Access query builder and from the main Access File menu, choose Export.

6) Choose the querydef (qryMyTest) as the source for the export

7) now choose Get External Data from the Access file menu. Choose to link the xls file you just made.

8) open the linked xls file in datasheet mode and do the sort ascending on that date field.

Note that all of the records are there (same rowcount)... but the export changed the value of that one field from 02/01/999 to a Null.

Neat, huh? We're betting that MS hit the error too and just started coding a replace up front. Sounds like an embedded IIF in their Export SQL like:

'shows the differences between source and adapted source values:

SELECT IIf(IsDate([tblTest].[MyDates]),
IIf(([tblTest].[MyDates])<#1/1/1000#, Null,
CDate([tblTest].[MyDates])), Null) AS MyFix,
tblTest.TestText, tblTest.MyDates FROM tblTest ORDER BY
tblTest.MyDates

'Just gives the results:

SELECT IIf(IsDate([tblTest].[MyDates]),
IIf(([tblTest].[MyDates])<#1/1/1000#,
Null,CDate([tblTest].[MyDates])), Null) AS MyFix,
tblTest.TestText FROM tblTest

If you hit err 3349, look to your Date type field values.   Remember, even complex SQL like the stuff noted above is fine in an Export To Anything execute routine. Of course it would be far better if the 3 digit year values would work, for our code AND for AccessIDE Export routines.

Hope this helps!

-Smith

 



home     who is smith    contact smith     rss feed π
Since 1997 a place for my stuff, and it if helps you too then all the better smithvoice.com