• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 975
  • Last Modified:

activesheet.pastespecial WITHOUT A SELECT when text is on clipboard

When the clipboard contains only text the following code will paste it onto the desired cell.

    Dim OBJ As New DataObject
    OBJ.SetText "TEST"
    OBJ.PutInClipboard ' this is just an example, the actual clipboard might have text and tabs and linefeeds etc
    ActiveSheet.Cells(2, 1).Select
WORKS: ActiveSheet.PasteSpecial Format:="Text"

Open in new window

But, I don't like using Select statements.  Is there anyway to do it completely without a select in Excel 2003? The following code fails with runtime error 1004

FAILS: ActiveSheet.Cells(3, 1).PasteSpecial Paste:=xlPasteValues

and, NO, I don't want to parse the clipboard text myself.

Also, I notice that the .PasteSpecial command automatically Selects what it has pasted, so I my ideal solution would eliminate that select also.
  • 2
1 Solution
How about just
ActiveSheet.cells(3,1).Value = OBJ.GetText(1)

Open in new window


Open in new window

rberkeAuthor Commented:
nutsch:  "and, NO, I don't want to parse the clipboard text myself"  because it will have lots of tabs and linefeeds and other control characters.

imnorie: surprisingly, that worked.  When the clipboard has Text,  Excel does not allow me to say "paste it as text".    Of course, that only solves half my problem, because the pastespecial operation does its own Select after running.  I will probably just save the original selection, then reselect it when done. Also, since the clipboard might sometimes have cells on it, I will probably put in some code to test the clipboard mode.  I seem to remember something similar to CutCopyMode that would let me know if the clipboard had text instead of cells.  If someone can remeber that, please let me know, otherwise I will use
on error resume next
rng.pastespecial xlpastevalues
if err <> 0 then
  if err <> 0 then msgbox "could not paste"
end if
on error goto 0
rberkeAuthor Commented:
imnorie's solution works so they get the points.
Nonetheless, I have chosen a different solution.
Sub paste_plaintext_to_Selection()
' you must first select the target cell (Only the first cell of your selection)
' this overlays a target area using as many adjacent cells as required.
' the clipboard may contain a scattered selection of Excel Cells or maybe simple text from another application
' The following code from williamc at MrExcel.com  behaves like the menu function for paste, however if the source is a copy either from within Excel or from the Windows clipboard, the paste operation inserts plain text
On Error Resume Next
Selection.Cells(1).Select ' Only the first cell of target matters
Select Case Application.CutCopyMode
Case xlCut:
Application.CutCopyMode = False
Case xlCopy:
Selection.PasteSpecial Paste:=xlFormulas, Operation:=xlNone, SkipBlanks:=True
On Error GoTo 0
Case Else:
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
End Select
End Sub

Open in new window

Of course, you will notice that my chosen solution DOES require I use .select. I have decided that trying to avoid .select was silly in this situation. After all, the paste operation will automatically change the selection anyhow.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now