VBA: Getting clipboard data (html web source) to paste into a specified cell.

[greenmonster15]

Goal:

I have data that was copied to my clipboard from the webpage source in a Chrome browser. I would like to get that data over to my excel worksheet and insert it starting at "A1".

Issue:

All of the pasted data is ending up in ONLY cell "A1" when using VBA (probably because it is a better listener than I am a programmer)
When I just click in cell "A1" and CTRL-V, the data gets spread across a lot of cells, which is what I am after.

'------------------------------------------
'Start The Process
'------------------------------------------

[B]'  Assigning clipboard data to string variable strClip

    Dim MyData As DataObject
    Dim strClip As String


    Set MyData = New DataObject
    MyData.GetFromClipboard
    strClip = MyData.GetText[/B]

'---------------------------------------------------------


    Set TempSheet = Worksheets("Temp")
    Set MainSheet = Worksheets("Sheet1")
    
    Application.DisplayAlerts = False
    TempSheet.Delete
    Set NewSheet = Application.Worksheets.Add(After:=Worksheets(Worksheets.Count)) ' "Sheet1"))
    NewSheet.Name = "Temp"
    Application.DisplayAlerts = True
    Set TempSheet = Nothing
    Set TempSheet = Worksheets("Temp")
    
'  Pasting website source data from clipboard to Temp sheet to be evaluated


    Dim FirstCell As Range ' First cell to contain desired data to extract
    Dim LastCell As Range  ' Last cell to contain desired data to extract
    Dim TargetRng As Range 
    Dim lngLastRow As Long
    Dim lngLastCell As Long
    
    
  [B]  TempSheet.Activate
    TempSheet.Cells(1, 1).Activate
    ActiveCell.Value = strClip[/B]

 

[wigi]

Hi there

Please have a look at the following:

Sub test()

    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Temp").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    With Sheets.Add(After:=Sheets(Sheets.Count))
        .Name = "Temp"
        .Paste
    End With


End Sub

 

[greenmonster15]

Okay!!!! So I replaced my code starting at Application.DisplayAlerts with your code and it worked beautifully. Can I remove the DataObject code (and references to it) that I was trying to use to pull the clipboard data into a variable and then insert? When I stepped in with F8, it looks like I get what I need well before I get to that code.

Last question. Why did that work? Or why didn't mine?

 

[wigi]

Yes, you can just get rid of your code and use mine

 

[greenmonster15]

Well the code works great when I just F8 and step into it. Fills the worksheet like I'd like. When I actually run the Macro though, it comes back with a "Paste Method of Worksheet Class failed." Highlighting the .Paste in my VBA. Yet I can just F8 right on through it and it pastes just fine.

Any thoughts? This is part of an automation project, so it's going to need to run over-and-over with out me having to F8 for days.

 

[solsonoly]

I know this is an old thread but I was having the same problem and thought it might help others stumbling across this. I also got a paste method error like greenmonster15. In my case I was copying a chrome webpage and then pasting it into Excel. I needed to add a application.wait line with a 1 second delay. In my case it appears windows needed a little longer to copy the information to the clipboard before it would allow me to paste.

 

[B___P]

Try to activate the destination sheet just before the paste method. May be, this way, excel knows where to put data.

bye