Copy a webpage's text to a single cell in Excel with VBA?

Question

I'm trying to use Excel VBA to pull an entire webpage (a news story) into a single cell. The problem is, when Excel outputs the website, every new line in the source text is placed in a new row. I'd like to know how to output it into one cell.

I have tried many methods, but they don't work because my version of Excel doesn't come with certain libraries? (My knowledge of computer science is limited.) I'm using a 2015 version of Excel on OS X. That's what I'm working with. The eventual goal of this whole project is for Excel to search a whole list of websites (a column of URLs) for a single term (stored in M5 right now), and output YES or NO which of the sites contain that term. For now, I'm trying it out on a single URL stored in E12.

Sub SearchSite()
strsearch = Range("M5")
theurl = Range("E12")

With ActiveSheet.QueryTables.Add(Connection:="URL;" & theurl, Destination:=Range("P1"))
.Name = "NewsQuery"
.AdjustColumnWidth = False
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=True
End With

Debug.Print "DONE"

End Sub

Answer 1

As I mentioned, perahps it's better to keep them all in separate rows. However, if you do need them to be in one cell only, you can use a second Sub to do so:

Sub combineCellsIntoOne(dest As Range)
Dim lastRow As Long
lastRow = Cells(Rows.Count, dest.Column).End(xlUp).Row

Dim i As Long
For i = dest.Row + 1 To lastRow
    dest.Value = dest.Value & " " & Cells(i, dest.Column).Value
    Cells(i, dest.Column).ClearContents
Next i

End Sub

You can put combineCellsIntoOne Range("P1") after the End With in your original one. That should work (note to change the destination range if/as necessary).