Copying Data From SQL Into Excel

L

Lonnie Schwindt

When I copy a range of data from SQL and paste it into
Excel, the data doesn't appear to be available for
formulas - in this case a VLOOKUP formula. However, once
I click in the formula bar (as if to edit the data) then
hit "return" the data is "magically" available for the
VLOOKUP formula. It seems to me to be a format problem,
but changing the format of the data doesn't help. Could
this be related to similar issues when copying data from
Access into Excel? Are there any workarounds?
 
D

Debra Dalgleish

It sounds similar to the Access problem. 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
'=============================
 
L

Lonnie Schwindt

Thanks for the help - I'll test it out!

- Lonnie
-----Original Message-----
It sounds similar to the Access problem. 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
'=============================




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

.
Click to expand...
 
Top