I have an excel file that is populated dynamically by an access database,
each time the excel file is opened. When I open the excel file, excel puts
an apostrophe in front of the dates in the first three columns, not allowing
users to sort properly on these dates. There are further columns containing
text, so it is not a problem.
I know how to run a macro when the user opens the excel file, but I do not
know how to write a macro that can:
1. Remove the apostraphe from the data and convert the data to date format.
2. Make sure that only the cells that are inserted from the database into
excel are converted, ie. all the cells bar cells in the first two rows as
these contain headings. How do I handle different ranges?
3. Deal with empty cells that have no data in them, to make sure they remain
blank, when the macro is run.

It would be really appreciated if anyone could help as I can't get my head
round the excel macros

cheers
matt

Sub aaatester2()
Dim rng As Range, cell As Range
If Application.TransitionNavigKeys = False Then
Set rng = ActiveSheet.UsedRange.Columns("A:C").SpecialCells( _
xlConstants, xlTextValues)
For Each cell In rng
If cell.PrefixCharacter = "'" Then
cell.Formula = cell.Value
cell.NumberFormat = "mm/dd/yyyy"
End If
Next
End If

End Sub


--
Regards,
Tom Ogilvy

"matt h" wrote in message
...
I have an excel file that is populated dynamically by an access database,
each time the excel file is opened. When I open the excel file, excel puts
an apostrophe in front of the dates in the first three columns, not
allowing
users to sort properly on these dates. There are further columns
containing
text, so it is not a problem.
I know how to run a macro when the user opens the excel file, but I do not
know how to write a macro that can:
1. Remove the apostraphe from the data and convert the data to date
format.
2. Make sure that only the cells that are inserted from the database into
excel are converted, ie. all the cells bar cells in the first two rows as
these contain headings. How do I handle different ranges?
3. Deal with empty cells that have no data in them, to make sure they
remain
blank, when the macro is run.

It would be really appreciated if anyone could help as I can't get my head
round the excel macros

cheers
matt