vba Excel宏将网络数据放入单个单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18956479/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel macro to get web data into single cell
提问by user2605793
I have a spreadsheet with a list of URLs in column A. I want to read each URL and store the output in a single cell, or at least into the same row as the URL. I am currently using the following standard VBA code to do each read (simplified here to use a static URL).
我有一个包含 A 列中 URL 列表的电子表格。我想读取每个 URL 并将输出存储在单个单元格中，或者至少存储在与 URL 相同的行中。我目前使用以下标准 VBA 代码来执行每次读取（此处简化为使用静态 URL）。
The problem is Excel is automatically separating the data into multiple rows and multiple columns, depending on the format of the web page (which is obviously what would usually be wanted).
问题是 Excel 会自动将数据分成多行多列，具体取决于网页的格式（这显然是通常需要的格式）。
Is there any simple way of modifying that code to force the output into a single cell or row please?
请问有什么简单的方法可以修改该代码以强制输出到单个单元格或行中吗？
Sub FetchData()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.someurl", Destination:=Range("$B"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
Don't worry, I managed to figure it out using code from here: Fetch specific table only from website into Excel
别担心，我设法使用这里的代码弄清楚了： 仅从网站获取特定表到 Excel
My version below assumes column A contains a list of URLs, and columns B-I contain other data. I want to read the web page for each URL from column A and pull out 2 fields from a table and put them in columns J and K (columns 10 and 11). The fields are named ID="Name1" and ID="Name2" in the web pages.
我下面的版本假设 A 列包含一个 URL 列表，而 BI 列包含其他数据。我想从 A 列中读取每个 URL 的网页，并从表中提取 2 个字段并将它们放在 J 和 K 列（第 10 和 11 列）中。这些字段在网页中被命名为 ID="Name1" 和 ID="Name2"。
Sub Getfromweb()
Dim RowNumb As Long
Dim LastRow As Long
Dim htm As Object
Set htm = CreateObject("htmlFile")
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For RowNumb = 1 To LastRow
With CreateObject("msxml2.xmlhttp")
.Open "GET", Sheets(1).Cells(RowNumb, 1), False
.send
htm.body.innerhtml = .responsetext
End With
With htm.getelementbyid("Name1")
Sheets(1).Cells(RowNumb, 10).Value = .innertext
End With
With htm.getelementbyid("Name2")
Sheets(1).Cells(RowNumb, 11).Value = .innertext
End With
Next RowNumb
End Sub
采纳答案by user2605793
Apparently it relates to Text to Columns. If that has been used previously and a blank was specified as the separator then it gets applied to future data imports. Weird. Anyway just go into Text to Columns and remove the blank separator and accept, then redo the data import.
显然它与文本到列有关。如果之前已经使用过并且指定了空格作为分隔符，那么它将应用于未来的数据导入。奇怪的。无论如何，只需进入 Text to Columns 并删除空白分隔符并接受，然后重做数据导入。
