copying data from Access 2002 to Excel

[jrh]

In Access 97, I could open a query and copy all or certain
rows to paste into Excel. When I pasted them into Excel,
the numbers were pasted as numeric. With Access 2002 when
I paste numbers into excel, they are formatted at General
and I have to do a Text to Columns to format the data as
numeric.

Is there an option that I need to change?

 

[Debra Dalgleish]

AFAIK, there's no option setting that will change this behaviour.
However, instead of pasting the data from Access, you can choose
Edit>Paste Special, and choose csv or Text. Or, do a normal paste, and
then change the data back to numbers:

1. Select an empty cell on the worksheet
2. Choose Edit>Copy
3. Select the cells that you pasted from Access
4. Choose Edit>Paste Special
5. Select Add, click OK

If you do this frequently, you can use a macro to paste as csv:
'=======================
Sub PasteCSV()
ActiveSheet.PasteSpecial Format:="Csv", _
Link:=False, DisplayAsIcon:=False
End Sub
'========================

Or to convert the numbers:
'=============================
Sub ConvertToNumbers()
'by Jon Peltier
Cells(65535, 255).Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationAdd
End Sub
'=============================