获取html源代码到Excel多行 [英] Obtain html source code to Excel multiple rows

问题描述

我对Excel VBA非常新鲜，我需要抓住网站的HTML。
这是一个困难的任务，我现在有一个解决方法 - 使用VBA获取整个HTML源代码到工作表，然后用Excel函数刮。

这是从其他网站获取整个HTML源代码到工作表的VBA代码：

  Sub ExtractWeb（） 
 
'引用Internet Explorer的运行副本
 Dim ie As InternetExplorer 
 
'引用返回的HTML文档
 Dim html作为HTMLDocument 
 
'在内存中打开Internet Explorer，然后转到网站
设置ie =新的InternetExplorer 
 ie.Visible = False 
 ie.navigatehttp：// www .google.com
 
'等待IE完成加载页
尽管ie.readyState<> READYSTATE_COMPLETE 
 DoEvents 
循环
 
'显示返回的HTML文档的文本
设置html = ie.document 
 
工作表（test） 。
 Range 
 $ b 
问题是：将包含多行的整个源代码粘贴到单个单元格中。 
但是我期望源代码的每一行都插入一行。 
我应该如何更正我的代码？
解决方案
 
  Dim arr 
 
 arr = Split（html.DocumentElement.outerHTML，vbLf）'或vbCR或vbCrLf 
 
工作表（test）。范围（A1）。调整大小（UBound（arr） +1，1）.Value = arr 
  
 
I am very new to Excel VBA and I need to scraping a website's HTML.
This is a difficult task to me now and I have a workaround - Using VBA to obtain the whole HTML source code to a worksheet, and then scraping with Excel functions.

Here is the VBA code from other sites which obtain the whole HTML source code to a worksheet:
Sub ExtractWeb()

'to refer to the running copy of Internet Explorer
Dim ie As InternetExplorer

'to refer to the HTML document returned
Dim html As HTMLDocument

'open Internet Explorer in memory, and go to website
Set ie = New InternetExplorer
ie.Visible = False
ie.navigate "http://www.google.com"

'Wait until IE is done loading page
Do While ie.readyState <> READYSTATE_COMPLETE
DoEvents
Loop

'show text of HTML document returned
Set html = ie.document

Worksheets("test").Select
Range("A1").Value = html.DocumentElement.outerHTML

End Sub
The problem is: The whole source code, which contains many lines, is pasted into a single cell.
However I expect that each line of the source code is inserted into a single row.
How should I correct my code?
解决方案
Dim arr

arr = Split(html.DocumentElement.outerHTML, vbLf) 'or vbCR or vbCrLf

Worksheets("test").Range("A1").Resize( UBound(arr)+1, 1 ).Value = arr


                        
这篇关于获取html源代码到Excel多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！