import date field from access into excel

[Tony7777]

Hi,
I need to paste data into an excel sheet from access, and then sort b
date field. However when the date field is entered by paste functio
into an excel cell the formula from access is retained which prevent
searching on the date.
To solve this manually, I need to edit the cell (by F2 seems th
easiest) and then enter. This changes the formula into a searchabl
date.

My options are to either work out how to do the manual process above o
work out how to remove the formuala (I have tried copy and paste specia
as 'values' which didn't work)
If you can offer advice on how to create a loop via a macro to edi
each cell in a column and then 'enter/return' this would do it. I ca
create the macro, but what I don't know is how to code the loop t
select the next cell.....

Ton

 

[Tom Ogilvy]

if your dates have a slash ( 10/20/2004), then you don't need a macro.

select the column, Format as Date, then do
Edit=>Replace
What \
With \

and this should convert them to dates unless excel can not recognize them as
dates.

If you need a macro, turn on the macro recorder while you do it.

Another method is to select the column and do Data=>Text to columns, select
delimited and pick a delimiter not found in any of the cells.

 

[Frank Stone]

hi,
Access table don't have formula so i assume that you are
getting your data from a query which can have formaula.
(but that is just in the header.) Hmmmmmm.
Instead of copy and paste, i might suggest that you set up
a MSQ (microsoft query) Tools>get exteranl data> new query.
use the Access query as your data source for the MSQ.
And i think that will solve your date problem.
works for me.
Reqards
Frank