Copying from Access

  • Thread starter Thread starter T.
  • Start date Start date
T

T.

I always like to copy from Access into Excel. I do this a
lot and it's much faster than exporting the Access table
or query and then opening in Excel.

With the new XP version of Excel, my number formats are
all screwed up and come in as text from Access. The new
feature in Excel XP makes it nice to highlight a whole
range and fix them all at the same time, but if I have a
bunch of rows this takes forever and ties up my machine or
even crashes.

Can anyone help on how to make this work like it used to?
 
Instead of pasting, 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
'=============================
 
Back
Top