Date format Workbooks Open

  • Thread starter Thread starter JasonC
  • Start date Start date
J

JasonC

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.
 
Back
Top