Pasting data into Excel from Access

[Paul Chauvin]

When pasting data from Access into Excel, columns of
numbers cannot be autosummed even though the cell format
shows the format as general. Any attempt to change the
format of those cells has no effect on the data in the
cells. Is there a way to correct that?

 

[matt b]

Have the same problem, quick way round it is to paste data
into notepad then save as text doc. Then use excel to read
it and import following wizard. Probably a much easier
answer but this works in short term. Got to be a bug I
think

 

[Dave Peterson]

Try selecting an empty cell
copy it
select your range of offending "numbers"
Edit|Paste special|check Add under operations

Just formatting your cell to general, doesn't change the value in the cell. (If
it was "text" numbers, it's still text numbers. But the next time you change
the cell, xl will see it as a number.)

You could format the cells as General. Then hit F2, enter for each cell. But
the copy|paste special|Add is much, much quicker.

 

[Debra Dalgleish]

If you copy from Access 2002 and paste into Excel, the numbers are
pasted in as text. You'll have to change them back to numbers before you
can format them:

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 convert the numbers.
I use this one (written by Jon Peltier):

Sub ConvertToNumbers()
Cells(65535, 255).Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlPasteSpecialOperationAdd
End Sub