使用VBA将网页的文本复制到Excel中的单个单元格吗? [英] Copy a webpage's text to a single cell in Excel with VBA?

问题描述

我正在尝试使用Excel VBA将整个网页(新闻故事)拉入单个单元格中.问题是，当Excel输出网站时，源文本中的每个新行都将放置在新行中.我想知道如何将其输出到一个单元格中.

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.

我尝试了许多方法，但是它们不起作用，因为我的Excel版本没有某些库吗?(我对计算机科学的知识是有限的.)我正在OS X上使用2015年版本的Excel.这就是我正在使用的.整个项目的最终目标是让Excel搜索单个词(现在存储在M5中)的整个网站列表(URL列)，并输出YES或NO哪个站点包含该词.现在，我正在对存储在E12中的单个URL进行尝试.

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

推荐答案

正如我所提到的，perahps最好将它们全部放在单独的行中.但是，如果确实需要它们仅位于一个单元格中，则可以使用第二个Sub来做到这一点:

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

您可以将 combineCellsIntoOne Range("P1")放在原始代码的 End With 之后.那应该起作用(请注意，如有必要，请更改目标范围).

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

这篇关于使用VBA将网页的文本复制到Excel中的单个单元格吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！