<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2654.45">
<TITLE>RE: [AccessD] Automating excel</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=2>John,</FONT>
<BR><FONT SIZE=2>Generally I shoot my users who mix data. Pretty soon the ones who are left know better than to mess up the spreadsheet. Failing this you might try functions T,TEXT,ISNUMBER,ISTEXT, VALUE or some combination in a "validation" column that references the actual data. This "scrubbed" data becomes the column you import.</FONT></P>
<P><FONT SIZE=2>Jim Hale</FONT>
</P>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: John Colby [<A HREF="mailto:jcolby@colbyconsulting.com">mailto:jcolby@colbyconsulting.com</A>]</FONT>
<BR><FONT SIZE=2>Sent: Thursday, September 04, 2003 12:01 PM</FONT>
<BR><FONT SIZE=2>To: Access Developers discussion and problem solving</FONT>
<BR><FONT SIZE=2>Subject: RE: [AccessD] Automating excel</FONT>
</P>
<BR>
<P><FONT SIZE=2>This is precisely the problem. does not create an import spec in the same</FONT>
<BR><FONT SIZE=2>way that you can do with comma delimited files. You are expected to "link"</FONT>
<BR><FONT SIZE=2>to the spreadsheet. That works, but when you do that, Access looks at the</FONT>
<BR><FONT SIZE=2>first row (or few rows) of data to decide what the data is. If the data</FONT>
<BR><FONT SIZE=2>type in a given column is (for example) date, and then 10 rows down changes</FONT>
<BR><FONT SIZE=2>to text - EVEN IF THE TEXT STRING IS A DATE - Access gives "error" in the</FONT>
<BR><FONT SIZE=2>cell (or something like that).</FONT>
</P>
<P><FONT SIZE=2>Even Excel itself, up until XP, did not allow you to SEE what the data type</FONT>
<BR><FONT SIZE=2>is. You could place your cursor in the top cell and start moving down the</FONT>
<BR><FONT SIZE=2>spreadsheet - you would have no clue by looking in the cell that the</FONT>
<BR><FONT SIZE=2>underlying datatype of the cell was no longer a date, but rather a string.</FONT>
<BR><FONT SIZE=2>In XP there is now a visual cue (a little red triangle in the corner or the</FONT>
<BR><FONT SIZE=2>cell) that the datatype changed.</FONT>
</P>
<P><FONT SIZE=2>Anyway, for this reason, Excel spreadsheets can really suck to use as a data</FONT>
<BR><FONT SIZE=2>exchange method. IF the spreadsheet is created programmatically such that</FONT>
<BR><FONT SIZE=2>all the cells in a given column are the same data type you will never have a</FONT>
<BR><FONT SIZE=2>problem. However if a user cuts and pastes the data in, that process for</FONT>
<BR><FONT SIZE=2>one reason or another may cause these data type changes at which point the</FONT>
<BR><FONT SIZE=2>linked spreadsheet will be useless.</FONT>
</P>
<P><FONT SIZE=2>For this reason, my idea was to use Excel itself to export the data to a</FONT>
<BR><FONT SIZE=2>true comma delimited file. The reason is that as we all know, a comma</FONT>
<BR><FONT SIZE=2>delimited file does not have associated with the data what the datatype was.</FONT>
<BR><FONT SIZE=2>It is ALL just a string of characters with commas between them. Thus a data</FONT>
<BR><FONT SIZE=2>would be converted to a string that looks like a date. A string that</FONT>
<BR><FONT SIZE=2>contains that same date would end up looking EXACTLY the same as the DATE</FONT>
<BR><FONT SIZE=2>itself. Is that as clear as mud? It's all just text to a CSV. Dates are</FONT>
<BR><FONT SIZE=2>text, strings are text, real numbers are text, currency is text.</FONT>
</P>
<P><FONT SIZE=2>NOW, with a pure text comma delimited file, a real import specification can</FONT>
<BR><FONT SIZE=2>be created that imports that pure text file back in to a table and there is</FONT>
<BR><FONT SIZE=2>no possibility of misunderstanding that date.</FONT>
</P>
<P><FONT SIZE=2>John W. Colby</FONT>
<BR><FONT SIZE=2>www.colbyconsulting.com</FONT>
</P>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: accessd-bounces@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>[<A HREF="mailto:accessd-bounces@databaseadvisors.com">mailto:accessd-bounces@databaseadvisors.com</A>]On Behalf Of Mark A Matte</FONT>
<BR><FONT SIZE=2>Sent: Thursday, September 04, 2003 10:41 AM</FONT>
<BR><FONT SIZE=2>To: accessd@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>Subject: RE: [AccessD] Automating excel</FONT>
</P>
<BR>
<P><FONT SIZE=2>John,</FONT>
</P>
<P><FONT SIZE=2>Could you create an "Import Specification" in Access where you could control</FONT>
<BR><FONT SIZE=2>what datatype was brought into each field?</FONT>
</P>
<P><FONT SIZE=2>Mark</FONT>
</P>
<BR>
<P><FONT SIZE=2>>From: "Erwin Craps" <Erwin.Craps@ithelps.be></FONT>
<BR><FONT SIZE=2>>Reply-To: Access Developers discussion and problem</FONT>
<BR><FONT SIZE=2>>solving<accessd@databaseadvisors.com></FONT>
<BR><FONT SIZE=2>>To: "Access Developers discussion and problem</FONT>
<BR><FONT SIZE=2>>solving"<accessd@databaseadvisors.com></FONT>
<BR><FONT SIZE=2>>Subject: RE: [AccessD] Automating excel</FONT>
<BR><FONT SIZE=2>>Date: Thu, 4 Sep 2003 07:53:40 +0200</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>You could open the file in excel from Access and read/evaluate line by</FONT>
<BR><FONT SIZE=2>>line from Access.</FONT>
<BR><FONT SIZE=2>>Saving to CSV will not help your datatype property.</FONT>
<BR><FONT SIZE=2>>Erwin</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>-----Oorspronkelijk bericht-----</FONT>
<BR><FONT SIZE=2>>Van: accessd-bounces@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>>[<A HREF="mailto:accessd-bounces@databaseadvisors.com">mailto:accessd-bounces@databaseadvisors.com</A>] Namens Charlotte Foust</FONT>
<BR><FONT SIZE=2>>Verzonden: woensdag 3 september 2003 22:09</FONT>
<BR><FONT SIZE=2>>Aan: Access Developers discussion and problem solving</FONT>
<BR><FONT SIZE=2>>Onderwerp: RE: [AccessD] Automating excel</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>I'm not even sure you can do that, John. Excel has a nasty habit of</FONT>
<BR><FONT SIZE=2>>changing datatypes no matter what you want it to do.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>Charlotte Foust</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>-----Original Message-----</FONT>
<BR><FONT SIZE=2>>From: John Colby [<A HREF="mailto:jcolby@colbyconsulting.com">mailto:jcolby@colbyconsulting.com</A>]</FONT>
<BR><FONT SIZE=2>>Sent: Wednesday, September 03, 2003 11:56 AM</FONT>
<BR><FONT SIZE=2>>To: AccessD</FONT>
<BR><FONT SIZE=2>>Subject: [AccessD] Automating excel</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>Has anyone ever automated Excel to save a spreadsheet as TRUE comma</FONT>
<BR><FONT SIZE=2>>delimited text? My issue is that a client's client sends them data. It</FONT>
<BR><FONT SIZE=2>>was comma delimited text. Now it's a spreadsheet. Spreadsheets SUCK</FONT>
<BR><FONT SIZE=2>>for data import since it is possible that the data will change datatypes</FONT>
<BR><FONT SIZE=2>>down the spreadsheet and that causes the import to fail without any</FONT>
<BR><FONT SIZE=2>>warning.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>If I can automate excel to export that same spreadsheet, I suspect that</FONT>
<BR><FONT SIZE=2>>the result would just be text in all cases and so that "datatype change"</FONT>
<BR><FONT SIZE=2>>issue would disappear.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>Has anyone ever done this?</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>John W. Colby</FONT>
<BR><FONT SIZE=2>>www.colbyconsulting.com</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>_______________________________________________</FONT>
<BR><FONT SIZE=2>>AccessD mailing list</FONT>
<BR><FONT SIZE=2>>AccessD@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>><A HREF="http://databaseadvisors.com/mailman/listinfo/accessd" TARGET="_blank">http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT SIZE=2>>Website: <A HREF="http://www.databaseadvisors.com" TARGET="_blank">http://www.databaseadvisors.com</A></FONT>
<BR><FONT SIZE=2>>_______________________________________________</FONT>
<BR><FONT SIZE=2>>AccessD mailing list</FONT>
<BR><FONT SIZE=2>>AccessD@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>><A HREF="http://databaseadvisors.com/mailman/listinfo/accessd" TARGET="_blank">http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT SIZE=2>>Website: <A HREF="http://www.databaseadvisors.com" TARGET="_blank">http://www.databaseadvisors.com</A></FONT>
<BR><FONT SIZE=2>>_______________________________________________</FONT>
<BR><FONT SIZE=2>>AccessD mailing list</FONT>
<BR><FONT SIZE=2>>AccessD@databaseadvisors.com</FONT>
<BR><FONT SIZE=2>><A HREF="http://databaseadvisors.com/mailman/listinfo/accessd" TARGET="_blank">http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT SIZE=2>>Website: <A HREF="http://www.databaseadvisors.com" TARGET="_blank">http://www.databaseadvisors.com</A></FONT>
</P>
<P><FONT SIZE=2>_________________________________________________________________</FONT>
<BR><FONT SIZE=2>Compare Cable, DSL or Satellite plans: As low as $29.95.</FONT>
<BR><FONT SIZE=2><A HREF="https://broadband.msn.com" TARGET="_blank">https://broadband.msn.com</A></FONT>
</P>
<P><FONT SIZE=2>_______________________________________________</FONT>
<BR><FONT SIZE=2>AccessD mailing list</FONT>
<BR><FONT SIZE=2>AccessD@databaseadvisors.com</FONT>
<BR><FONT SIZE=2><A HREF="http://databaseadvisors.com/mailman/listinfo/accessd" TARGET="_blank">http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT SIZE=2>Website: <A HREF="http://www.databaseadvisors.com" TARGET="_blank">http://www.databaseadvisors.com</A></FONT>
</P>
<BR>
<BR>
<P><FONT SIZE=2>_______________________________________________</FONT>
<BR><FONT SIZE=2>AccessD mailing list</FONT>
<BR><FONT SIZE=2>AccessD@databaseadvisors.com</FONT>
<BR><FONT SIZE=2><A HREF="http://databaseadvisors.com/mailman/listinfo/accessd" TARGET="_blank">http://databaseadvisors.com/mailman/listinfo/accessd</A></FONT>
<BR><FONT SIZE=2>Website: <A HREF="http://www.databaseadvisors.com" TARGET="_blank">http://www.databaseadvisors.com</A></FONT>
</P>
</BODY>
</HTML>