I have recorded this macro in order to import a text file and convert the text to date values.
The text file (with the extension LIS) looks like this:
DEC05
NOV05
MAY05
FEB05
JAN05
The aim is that the file gets imported into Excel and the text gets converted to a date format. So, for example, DEC05 gets converted to Dec-05 (01/12/2005 in the formula bar).
When recording the macro the result is as expected; but when the macro is run subsequently DEC05 gets converted to 05-Dec (05/12/2006 in the formula bar).
The macro looks like:
Workbooks.OpenText Filename:= "C:\test.lis", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 4)
If anyone has any ideas they will be massively appreciated. The format of the text file cannot change.
Thanks.
The text file (with the extension LIS) looks like this:
DEC05
NOV05
MAY05
FEB05
JAN05
The aim is that the file gets imported into Excel and the text gets converted to a date format. So, for example, DEC05 gets converted to Dec-05 (01/12/2005 in the formula bar).
When recording the macro the result is as expected; but when the macro is run subsequently DEC05 gets converted to 05-Dec (05/12/2006 in the formula bar).
The macro looks like:
Workbooks.OpenText Filename:= "C:\test.lis", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 4)
If anyone has any ideas they will be massively appreciated. The format of the text file cannot change.
Thanks.