0

I've been trying to complete a code which requires me to paste data from a Ctrl+A/C'd webpage into a sheet. This is what I've done so far.

Sub testPaste()

Sheets("sheet2").Delete
Sheets.Add.Name = "Sheet2"
Application.DisplayAlerts = False

Dim wscel As Range
Set wscel = Sheets("sheet2").Range("A1")

Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
DataObj.GetFromClipboard

strPaste = DataObj.GetText

wscel.PasteSpecial (xlPasteValues)

End Sub

The code however gives me an error 1004. Removing (xlPasteValues) fixes it but it pastes the data as is which I can't work with. what is it that I'm doing wrong here?

Chronocidal
  • 6,827
  • 1
  • 12
  • 26
CheMBurN
  • 29
  • 3
  • 2
    Remove the brackets `wscel.PasteSpecial (xlPasteValues)` should be `wscel.PasteSpecial xlPasteValues` – Tom Dec 03 '19 at 16:21
  • @Tom In this case - a single `ByVal` argument - VBA doesn't distinguish between having or omitting the brackets. If you were using a `ByRef` argument or two arguments of any type, such as `wscel.PasteSpecial xlPasteValues, SkipBlanks:=True` then, yes, you would get an error from `wscel.PasteSpecial (xlPasteValues, SkipBlanks:=True)` – Chronocidal Dec 03 '19 at 16:45

1 Answers1

3

You are using Range.PasteSpecial, which applies to pasting Excel Formatted Range Objects - documentation found here. Unfortunately, what sits on your Clipboard isn't an Excel Range Object, so you need the Worksheet.PasteSpecial method instead.

If your clipboard contains, for example an HTML sample, then you can "Paste As Text" like so:

    wscel.Worksheet.Activate
    wscel.Activate
    wscel.Worksheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= False

And, yes - this is one of the few cases were you can't avoid using Select

Chronocidal
  • 6,827
  • 1
  • 12
  • 26
  • 1
    Good catch. Of course I noticed the extraneous parentheses, and totally missed the copy/paste from webpage. Upvoted. – BigBen Dec 03 '19 at 16:31