Macro changes the format of dates
 

I use Windows 2000 (5.00.2195) and Office 2000.
I use a macro that open a text file delimited with
semicolon.
I use the next instruction:

Workbooks.OpenText FileName:=xarchi,
DataType:=xlDelimited, semicolon:=True, Tab:=False

"xarchi" contains the name of the file that I open.

Problem: The macro open the file and changes the format
of dates, i.e. 07/02/2003 when the text file contains
02/07/2003

When I open the file manually (via menu) dates are not
changed.
The macro works fine in Win95, Win98, WinXP and all the
previous Excel versions..
The problem is not at Regional Configuration.
If I use a text file delimited with comma, the macro
opens the file and does not convert the format of date.

Thanks in advance

Rodolfo Silva

Macro changes the format of dates
 

Record another macro when you do it next time.

You shortened up that workbooks.opentext line. It has a FieldInfo:= parm that
contains your spec for how to treat the data. Make sure you pick the correct
format: mdy or dmy (I couldn't tell from your example.).

And if the filename is .csv, rename it to .txt. (VBA pretty much ignores any
choices you've made for importing the values (via code).)



Rodolfo Silva wrote:

I use Windows 2000 (5.00.2195) and Office 2000.
I use a macro that open a text file delimited with
semicolon.
I use the next instruction:

Workbooks.OpenText FileName:=xarchi,
DataType:=xlDelimited, semicolon:=True, Tab:=False

"xarchi" contains the name of the file that I open.

Problem: The macro open the file and changes the format
of dates, i.e. 07/02/2003 when the text file contains
02/07/2003

When I open the file manually (via menu) dates are not
changed.
The macro works fine in Win95, Win98, WinXP and all the
previous Excel versions..
The problem is not at Regional Configuration.
If I use a text file delimited with comma, the macro
opens the file and does not convert the format of date.

Thanks in advance

Rodolfo Silva

--

Dave Peterson

Macro changes the format of dates
 

Just for information -
The OP said:
I use Windows 2000 (5.00.2195) and Office 2000


There is on LOCAL argument for OpenText in Excel 2000.

--
Regards,
Tom Ogilvy


keepitcool wrote in message
...
Rodolfo,

tried this with xlXP..

it's the only argument in help that gives you NO clue..
and it's NOT recorded with macro recorder.

it appears the LOCAL argument in OpenText method is the key you're
looking for.

Try following.. with local argument true resp false and notice the
difference


dates.txt with 1 line only..
01-05-03;05-01-03

sub Test()
Workbooks.OpenText Filename:="D:\Documents\dates.txt", _
Origin:=437, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=True, _
Comma:=False, _
Space:=False, _
Other:=False, _
Local:=True '<- this decides date interpretation
end sub





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Rodolfo Silva" wrote:

I use Windows 2000 (5.00.2195) and Office 2000.
I use a macro that open a text file delimited with
semicolon.
I use the next instruction:

Workbooks.OpenText FileName:=xarchi,
DataType:=xlDelimited, semicolon:=True, Tab:=False

"xarchi" contains the name of the file that I open.

Problem: The macro open the file and changes the format
of dates, i.e. 07/02/2003 when the text file contains
02/07/2003

When I open the file manually (via menu) dates are not
changed.
The macro works fine in Win95, Win98, WinXP and all the
previous Excel versions..
The problem is not at Regional Configuration.
If I use a text file delimited with comma, the macro
opens the file and does not convert the format of date.

Thanks in advance

Rodolfo Silva

Macro changes the format of dates
 

Thak you Dave for your previous answer.

I applied the FieldInfo:= param. in this way:

Workbooks.OpenText FileName:=xarchi,
DataType:=xlDelimited, semicolon:=True, Tab:=False,
FieldInfo:=Array(Array(6, 4), Array(12, 4))

I need open dates with format dd-mm-aaaa. The dates are
in columns 6 and 12.
The FieldInfo:= param has no effect, even though I tried
changing the redistribution param. Why?

Date format is incorrectly changed. Additionally, for
testing, I apply date format manually
(Format/Cells/Date). The result is:
09-01-01
25-02-2001
03-02-01
07-05-01

Why the format is not applied regularly (aaaa vs aa)?

Thank you in advance

-----Original Message-----
Record another macro when you do it next time.

You shortened up that workbooks.opentext line. It has a
FieldInfo:= parm that
contains your spec for how to treat the data. Make sure
you pick the correct
format: mdy or dmy (I couldn't tell from your example.).

And if the filename is .csv, rename it to .txt. (VBA
pretty much ignores any
choices you've made for importing the values (via code).)



Rodolfo Silva wrote:

I use Windows 2000 (5.00.2195) and Office 2000.
I use a macro that open a text file delimited with
semicolon.
I use the next instruction:

Workbooks.OpenText FileName:=xarchi,
DataType:=xlDelimited, semicolon:=True, Tab:=False

"xarchi" contains the name of the file that I open.

Problem: The macro open the file and changes the format
of dates, i.e. 07/02/2003 when the text file contains
02/07/2003

When I open the file manually (via menu) dates are not
changed.
The macro works fine in Win95, Win98, WinXP and all the
previous Excel versions..
The problem is not at Regional Configuration.
If I use a text file delimited with comma, the macro
opens the file and does not convert the format of date.

Thanks in advance

Rodolfo Silva

--

Dave Peterson

.

Macro changes the format of dates
 

For some reason excel isn't seeing that as a date. Try formatting it as mmm dd,
yyyy (or your local equivalent).

The only way I could get the problem was with the file extension = .csv (maybe
be different in your version--look for File|saveAs and look for your
equivalent).

What was the name of xarchi?

I don't have a better explanation.

===

Did you try the local:=true that keepitcool suggested.



Rodolfo Silva wrote:

Thak you Dave for your previous answer.

I applied the FieldInfo:= param. in this way:

Workbooks.OpenText FileName:=xarchi,
DataType:=xlDelimited, semicolon:=True, Tab:=False,
FieldInfo:=Array(Array(6, 4), Array(12, 4))

I need open dates with format dd-mm-aaaa. The dates are
in columns 6 and 12.
The FieldInfo:= param has no effect, even though I tried
changing the redistribution param. Why?

Date format is incorrectly changed. Additionally, for
testing, I apply date format manually
(Format/Cells/Date). The result is:
09-01-01
25-02-2001
03-02-01
07-05-01

Why the format is not applied regularly (aaaa vs aa)?

Thank you in advance

-----Original Message-----
Record another macro when you do it next time.

You shortened up that workbooks.opentext line. It has a
FieldInfo:= parm that
contains your spec for how to treat the data. Make sure
you pick the correct
format: mdy or dmy (I couldn't tell from your example.).

And if the filename is .csv, rename it to .txt. (VBA
pretty much ignores any
choices you've made for importing the values (via code).)



Rodolfo Silva wrote:

I use Windows 2000 (5.00.2195) and Office 2000.
I use a macro that open a text file delimited with
semicolon.
I use the next instruction:

Workbooks.OpenText FileName:=xarchi,
DataType:=xlDelimited, semicolon:=True, Tab:=False

"xarchi" contains the name of the file that I open.

Problem: The macro open the file and changes the format
of dates, i.e. 07/02/2003 when the text file contains
02/07/2003

When I open the file manually (via menu) dates are not
changed.
The macro works fine in Win95, Win98, WinXP and all the
previous Excel versions..
The problem is not at Regional Configuration.
If I use a text file delimited with comma, the macro
opens the file and does not convert the format of date.

Thanks in advance

Rodolfo Silva

--

Dave Peterson

.


--

Dave Peterson