>I have a Visual FoxPro 6 application where I transfer large amounts of data into Excel using OLE automation. Some of my data starts in .CSV files and some of it starts in FoxPro tables which is easily made into a .CSV file using the "COPY TO myfile.csv DELIMITED" command.
>
>Because the data sets are large (i.e. 60K rows by 100 columns), I need to avoid any cell by cell formatting. So far, I have tried to blast in the entire .CSV file by just opening it in Excel using OLE with some minor cosmetic formatting with borders, headers, column width stuff, etc.
>
>My problem is that sometimes my data includes character fields that is supposed to just be plain text, but has codes like "12-1", "01-1", and "02-1". Unfortunately, the Excel programmers tried to think too much and convert this information into dates instead of just leaving it as text as previous data was. This ruins the data since I can never get it back to the original codes once Excel changes it. I can add an extra character to the front of the field and keep it intact and strip the extra character out later in Excel but this adds extra time and I would like to avoid it if possible.
>
>Also for the data that starts in a FoxPro table, I have tried using the clipboard using the _VFP.DATATOCLIP(,,3) command with a paste into Excel using OLE automation. This avoids the date problem but the data loaded into Excel contains trailing spaces up to the width of the foxpro field which must be stripped back out to get the proper centering of data. Once again, this is something I want to avoid.
>
>Anyway, are there other super fast ways to populate Excel with huge amounts of data that avoid these issues?
>
>Thanks,
>Jeff

Jeff,
I'd go with ADO transfer. Check QueryTables and FoxyClasses for a sample.
Cetin