Create index and perfrom function on each and every value.
 

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

Create index and perfrom function on each and every value.
 

Here is some code to do that bit

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
Cells(i, "A").Value = DateSerial(Cells(i, "A").Value \ 10000, _
(Cells(i, "A").Value Mod 10000) \ 100, Cells(i, "A").Value Mod 100)
Next i
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jarryd" wrote in message ...
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

Create index and perfrom function on each and every value.
 

Excellent!! Thanks a mill, Bob!

Jarryd
"Bob Phillips" wrote in message
...
Here is some code to do that bit

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
Cells(i, "A").Value = DateSerial(Cells(i, "A").Value \ 10000, _
(Cells(i, "A").Value Mod 10000) \ 100, Cells(i, "A").Value Mod
100)
Next i
End Sub



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jarryd" wrote in message
...
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