Passing FieldInfo Array from VBS to objExcel.OpenText
Jos
Here is my problem: I want to merge multiple .csv files into one .xls file,
each .csv in a separate sheet 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
J.Williams
"Jos" <J...@discussions.microsoft.com> wrote in message
news:943CCCC2-983D-4BFC...@microsoft.com...
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 sheet 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.