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 Data>Text 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?

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 Data>Text 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.