Re: Passing FieldInfo Array from VBS to objExcel.OpenText


"Jos" <Jos@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:943CCCC2-983D-4BFC-9F07-F51B6DC0991D@xxxxxxxxxxxxxxxx
Hi,

Here is my problem: I want to merge multiple .csv files into one .xls
file,
each .csv in a separate *** as fast as possible, using the OpenText
method
of Excel in VBS.
The .csv file is comma delimited, text is enclosed in double quotes, dates
are in format dd-mm-yyyy. For exchangebitily with other products we use,
this format is required.
I am Dutch and Excel on our server is configured to semicolon as field
delimiter for csv file objExcel.Open, so I must use OpenText with the
SemiColon argument set to False and the Comma argument set to True. Fine
sofar for numbers, text.
For date fields (I know which column is a date field) I use a
...NumberFormat = "dd/mm/yy;@" for the entire column.
The date fields however are displayed dd-mm-yy for dates that begin with a
zero eg:
02-08-2007 is displayed as 02-08-07, but dates that do not begin with a
zero
eg:
31-07-2007 is displayed as 31-07-2007 and do seem to be text rather than
date.

This is what I tried:

Const xlDelimited = 1
Const xlGeneralFormat = 1
Const xlTextFormat = 2
Const xlDMYFormat = 4
Const xlTextQualifierDoubleQuote = 1

Set objExcel = CreateObject("Excel.Application")

With objExcel

strDataTypes = Array(Array(1, xlDMYFormat),Array(2, xlDMYFormat), Array(3,
xlGeneralFormat), Array(4, xlTextFormat), Array(5, xlDMYFormat), Array(6,
xlGeneralFormat), Array(7, xlGeneralFormat), Array(8, xlDMYFormat),
Array(9,
xlDMYFormat), Array(10, xlTextFormat))

.Workbooks.OpenText strFileName, , 1, xlDelimited,
xlTextQualifierDoubleQuote, False, False, False, True, False, False, ,
strDataTypes

First lines from .csv:

"DatTotMet","DatVanaf","Iw3Nr","Naam","GebDat","Lft","VgNr","OpLijst","AfLijst","Reden_UitStroom"
31-07-2007,01-01-2007,2725,"Rob, E.
(Eveline)",10-12-1969,37,1,24-11-2004,05-01-2007,"Overige reden"
31-07-2007,01-01-2007,3178,"Kroonenberg, J.
(Johannes)",09-10-1970,36,2,24-11-2004,05-01-2007,"Overige reden"
31-07-2007,01-01-2007,3182,"Kuipers, J.J.A.
(John)",27-09-1985,21,3,24-11-2004,05-01-2007,"Verhuizing naar andere
gemeente / wgr"
31-07-2007,01-01-2007,2755,"Doorgeest, M.
(Marianne)",12-04-1948,59,4,30-06-2004,11-01-2007,"Overige reden"
31-07-2007,01-01-2007,3072,"Brandes, A.F.K.
(Lex)",07-02-1961,46,5,23-08-2004,11-01-2007,"Overige reden"

I think the array is in fault or the way I pass it, or...???

--
Jos

When Excel VBA sees a *.csv filename, it ignores the FieldInfo data.

Now, the dates in your file are in dd-mm-yyyy format, however Excel is
seeing them as mm-dd-yyyy. Consequently, where a date is a valid mm-dd-yyyy
date, Excel formats it in mm/dd/yyyy date format, otherwise it formats it as
dd-mm-yyyy in general format. From your test data, you should notice that
dates in the Excel *** with the '/' separator have been incorrectly
converted and formatted as date, whereas those with '-' haven't been
converted and formatted as general.

If you rename the .csv to .txt (or almost anything else), then it will
format the dates as expected.


.