date format change when opening file with vb
 

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

date format change when opening file with vb
 

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)



"bradasley" wrote in message
...
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

date format change when opening file with vb
 

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

"Bob Phillips" wrote:

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)



"bradasley" wrote in message
...
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

date format change when opening file with vb
 

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!

*** Sent via Developersdex http://www.developersdex.com ***

date format change when opening file with vb
 

Hi,

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

"Rob Edwards" wrote:

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!

*** Sent via Developersdex http://www.developersdex.com ***

date format change when opening file with vb
 

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!

*** Sent via Developersdex http://www.developersdex.com ***