Macro changes the format of dates

  • Thread starter Thread starter Rodolfo Silva
  • Start date Start date
R

Rodolfo Silva

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