Date format Workbooks Open
 

I have an application which saves the output to .xls format. If I open the
file from Excel, I get the Text Import Wizard. There is a date/time column
in format
dd/mm/yyyy hh:mm

However, If I use Application.Workbooks.Open to open the file, the date/time
column is in format dd/mm/yy hh:mm

ie the yyyy is shortened to 2 digits.

Manually clicking into a cell and pressing enter updates the cell, as does
Text to Columns. I have recorded some code to use DataText to Columns, but
running the code does nothing. If the dates are in column L, the code is as
follows:

Columns("L:L").Select
Selection.TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True

I have tried different methods of opening the file and of delimiting the
text after it is open, but can't get the date correct.

Can anyone help?

Date format Workbooks Open
 

I'm missing something: If your output is saved to .xls format, how is it
possible that when you open it again it fires up the Text Import wizard? Is
it a text file or a workbook? Sounds to me as though your app is improperly
saving it as a text file with an .xls extension.

--- "JasonC" wrote:
I have an application which saves the output to .xls format. If I open the
file from Excel, I get the Text Import Wizard. There is a date/time column
in format dd/mm/yyyy hh:mm. However, If I use Application.Workbooks
.Open to open the file, the date/time column is in format dd/mm/yy hh:mm
ie the yyyy is shortened to 2 digits.

Manually clicking into a cell and pressing enter updates the cell, as does
Text to Columns. I have recorded some code to use DataText to Columns, but
running the code does nothing. If the dates are in column L, the code is as
follows:

Columns("L:L").Select
Selection.TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True

I have tried different methods of opening the file and of delimiting the
text after it is open, but can't get the date correct.