Comma Delimited Text File Issue

[solomon_monkey]

I have been using this archive for a few months now and have found them
wonderfully useful. I have a very strange problem though... when I
define a text file and delimit it in an Excel macro it amends some
dates. In the .txt file the dates are all in the month of December
01/12/2004; 02/12/2004 etc... when I use the macro below it reverses
the dates in the cell to 12/01/2004 etc...

Sub
myFile = Application.GetOpenFilename("Text,*.txt")
Workbooks.OpenText Filename:=myFile, DataType:=xlDelimited,
Comma:=True
End Sub

If I do not have the first line of code but instead have the location
of an actual file this problem does not exist.

e.g.

Sub
Workbooks.OpenText Filename:="S:\myFile.TXT",
DataType:=xlDelimited, Comma:=True
End Sub

Any ideas would severely be appreciated as this is the first part of a
macro that I run for 15-20 different file names a week.

Solomon

 

[Guest]

Are you American?

There are many issues with Excel's (mostly feeble) attempts to work with
different date formats. English dates for example are dd/mm/yyyy whilst
American's prefer mm/dd/yyyy which is what Excel works with.

You may find that changing the number format of the cells to a different
date format (look in custom formats) may solve your problem...

 

[solomon_monkey]

Thanks for the idea- alas I am not American and it is not an American
date format problem... it is from a .txt file with the format in
correct rotw (rest of the world) format and it does work fine if you do
specify the file in the macro but not if you leave it so the user can
specify the file... I need the user to specify though...

 

[Dave Peterson]

If you record a macro when you open your .txt file, you'll see a text import
wizard where you can specify the format for that column. You can choose from
mdy, ydm, dmy (and more).

Include that portion in your code and it should be ok.

 

[solomon_monkey]

Thanks Dave,

Unfortunately I do not see the text import file. ALl I get when I
record the macro is

Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote , ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo:=...

Regards,

 

[Dave Peterson]

That fieldinfo that you truncated is what does the real work.

If your file is named .csv, change it to .txt and try recording again.

 

[Lonnie M.]

Hi, here is an example of some code that imports a text file with 7
columns. You can change the data type to text for your date column and
it should keep your format. This doesn't bring up the wizard, so let me
know if you want a different example to bring up the wizard.

'########################################################
' The Array(X,Y) in field info represents that column's data type
''X' Represents the Column; 'Y' Represents the data type
' Data type values:
'1 = General; 2 = Text; 3 = Date (MMDDYY)
Workbooks.OpenText FileName:=Fname, Origin _
:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False
_
, Comma:=False, Space:=False, Other:=False, OtherChar:=False, FieldInfo
_
:=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 2), Array(5,
1), _
Array(6, 3), Array(7, 3))
Columns.AutoFit
'########################################################
HTH--Lonnie M.

 

[solomon_monkey]

Thanks peeps- sorry for being dumb on the Data Import thing... got that
now... still not working when you do not let the user define the file
to be opened...

 

[Dave Peterson]

Are all the files the same layout?

If yes, then maybe something like:

Option Explicit
Sub testme()

Dim myFileName As Variant

myFileName = Application.GetOpenFilename(filefilter:="Text Files, *.Txt", _
Title:="Pick a File")

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

Workbooks.OpenText Filename:=myFileName '....rest of recorded code here!

End Sub

You'll have to add all that .opentext stuff from your recorded macro.

 

[solomon_monkey]

Okay, this is bizarre- I can record the macro- it all works fine... the
cells are all good... I run the macro and it changes the formatting
around!!??
Is there some setting within Excel that I have active maybe??

 

[Dave Peterson]

What's the name of the file you import?

If it ends with .csv, change that extension first.

..CSV files get to ignore that VBA code.

 

[solomon_monkey]

It's a .txt file. I have recorded a macro opening it through Excel and
delimiting it and when I record it everything is fine... when I rerun
the macro it is some dates that are not... ???

 

[Dave Peterson]

What do you get for that Fieldarray() stuff?

Make sure you're specific with the date format.

And open your .txt file in Notepad to check to see if those dates are
consistent. (maybe a simple(?) data error??)