| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format error with CSV file
Hi,
Using Excel 2007 I have a csv file that when opened manually has the correct date format of DD/MM/YYYY When I open the file with VBA, the date format defaults to MM/DD/YYYY, some of the dates come through as text fields. Even importing this through VBA as a text file (with .csv extension) and changing the format to D/M/Y it still comes in as MM/DD/YYYY and some of the fields being text, when I change the extension to ".txt" it imports OK I do not want to have my code renaming the files to .txt in order to get the correct date format Any help on this would be greatly appreciated Regards Raymond Allan |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date format error with CSV file
On Jan 28, 10:22*pm, FmEdit wrote:
Hi, Using Excel 2007 I have a csv file that when opened manually has the correct date format of DD/MM/YYYY When I open the file with VBA, the date format defaults to MM/DD/YYYY, some of the dates come through as text fields. Even importing this through VBA as a text file (with .csv extension) and changing the format to D/M/Y it still comes in as MM/DD/YYYY and some of the fields being text, when I change the extension to ".txt" it imports OK I do not want to have my code renaming the files to .txt in order to get the correct date format Any help on this would be greatly appreciated Regards Raymond Allan Is this something you can fix by making the appropriate adjustment in your Regional Settings under Control Panel ? Otherwise you might have to manipulate all the date values in your VBA code. AP |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date format error with CSV file
On 28 Jan, 12:02, AP wrote:
On Jan 28, 10:22*pm, FmEdit wrote: Hi, Using Excel 2007 I have a csv file that when opened manually has the correct date format of DD/MM/YYYY When I open the file with VBA, the date format defaults to MM/DD/YYYY, some of the dates come through as text fields. Even importing this through VBA as a text file (with .csv extension) and changing the format to D/M/Y it still comes in as MM/DD/YYYY and some of the fields being text, when I change the extension to ".txt" it imports OK I do not want to have my code renaming the files to .txt in order to get the correct date format Any help on this would be greatly appreciated Regards Raymond Allan Is this something you can fix by making the appropriate adjustment in your Regional Settings under Control Panel ? Otherwise you might have to manipulate all the date values in your VBA code. AP- Hide quoted text - - Show quoted text - My regional settings are all set to UK format (default) The frustrating part is when it is manually opened, all the date fields are in the correct format, by using VBA to open the file it defaults to the US format and any date that has the day 12 becomes a text field RA |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date format error with CSV file
Il 28/01/2010 15.20, FmEdit ha scritto:
On 28 Jan, 12:02, wrote: On Jan 28, 10:22 pm, wrote: Hi, Using Excel 2007 I have a csv file that when opened manually has the correct date format of DD/MM/YYYY When I open the file with VBA, the date format defaults to MM/DD/YYYY, some of the dates come through as text fields. Even importing this through VBA as a text file (with .csv extension) and changing the format to D/M/Y it still comes in as MM/DD/YYYY and some of the fields being text, when I change the extension to ".txt" it imports OK I do not want to have my code renaming the files to .txt in order to get the correct date format Any help on this would be greatly appreciated Regards Raymond Allan Is this something you can fix by making the appropriate adjustment in your Regional Settings under Control Panel ? Otherwise you might have to manipulate all the date values in your VBA code. AP- Hide quoted text - - Show quoted text - My regional settings are all set to UK format (default) The frustrating part is when it is manually opened, all the date fields are in the correct format, by using VBA to open the file it defaults to the US format and any date that has the day 12 becomes a text field RA Menu Data - Text to Column (I think so in english version) -- Ciao BlackSun |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date format error with CSV file
On 28 Jan, 14:29, BlackSun wrote:
Il 28/01/2010 15.20, FmEdit ha scritto: On 28 Jan, 12:02, *wrote: On Jan 28, 10:22 pm, *wrote: Hi, Using Excel 2007 I have a csv file that when opened manually has the correct date format of DD/MM/YYYY When I open the file with VBA, the date format defaults to MM/DD/YYYY, some of the dates come through as text fields. Even importing this through VBA as a text file (with .csv extension) and changing the format to D/M/Y it still comes in as MM/DD/YYYY and some of the fields being text, when I change the extension to ".txt" it imports OK I do not want to have my code renaming the files to .txt in order to get the correct date format Any help on this would be greatly appreciated Regards Raymond Allan Is this something you can fix by making the appropriate adjustment in your Regional Settings under Control Panel ? Otherwise you might have to manipulate all the date values in your VBA code. AP- Hide quoted text - - Show quoted text - My regional settings are all set to UK format (default) The frustrating part is when it is manually opened, all the date fields are in the correct format, by using VBA to open the file it defaults to the US format and any date that has the day *12 becomes a text field RA Menu Data - Text to Column (I think so in english version) -- Ciao BlackSun- Hide quoted text - - Show quoted text - The text to column is my current workaround, I was hoping for a solution that would not invlove having to manipulate the data. RA |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date format error with CSV file
Hi,
Try this : Workooks.Open Filename:="c:\Excel\MyFile.csv",Local:=True "FmEdit" a écrit dans le message de groupe de discussion : ... Hi, Using Excel 2007 I have a csv file that when opened manually has the correct date format of DD/MM/YYYY When I open the file with VBA, the date format defaults to MM/DD/YYYY, some of the dates come through as text fields. Even importing this through VBA as a text file (with .csv extension) and changing the format to D/M/Y it still comes in as MM/DD/YYYY and some of the fields being text, when I change the extension to ".txt" it imports OK I do not want to have my code renaming the files to .txt in order to get the correct date format Any help on this would be greatly appreciated Regards Raymond Allan |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date format error with CSV file
On 28 Jan, 14:51, "michdenis" wrote:
Hi, Try this : Workooks.Open Filename:="c:\Excel\MyFile.csv",Local:=True Perfect, thank you very much Regards Raymond Allan |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| file format or file extension is not valid...error message | Excel Discussion (Misc queries) | |||
| How can I get date of file creation to XLS cell in date format? | Excel Worksheet Functions | |||
| Copy/Paste dates from CSV file - date format error? | Excel Programming | |||
| Concatenate error and Date Format Error | Excel Programming | |||
| Error:Invalid File format,while opening an Excel Template file | Excel Programming | |||
Linear Mode
