date format change when opening file with vb

[Guest]

When opening a file by simply double clicking, the dates in the file remain
in UK format.

When opening the file as part of a macro, the dates where the the day is
greater than 12 switches to US format.

Does anyone know how I can stop the dates switching to US format?

Thanks

David

 

[Bob Phillips]

When you say switches, do you mean in shows in US format in Excel, or you
see US dates in VBA?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

It shows the date in us format in excel where the uk day is less than 12.

 

[Rob Edwards]

Hi,
I presume that you are using the OpenText method to import the
info, which has a series of two-element arrays.

If yes, then the second element is a data type that interprets the
info. Use '4' to ensure the data is set in the DMY format.

Rob Edwards

Always look on the bright side of life!

 

[Guest]

Hi,

Thanks for the advice, but can you explain what you mean by '4'?

 

[Rob Edwards]

Hi,
The 4 is the data type that imports dates in the DMY format. For
instance...

Workbooks.OpenText Filename:="GRAHAM", Origin:=xlWindows _
, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0,
4), Array( _
23, 9), Array(26, 1)

The arrays above are in the form Array(n, m). The n = start position,
the m = data type.

The full list for the data types (m) is...

1 General Data
2 Text
3 MDY
4 DMY
5 YMD
6 MYD
7 DYM
8 YDM
9 Skipped

Therefore, from the first array above:

Array(0, 4) Start position 0 & import as DMY
Array(23, 9) Start position 23 & skip
Array(26, 1) Start position 26 & import as general data

Rob Edwards

Always look on the bright side of life!