尝试从Excel中的VBA中提取一个值 [英] Trying to extract ONE value from a webpage with VBA in Excel
问题描述
我一直在试图找到这几天的信息，但是我发现的所有例子都有一小段代码，我需要这些代码。=）
我想做的是从首页中提取一个值，并将其放入Excel
中的单元格中（然后从同一网站上的另一个页面获取另一个值，然后放入下一个单元格等）
该页面是一个瑞典的证券交易所页面，我用作测试页面的页面是投资者B （ https://www.avanza.se/aktier/om- aktien.html / 5247 / investor-b ）
我感兴趣的是一个叫Senaste（这是页面 - 围绕它的信息）
< li>
< span class =XSText> Senast< br />< / span>
< span class =lastPrice SText bold>< span class =pushBox roundCorners3title =Senast uppdaterad：17:29:59> 248,60< / span>< / span> ;
< / li>
这是248,60的价值！
我有一些编码经验，但不是VBA脚本，在阅读了一些论坛帖子（大部分在这里）之后，我已经自己尝试了一些例子，但无法做任何工作。
由于我对VBA非常基础，所以我可能会遇到错误的结构，所以请基本和耐心等待我，这是我的测试，但我得到了运行时错误429
ActiveX组件可以不能创建对象
我可能完全错误的轨迹
Private Sub CommandButton1_Click（）
Dim ie As Variant
Set ie = CreateObject（InternetExplorer）
ie.navigatehttps://www.avanza.se/aktier/om-aktien .html / 5247 / investor-b
ie.Visible = True
Do
DoEvents
循环直到ie.readyState = READYSTATE_COMPLETE
Application.Wait（Now（） + TimeValue（00：00：016））'用于内部页面刷新或加载
Dim doc As Variant'需要从网页中提取的文档或数据的变量
设置doc = CreateObject（ HTMLDocument）
设置doc = ie.document
Dim dd As Variant
dd = doc.getElementsByClassName（lastPrice SText bold）（0）.innerText
MsgBox dd
End Sub
编辑：2014-05-12当前代码beeing testing 17:05
在按钮命令下
Private Sub CommandButton1_Click（）
Dim IE As Object
'创建InternetExplorer对象
设置IE = CreateObject（InternetExplorer.Application）
'您可以取消下一行查看表单结果
IE.Visible = False
'将表单数据发送到URL作为POST二进制请求
IE 。导航https://www.avanza.se/aktier/om-aktien.html/5247/investor-b
'状态栏
Application.StatusBar =正在加载，请稍候...
'等待IE加载...
'尽管IE.Busy
'Application.Wait DateAdd（s，1，Now）
'循环
'这应该从准备就绪
IEWait IE
Application.StatusBar =搜索值。请稍候...
'Dim document As HTMLDocument
'Set Document = IE.Document
Dim dd As Variant
dd = IE.Document.getElementsByClassName（lastPrice SText bold ）（0）.innerText
MsgBox dd
'显示IE
IE.Visible = True
'清理
Set IE = Nothing
设置objElement = Nothing
设置objCollection = Nothing
Application.StatusBar =
End Sub
而在module1
公开声明Sub Sleep Libkernel32（ByVal dwMilliseconds As Long）
公共功能IEWait（p_ieExp As InternetExplorer）
'这应该是从准备就绪的
Dim initialReadyState As Integer
initialReadyState = p_ieExp.ReadyState
'等待250 ms直到完成
尽管p_ieExp.Busy还是p_ieExp.ReadyState<> READYSTATE_COMPLETE
睡眠250
循环
结束功能
如前所述，我d不知道我是否拥有这个最新加载项的结构，而不是在这种编码中过期，我害怕。
最好的问候
停止编辑2014-05-12 17:08
你很近，但有一些小的错误。
这是我如何设置（测试）：
<$ p $
$ b'创建InternetExplorer对象
设置IE = CreateObject（InternetExplorer.Application）
'您可以取消下一行查看表单结果
IE.Visible = False
'从
获取数据的URL IE.Navigatehttps： /www.avanza.se/aktier/om-aktien.html/5247/investor-b
'状态栏
Application.StatusBar =正在加载，请稍候...
'等待IE加载...
尽管IE.Busy
Application.Wait DateAdd（s，1，Now）
循环
Application.StatusBar =搜索值，请稍候...
Dim dd As String
dd = IE.Document.getElementsByClassName（lastPrice SText bold）（0 ）.innerText
MsgBox dd
'显示IE
IE.Visible = True
'清理
设置IE = Nothing
Application.StatusBar =
End Sub
结果：
在Excel 2010中测试以下参考文献：
编辑 - 选项B
为了摆脱可能的运行时错误91尝试改变这样的几行：
Dim dd作为Variant
设置dd = IE.Document.getElementsByClassName（lastPrice SText bold）
MsgBox dd（0）.textContent
编辑 - 选项C
另一种获取元素的方法：
Dim tag
Dim tags As Object
Set tags = IE.Document.getEle mentsByTagName（*）
对于每个标签在标签
如果tag.className =lastPrice SText bold然后
MsgBox tag.innerText
退出
结束如果
下一个标签
（所有三种方法已经过测试Excel 2010和IE10）
I've been trying to find the information now for a couple of days, but all the examples I've found just has a small piece of the code, I need it all =)
What I want to do is to extract one value from a homepage and put it into a cell in Excel (and then take another value from another page on the same site and put in the next cell etc etc.)
The page is a swedish stock-exchange page, and the page I've used as a test-page is the stock for "Investor B" (https://www.avanza.se/aktier/om-aktien.html/5247/investor-b)
And the value I'm interested in is the one called "Senaste" (this is the page-information surrounding it)
<li>
<span class="XSText">Senast<br/></span>
<span class="lastPrice SText bold"><span class="pushBox roundCorners3" title="Senast uppdaterad: 17:29:59">248,60</span></span>
</li>
And it's the value 248,60 I'm after!
I got some coding experience, but not for VBA-scripting, after reading some forum-posts (mostly here), I've been trying out a few example by myself, but couldn't get any to work. Since I'm quite basic with VBA, I might have got the structure wrong, so please be basic and patient with me, this was my test, but I got "Runtime error 429" ActiveX component can't create object
I might be totally on the wrong track
Private Sub CommandButton1_Click()
Dim ie As Variant
Set ie = CreateObject("InternetExplorer")
ie.navigate "https://www.avanza.se/aktier/om-aktien.html/5247/investor-b"
ie.Visible = True
Do
DoEvents
Loop Until ie.readyState = READYSTATE_COMPLETE
Application.Wait (Now() + TimeValue("00:00:016")) ' For internal page refresh or loading
Dim doc As Variant 'variable for document or data which need to be extracted out of webpage
Set doc = CreateObject("HTMLDocument")
Set doc = ie.document
Dim dd As Variant
dd = doc.getElementsByClassName("lastPrice SText bold")(0).innerText
MsgBox dd
End Sub
EDIT: 2014-05-12 Current code beeing tested 17:05
under the button command
Private Sub CommandButton1_Click()
Dim IE As Object
' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")
' You can uncoment Next line To see form results
IE.Visible = False
' Send the form data To URL As POST binary request
IE.Navigate "https://www.avanza.se/aktier/om-aktien.html/5247/investor-b"
' Statusbar
Application.StatusBar = "Loading, Please wait..."
' Wait while IE loading...
'Do While IE.Busy
' Application.Wait DateAdd("s", 1, Now)
'Loop
'this should go from ready-busy-ready
IEWait IE
Application.StatusBar = "Searching for value. Please wait..."
' Dim Document As HTMLDocument
' Set Document = IE.Document
Dim dd As Variant
dd = IE.Document.getElementsByClassName("lastPrice SText bold")(0).innerText
MsgBox dd
' Show IE
IE.Visible = True
' Clean up
Set IE = Nothing
Set objElement = Nothing
Set objCollection = Nothing
Application.StatusBar = ""
End Sub
And in module1
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public Function IEWait(p_ieExp As InternetExplorer)
'this should go from ready-busy-ready
Dim initialReadyState As Integer
initialReadyState = p_ieExp.ReadyState
'wait 250 ms until it's done
Do While p_ieExp.Busy Or p_ieExp.ReadyState <> READYSTATE_COMPLETE
Sleep 250
Loop
End Function
As said earlier, I do not know if I got the structure right with this latest add-in, not to expired in this kind of coding I'm afraid.
Best Regards
Stop editing 2014-05-12 17:08
You are close but have a couple small errors.
Here is how I would set it up (Tested):
Private Sub CommandButton1_Click()
Dim IE As Object
' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")
' You can uncoment Next line To see form results
IE.Visible = False
' URL to get data from
IE.Navigate "https://www.avanza.se/aktier/om-aktien.html/5247/investor-b"
' Statusbar
Application.StatusBar = "Loading, Please wait..."
' Wait while IE loading...
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop
Application.StatusBar = "Searching for value. Please wait..."
Dim dd As String
dd = IE.Document.getElementsByClassName("lastPrice SText bold")(0).innerText
MsgBox dd
' Show IE
IE.Visible = True
' Clean up
Set IE = Nothing
Application.StatusBar = ""
End Sub
Results:
Tested in Excel 2010 with the following references:
Edit - Option B
To get rid of a possible "Run-Time Error '91'" try changing a few lines like this:
Dim dd As Variant
Set dd = IE.Document.getElementsByClassName("lastPrice SText bold")
MsgBox dd(0).textContent
Edit - Option C
Yet another way to get elements:
Dim tag
Dim tags As Object
Set tags = IE.Document.getElementsByTagName("*")
For Each tag In tags
If tag.className = "lastPrice SText bold" Then
MsgBox tag.innerText
Exit For
End If
Next tag
(All three methods have been tested on Excel 2010 and IE10)
这篇关于尝试从Excel中的VBA中提取一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
