Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dates format | Excel Worksheet Functions | |||
CONVERT DATES FROM EUROPEAN FORMAT TO US FORMAT | New Users to Excel | |||
Format text 'dates' to real dates | Excel Worksheet Functions | |||
Format dates | Excel Discussion (Misc queries) | |||
the dates on cell format make different dates. | New Users to Excel |