date format change when opening file with vb

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
 
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!
 
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!
 
Back
Top