tnauser
asked on
Problem with reading date in uk format dd/mm/yyyy from excel spreadsheet
I am writing a macro in visual basic to import records from a .csv file into the spreadsheet from where the macro is initiated. Column A in the csv contains a date which is used to decide which sheet in the spreadsheet the record is imported into, there is a sheet per month. The date format in the csv is a standard uk format dd/mm/yyyy.
To start with the import runs as it should until the first two numbers of the date in column A of the csv drop to 12 or below at which point excel decides it will start using the american format mm/dd/yyyy. So to import to the appropriate sheet I am reading the month from the cell in column A (examples below). The first five dates will be interpreted correctly as Month(activecell) = 10 but with the last two it interprets Month(activecell) = 12.
14/10/2005
13/10/2005
13/10/2005
13/10/2005
13/10/2005
12/10/2005
12/10/2005
If I use:
Set objImpExcel = CreateObject("Excel.Applic
objImpExcel.Workbooks.Open
objImpExcel.ActiveSheet.Co
or
objImpExcel.ActiveSheet.Co
it doesn't make a difference. If I open the csv and format column A as mm/dd/yyyy and then re-run the import it uses a consistent date format the whole way through and the import works as correctly.
Is there a way round this using visual basic as I don't want to have to open the csv file manually everytime I need to import records.
Thanks
To start with the import runs as it should until the first two numbers of the date in column A of the csv drop to 12 or below at which point excel decides it will start using the american format mm/dd/yyyy. So to import to the appropriate sheet I am reading the month from the cell in column A (examples below). The first five dates will be interpreted correctly as Month(activecell) = 10 but with the last two it interprets Month(activecell) = 12.
14/10/2005
13/10/2005
13/10/2005
13/10/2005
13/10/2005
12/10/2005
12/10/2005
If I use:
Set objImpExcel = CreateObject("Excel.Applic
objImpExcel.Workbooks.Open
objImpExcel.ActiveSheet.Co
or
objImpExcel.ActiveSheet.Co
it doesn't make a difference. If I open the csv and format column A as mm/dd/yyyy and then re-run the import it uses a consistent date format the whole way through and the import works as correctly.
Is there a way round this using visual basic as I don't want to have to open the csv file manually everytime I need to import records.
Thanks
always a problem. I'm in Oz.
can you get the program that creates the CSV to use dd/mmm/yyyy ?
only other way is to import the CVS line by line as a text file.
Use split to break into the cells. You can then treat the date as special........
It's slower but should work.
can you get the program that creates the CSV to use dd/mmm/yyyy ?
only other way is to import the CVS line by line as a text file.
Use split to break into the cells. You can then treat the date as special........
It's slower but should work.
ASKER
Thanks for the response. Unfortunately I have no control over the date format the csv arrives in.
Also the code is fairly slow at the moment because for every every record in the csv I have to cycle-through all the rows in the corresponding sheet for that month checking each row to make sure the record doesn't exist and if not add it in at the bottom. There may be a more efficient way of doing this but I'm not too sure how. Is there a collection object that you can use instead of cycling through rows using offset?
Thanks
Also the code is fairly slow at the moment because for every every record in the csv I have to cycle-through all the rows in the corresponding sheet for that month checking each row to make sure the record doesn't exist and if not add it in at the bottom. There may be a more efficient way of doing this but I'm not too sure how. Is there a collection object that you can use instead of cycling through rows using offset?
Thanks
You could make a temporary csv based on the original csv, but only all dates are in "mm/dd/yyyy"-format, this should solve the problem with Excel.
You can use the Dictionary Object, it is like a Collection Object, but has alot more features and is alot faster.
You have to make a reference to the Microsoft Scripting Runtime before you can use Dictionary Objects though.
Description
Object that stores data key, item pairs.
Syntax
Scripting.Dictionary
Remarks
A Dictionary object is the equivalent of a PERL associative array. Items, which can be any form of data, are stored in the array. Each item is associated with a unique key. The key is used to retrieve an individual item and is usually a integer or a string, but can be anything except an array.
The following code illustrates how to create a Dictionary object:
Dim d 'Create a variable
Set d = CreateObject(Scripting.Dic
d.Add "a", "Athens" 'Add some keys and items
d.Add "b", "Belgrade"
d.Add "c", "Cairo"
...
I hope this helps!
Osmodean
You can use the Dictionary Object, it is like a Collection Object, but has alot more features and is alot faster.
You have to make a reference to the Microsoft Scripting Runtime before you can use Dictionary Objects though.
Description
Object that stores data key, item pairs.
Syntax
Scripting.Dictionary
Remarks
A Dictionary object is the equivalent of a PERL associative array. Items, which can be any form of data, are stored in the array. Each item is associated with a unique key. The key is used to retrieve an individual item and is usually a integer or a string, but can be anything except an array.
The following code illustrates how to create a Dictionary object:
Dim d 'Create a variable
Set d = CreateObject(Scripting.Dic
d.Add "a", "Athens" 'Add some keys and items
d.Add "b", "Belgrade"
d.Add "c", "Cairo"
...
I hope this helps!
Osmodean
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks very much robberbaron.
That worked straight away.
That worked straight away.
After some research I came across a document that stated that Access uses the "mm/dd/yyyy"-format internally for some obscure reason and that you cant change that.
I think Excel uses the same logic, so I am afraid you cant get around it.
I hope this helps a bit,
Osmodean