Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening .CSV file
I have a .CSV file which i am opening through VBA code:
set wb = workbooks.open(Filepath) When I open the file the first columns reads: 19/04/10 19/04/10 19/04/10 When I open the file via the file menu (File | Open) the first column reads: 19/04/2010 19/04/2010 19/04/2010 What do I have to do to be able to open the file via VBA and have the column 1 values seen as they appear when the file is opened normally? PWS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening .CSV file
When you use File|Open, excel will use your windows regional settings -- and you
have your date format set to dmy. But when VBA opens the file, it doesn't use your regional setting. It's USA centric and uses an mdy order. And since 19/04/10 doesn't look like a date in mdy order, excel will treat it as text. You may have noticed that some data comes in ok. But it's not! It may look like it's a date, but it's not the date that the CSV file wants. 05/06/10 would come in as May 6, 2010 -- not June 6, 2010 (what the data really meant). So don't just try to fix the data after importing it. Instead, rename/copy the .CSV file to .TXT. And then record a macro when you open that file manually. Excel will show you the text to columns wizard and you'll be able to specify that field as a date (dmy order). Then incorporate that recorded code into your existing macro. But you'll have to rename/copy that CSV file (either manually or via code) before this modified code runs. Paul W Smith wrote: I have a .CSV file which i am opening through VBA code: set wb = workbooks.open(Filepath) When I open the file the first columns reads: 19/04/10 19/04/10 19/04/10 When I open the file via the file menu (File | Open) the first column reads: 19/04/2010 19/04/2010 19/04/2010 What do I have to do to be able to open the file via VBA and have the column 1 values seen as they appear when the file is opened normally? PWS -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening .CSV file
Or I could just add the Local= TRUE :
set wb = workbooks.open(FileName:=Filepath, Local:=True) I answer my own question in case away seaches my original question and wants another solution. "Dave Peterson" wrote in message ... When you use File|Open, excel will use your windows regional settings -- and you have your date format set to dmy. But when VBA opens the file, it doesn't use your regional setting. It's USA centric and uses an mdy order. And since 19/04/10 doesn't look like a date in mdy order, excel will treat it as text. You may have noticed that some data comes in ok. But it's not! It may look like it's a date, but it's not the date that the CSV file wants. 05/06/10 would come in as May 6, 2010 -- not June 6, 2010 (what the data really meant). So don't just try to fix the data after importing it. Instead, rename/copy the .CSV file to .TXT. And then record a macro when you open that file manually. Excel will show you the text to columns wizard and you'll be able to specify that field as a date (dmy order). Then incorporate that recorded code into your existing macro. But you'll have to rename/copy that CSV file (either manually or via code) before this modified code runs. Paul W Smith wrote: I have a .CSV file which i am opening through VBA code: set wb = workbooks.open(Filepath) When I open the file the first columns reads: 19/04/10 19/04/10 19/04/10 When I open the file via the file menu (File | Open) the first column reads: 19/04/2010 19/04/2010 19/04/2010 What do I have to do to be able to open the file via VBA and have the column 1 values seen as they appear when the file is opened normally? PWS -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening .CSV file
That'll depend on what versions of excel you're supporting.
Paul W Smith wrote: Or I could just add the Local= TRUE : set wb = workbooks.open(FileName:=Filepath, Local:=True) I answer my own question in case away seaches my original question and wants another solution. "Dave Peterson" wrote in message ... When you use File|Open, excel will use your windows regional settings -- and you have your date format set to dmy. But when VBA opens the file, it doesn't use your regional setting. It's USA centric and uses an mdy order. And since 19/04/10 doesn't look like a date in mdy order, excel will treat it as text. You may have noticed that some data comes in ok. But it's not! It may look like it's a date, but it's not the date that the CSV file wants. 05/06/10 would come in as May 6, 2010 -- not June 6, 2010 (what the data really meant). So don't just try to fix the data after importing it. Instead, rename/copy the .CSV file to .TXT. And then record a macro when you open that file manually. Excel will show you the text to columns wizard and you'll be able to specify that field as a date (dmy order). Then incorporate that recorded code into your existing macro. But you'll have to rename/copy that CSV file (either manually or via code) before this modified code runs. Paul W Smith wrote: I have a .CSV file which i am opening through VBA code: set wb = workbooks.open(Filepath) When I open the file the first columns reads: 19/04/10 19/04/10 19/04/10 When I open the file via the file menu (File | Open) the first column reads: 19/04/2010 19/04/2010 19/04/2010 What do I have to do to be able to open the file via VBA and have the column 1 values seen as they appear when the file is opened normally? PWS -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for Opening Reuters, Opening Excel File, Closing Excel File | Excel Programming | |||
opening a file in Excel starts application but dose not open file | Excel Discussion (Misc queries) | |||
File:1 and File:2 -- Double Files when Opening One File | Excel Discussion (Misc queries) | |||
opening an excel file opens a duplicate file of the same file | Excel Discussion (Misc queries) | |||
Error:Invalid File format,while opening an Excel Template file | Excel Programming |