Paste HTML table into one cell (overflow in cells below it)

[excel_2009]

Hi excel gurus, I have an interesting use case, I am attempting to paste HTML table code into a single cell, however it appears to only paste a certain amount of information (assuming there's likely a max character limit for a single cell)

With the above in mind, is it somehow possible to paste the HTML into one sell and any subsequent spillover would be pasted into the cells below it? i.e

1 - The data is copied to the clipboard
2 - The VBA code pastes the data into multiple cells i.e. A1, A2, A3 etc dependent on how much overflow there is

Can this work, if so how?

Thank you!

 

[John_w]

Try this macro:

Public Sub Paste_Clipboard_To_Cells()

    Const MaxCharsInCell As Long = 32766
    Dim ClipboardText As String
    Dim i As Long
    
    ClipboardText = GetClipBoardText
    
    With ActiveSheet
        .Columns(1).Clear
        For i = 1 To Len$(ClipboardText) Step MaxCharsInCell
            .Range("A1").Offset(i \ MaxCharsInCell).Value = Mid$(ClipboardText, i, MaxCharsInCell)
        Next
    End With
    
End Sub

Private Function GetClipBoardText() As String
    On Error Resume Next
    GetClipBoardText = CreateObject("HTMLfile").ParentWindow.ClipboardData.GetData("Text")
End Function

 

[excel_2009]

Hi John,

Many thanks for the response, I've attempted to use the code however no html data is being pasted into the worksheet, perhaps it's due to using a Mac laptop?

I tried copying normal text too, but this still won't paste either (i've also tried copying 1 sentence and this too doesn't paste).

Do you have any ideas on what the issue(s) could be?

Thanks again for your time!

 

[John_w]

Yes, it's probably due to using Mac VBA. Sorry, but I don't know how to make it work.