☰

Excel-将文本从网址复制到单元格

tag-icon • tag-icon microsoft-excel web url import
Excel-将文本从网址复制到单元格

我有一个包含如下 URL 的 Excel 表格：

|        column A        |        column B        |
---------------------------------------------------
| www.example.com/page1  |
| www.example.com/page2  |

我想将 A 列中提供的 URL 中的 html 内容复制到 B 列中。现在我已经找到了一些关于如何使用 VB 导入 web 内容的文档（https://msdn.microsoft.com/en-us/library/aa203721%28v=office.11​​%29.aspx）

编辑：抱歉，我已经设法使用上述来源实现了部分查询。我现在可以从 URL 中提取内容，并遍历 URL 列表：

Sub WebData()

Dim wSU As Worksheet
Dim wSR As Worksheet
Dim wSS As Worksheet

Dim iForRow As Integer
Dim iLastRow As Integer
Dim sURL As String

Set wSU = ThisWorkbook.Sheets("URLs")
Set wSR = ThisWorkbook.Sheets("Results")
Set wSS = ThisWorkbook.Sheets("Scrape")

Application.ScreenUpdating = False
iLastRow = wSU.Cells(wSU.Rows.Count, "a").End(xlUp).Row
For iForRow = 1 To iLastRow Step 1
sURL$ = wSU.Cells(iForRow, "a").Value
With wSS.QueryTables.Add(Connection:= _
"URL;" & sURL, Destination:=wSS.Range("A1"))
.Name = _
"example.aspx"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlNone
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
wSR.Cells(iForRow + 1, "a").Value = wSS.Range("A3").Value + "<br>" +    wSS.Range("A5").Value

Next iForRow
Application.ScreenUpdating = True
MsgBox "Process Completed"
End Sub

有没有办法将源的原始 html 导入 excel？现在它将 web 内容转换为纯文本，但我想保留 html 标记。我已经尝试将 .WebFormatting 和 .WebPreFormattedTextToColumns 设置为 none，但没有成功

相关内容