VBA opentext command

[jz193]

Hi- hope someone can help with this - its casuing me big problems.

I have just upgraded to excel (2003), and have an issue with when I am
opening text files in excel, using the opentext VBA command.
If I open the text file manually, I have no problem, all cells with dates in
them are recognised as dates. When I open this file and bring it in using
VBA, it recognises some of the dates as text, and so messes up my
calculations (have to press f2 and return in each cell to get them recognised
as a date).

I'm pretty sure that this is because it tries to bring them in in american
format for some reason, but has a problem when what it sees as the month
goes above 12 and therefore sees it as text (i.e
it brings it in as mm/dd/yyyy - however the file is dd/mm/yyyy do if dd>12,
it sees it as mm>12 therefore brings it in as text).

Additional info:
- date settings on my computer/excel are fine
- the file is a *.txt not *.csv
- Only happens when I use the VBA command (manual open is fine)

I think this is a bug (tried it on >5 computers)- any help would be
massively appreciated.

thanks in advnce

 

[tony h]

If you post the code you are using for the import it will be easier to
find a resolution.

regards

 

[Jim Rech]

Check out the OpenText method in Excel 2003 help. The last parameter is
Local. Set it to True.

--
Jim
| Hi- hope someone can help with this - its casuing me big problems.
|
| I have just upgraded to excel (2003), and have an issue with when I am
| opening text files in excel, using the opentext VBA command.
| If I open the text file manually, I have no problem, all cells with dates
in
| them are recognised as dates. When I open this file and bring it in using
| VBA, it recognises some of the dates as text, and so messes up my
| calculations (have to press f2 and return in each cell to get them
recognised
| as a date).
|
| I'm pretty sure that this is because it tries to bring them in in american
| format for some reason, but has a problem when what it sees as the month
| goes above 12 and therefore sees it as text (i.e
| it brings it in as mm/dd/yyyy - however the file is dd/mm/yyyy do if
dd>12,
| it sees it as mm>12 therefore brings it in as text).
|
| Additional info:
| - date settings on my computer/excel are fine
| - the file is a *.txt not *.csv
| - Only happens when I use the VBA command (manual open is fine)
|
| I think this is a bug (tried it on >5 computers)- any help would be
| massively appreciated.
|
| thanks in advnce

 

[jz193]

thanks for the replies- heres an exmaple of the code. Jim, I wasn't sure
what you meant by "last parameter", if you could clarify that would be great:

Workbooks.OpenText Filename:="R:\TEST.txt", Origin _
:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=True, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), TrailingMinusNumbers:=True


Any help much appreciated.

 

[Jim Rech]

Please do check out Help as that would answer your question. (Just add:
"Local:=True")

--
Jim
| thanks for the replies- heres an exmaple of the code. Jim, I wasn't sure
| what you meant by "last parameter", if you could clarify that would be
great:
|
| Workbooks.OpenText Filename:="R:\TEST.txt", Origin _
| :=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
| xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
| Semicolon:=True, _
| Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
| 1), _
| Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
| Array(7, 1), TrailingMinusNumbers:=True
|
|
| Any help much appreciated.
| "Jim Rech" wrote:
|
| > Check out the OpenText method in Excel 2003 help. The last parameter is
| > Local. Set it to True.
| >
| > --
| > Jim
| > | > | Hi- hope someone can help with this - its casuing me big problems.
| > |
| > | I have just upgraded to excel (2003), and have an issue with when I am
| > | opening text files in excel, using the opentext VBA command.
| > | If I open the text file manually, I have no problem, all cells with
dates
| > in
| > | them are recognised as dates. When I open this file and bring it in
using
| > | VBA, it recognises some of the dates as text, and so messes up my
| > | calculations (have to press f2 and return in each cell to get them
| > recognised
| > | as a date).
| > |
| > | I'm pretty sure that this is because it tries to bring them in in
american
| > | format for some reason, but has a problem when what it sees as the
month
| > | goes above 12 and therefore sees it as text (i.e
| > | it brings it in as mm/dd/yyyy - however the file is dd/mm/yyyy do if
| > dd>12,
| > | it sees it as mm>12 therefore brings it in as text).
| > |
| > | Additional info:
| > | - date settings on my computer/excel are fine
| > | - the file is a *.txt not *.csv
| > | - Only happens when I use the VBA command (manual open is fine)
| > |
| > | I think this is a bug (tried it on >5 computers)- any help would be
| > | massively appreciated.
| > |
| > | thanks in advnce
| >
| >
| >

 

[Tom Ogilvy]

Workbooks.OpenText Filename:="R:\TEST.txt", Origin _
:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=True, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1),
Array(7, 1), TrailingMinusNumbers:=True, Local:=True