How does OpenText work?
Posted by James Cronen on August 14, 2000 1:38 PM
I have an application that reads and writes CSV
files. Naturally, I'm using Excel (97 for Windows)
to quickly edit and save CSV files.
My problem comes when I try to import data back into
.xls format from a .csv file; I've been opening the
.csv file using the Workbooks.OpenText method,
selecting the region in the .csv file to be copied,
then using Copy and PasteSpecial (values) to move the
values into the .xls worksheet. (I actually do want
to do further analysis, so it's not acceptable just
to save the .csv file as a .xls file.)
The Workbooks.OpenText method works okay for almost all
my needs. However, I'm having a bear of a time working
with dates. I'd like to open my .csv file and have all
fields appear as text. Essentially, I'm just using
Excel as a text editor in this way. However, even
when using OpenText and requesting (the FieldInfo
parameter) that every column be parsed as text, the
data are still being posted in Excel as dates!
This would work if you could format the column as text
before putting data in it, but seeing as Excel is
opening the file for me, there's no chance.
And, of course, just to throw another wrench into the
works, I need to work both in European (dd/mm/yyyy) and
American (mm/dd/yyyy) date formats. That shouldn't
be too much of a problem for me, as long as the dates are
loaded in as text.
The data in the column coming in are going to be of
all types, so blindly formatting as dates isn't
acceptable (I don't want the number 5 coming into Excel
as 01/05/1900). Also, I'd like to preserve (visual)
formatting where possible, so I really need to use
PasteSpecial Paste:=xlValues instead of just Paste.
Anybody have any ideas? I'm just about at wit's end
on this one.
Thanks in advance...