Hi,

See worksheet below:

| A | B |
--+----------+------------|
1 | 20051101 | 28/11/2005 |
--+----------+------------|
2 | 20051108 | |
--+----------+------------|
3 | 20051125 | |
--+----------+------------|
4 | 20051128 | |
--------------------------|

I want to convert every cell in column A to dd/mm/yyyy date format. I have done it for coumn B, but that is made easy by there only being on cell that needs changing:

Application.Goto Reference:=Worksheets("IntefaceSheet").Range("B1") , _
Scroll:=True

IDATE1 = ActiveCell
IDATE2 = Mid(IDATE1, 1, 4)
IDATE3 = Mid(IDATE1, 5, 2)
IDATE4 = Mid(IDATE1, 7, 2)
ActiveCell.FormulaR1C1 = IDATE2 + "/" + IDATE3 + "/" + IDATE4

Now I need to do the same thing for every cell that has a value in column A. This has to be done in Excel and not in Access. I have a long Excel macro that is going to create an interface file (interface.xls) for importing a dodgy CSV file into an Access 2003 DB, so therefore the formatting and data-type conversion all has to be done in Excel before the data is imported into Access 2003.

The code in the Access DB launches the Excel file+Macro, the data is then imported into Access and the blank values in column B are updated in Access so that it looks like this:

| pick_dt | inv_dt |
--+----------+------------|
| 20051101 | 28/11/2005 |
--+----------+------------|
| 20051108 | 28/11/2005 |
--+----------+------------|
| 20051125 | 28/11/2005 |
--+----------+------------|
| 20051128 | 28/11/2005 |
--------------------------|



So now I just need to convert the values in column A to a date, but in Access the field WILL ultimately be of data-type date/time, so it won't import a number. That is why it has to be converted in Excell first and then imported into Access.

Any ideas? I figure that I should load the range (A:A) as an index and then do the above function on each value in the index. But I am struggling to work out what the code is to create an index that loads a range of Excel cells and then loop through each and then performs the above funtion on every cell that has a value (Is Not Null). I am sure it is easy, but I seem to have developed a mental block. Maybe someon looking at it afresh will see their way round it more easily.

TIA,

Jarryd