excel vba 代码从远程网站上的表单获取信息，然后在工作表上打印该信息不起作用？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8814434/
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 vba code to get info from a form on a remote website in ie, and then print that info on worksheet isn't working?
提问by
Can you guys reading this please try my code and see if it works for you? It isn't entering a number on my worksheet but it worked for someone else. I have the code in a regular module (not class module, not worksheet module, etc.) in VBA in Excel 2010.
你们可以阅读这个请试试我的代码，看看它是否适合你？它没有在我的工作表上输入数字，但对其他人有用。我在 Excel 2010 的 VBA 中的常规模块（不是类模块，不是工作表模块等）中有代码。
First it should open IE and go to the webpage below. Then the code should enter the number 2688 (or some other 4-digit number) in cell A20 on the Active WorkSheet.
首先它应该打开IE并转到下面的网页。然后代码应在活动工作表上的单元格 A20 中输入数字 2688（或其他一些 4 位数字）。
Should prodID be dimmed as an object? And does it need object explicit at the top? or to be in a different type of module? Could something be wrong with my settings? Or why isn't it working for me?
prodID 应该作为对象变暗吗？它是否需要在顶部显式对象？或者在不同类型的模块中？我的设置可能有问题吗？或者为什么它不适合我？
Sub work_damit()
Dim ieApp As Object
Dim URL As String
Dim prodID As Object
URL = "http://www.craft-e-corner.com/p-2688-new-testament-cricut-cartridge.aspx"
Set ieApp = CreateObject("InternetExplorer.Application")
With ieApp
.Navigate URL
.Visible = True
Label1:
Application.Wait (Now() + TimeValue("0:00:10"))
On Error GoTo errorHandler:
Set prodID = .document.getElementByID("ProductID")
Range("A20").Value = prodID.Value
.Quit
End With
Exit Sub
errorHandler:
If Err.Number <> 462 Then
GoTo Label1:
End If
End Sub
采纳答案by Nitheen T
it works perfect for me. What i am thinking is, does your broswer loads with that time period?. if not you might get an error.
它非常适合我。我在想的是，您的浏览器是否加载了那个时间段？。如果不是，您可能会收到错误消息。
Istead of using
而不是使用
Application.Wait (Now() + TimeValue("0:00:10"))
use
用
Do While ieApp.Busy
DoEvents
Loop
回答by brettdj
You are relying on a set time for the web page to load, if it hasn't loaded in 10 seconds the code diverts to the errorhandler.
您依赖于网页加载的设定时间，如果在 10 秒内未加载，代码将转移到错误处理程序。
Updated code
更新代码
This version uses xmlhttpto get the data
此版本用于xmlhttp获取数据
Public Sub SidsCode()
'http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26686033.html
Dim objIE As Object
Dim objxmlhttp As Object
Dim strURL
On Error GoTo errhandler
strURL = "http://www.craft-e-corner.com/p-2688-new-testament-cricut-cartridge.aspx"
Set objIE = CreateObject("InternetExplorer.Application")
objIE.Navigate "about:blank"
Set objxmlhttp = CreateObject("Microsoft.xmlhttp")
With objxmlhttp
.Open "GET", strURL, False
objxmlhttp.setRequestHeader "Content-Type", "text/xml"
objxmlhttp.send
If .Status = 200 Then
objIE.document.write objxmlhttp.responseText
ActiveSheet.Range("A20").Value = objIE.document.getElementByID("ProductID").Value
Else
MsgBox "no reponse from site"
End If
End With
objIE.Quit
Set objIE = Nothing
Exit Sub
errhandler:
MsgBox "Code failed on" & vbNewLine & Err.Description
objIE.Quit
Set objIE = Nothing
End Sub
Original code
原码
Instead you can code the readystate of the page like so:
相反，您可以像这样对页面的就绪状态进行编码：
Sub work_damit()
Dim ieApp As Object
Dim URL As String
Dim prodID As Object
URL = "http://www.craft-e-corner.com/p-2688-new-testament-cricut-cartridge.aspx"
Set ieApp = CreateObject("InternetExplorer.Application")
With ieApp
.Navigate URL
Do While .readystate <> 4
DoEvents
Loop
Set prodID = .document.getElementByID("ProductID")
Range("A20").Value = prodID.Value
.Quit
End With
Set ie = Nothing
End Sub
