Date format Workbooks Open

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?
 
B

Bob Bridges

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top