activesheet.pastespecial  WITHOUT A SELECT when text is on clipboard

Posted on 2012-08-23
Last Modified: 2012-08-24
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.
Question by:rberke
    LVL 39

    Expert Comment

    How about just
    ActiveSheet.cells(3,1).Value = OBJ.GetText(1)

    Open in new window

    LVL 33

    Accepted Solution


    Open in new window

    LVL 5

    Author Comment

    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
    LVL 5

    Author Closing Comment

    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  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

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now