Transfer text from Access table into Excel

T

tim.carne

Hi All,
I'm attempting to transfer a date I have stored in a table in Access,
into Excel.
The export works fine, however when I view the date in Access, it is
presented in the format
'dd/mm/yyyy
Hence, this is not recognised as a date in Excel and referenced sheets
to this cell don't display correctly.
I can manually get rid of the ' character, however I'm trying to
implement a macro that would do this - I used a standard search and
replace macro, however it can not find any ' characters.
When I manually use Search and Replace, it also is unable to locate
this character in the entire spreadsheet.
Does anyone have any suggestions of how best I could work around
this..?

Cheers
Tim.
 
E

Earl Kiosterud

Tim,

You can put in a helper column with =DATEVALUE(A2). You may get numbers up in the 10's of
thousands, which are the actual date values for the dates. If so, format the column for a
date (Format - Cells - Number tab). Check that the dates you get are correct for the
original data.

To make this change permanent, copy the helper column, then paste it in any column
(including itself) with Paste Special - Values. Then you can delete the other column --
it's no longer needed.

You'll have to repeat this process if you import data again. If you'll be doing it
regularly, a macro solution might be better, if you're willing. To import the data, you
might want to use Data - Get External Data - Import data. You need set that up only once,
then you can refresh the data any time you want, and it will read the Access table or query
into the worksheet, tossing the old data out.
 
Top