Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma Delimited Text File Issue
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma Delimited Text File Issue
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma Delimited Text File Issue
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... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma Delimited Text File Issue
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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma Delimited Text File Issue
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, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma Delimited Text File Issue
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. solomon_monkey wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma Delimited Text File Issue
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma Delimited Text File Issue
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... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma Delimited Text File Issue
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 wrote: 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma Delimited Text File Issue
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?? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma Delimited Text File Issue
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 wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma Delimited Text File Issue
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... ??? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comma Delimited Text File Issue
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??) solomon_monkey wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing Comma delimited file | Excel Discussion (Misc queries) | |||
How to produce comma-delimited and quoted text file from Excel? | Excel Discussion (Misc queries) | |||
How can I save a file as a comma-delimited text file in Excel? | Excel Discussion (Misc queries) | |||
how to save a file as ASCII: expanded comma delimited text (.exp). | Excel Discussion (Misc queries) | |||
I have a Comma delimited text file how do I import in to excel an. | Excel Discussion (Misc queries) |