VBA HTML不能在所有计算机上运行 [英] VBA HTML not running on all computers

问题描述

所以我有这个代码，我正在为我的deptarment进入一个网站输入数据，点击运行并将csv文件下载到工作表。它在我的电脑和我的个人资料在其他部门使用的电脑上工作正常。我们都使用相同版本的windows，excel和IE。当我有其他部门的人员运行宏时，它会打开网站，但不要将数据输入到字段中，尽管该网站与我登录时的编码完全相同。

  Sub testmetric（）
 
 
 
 Dim appIE As Object 
 Dim Obj As Object 
 
错误恢复下一步
 
表单（审核）。选择
 
 Selection.AutoFilter 
 
表单（审核）单元格.Clear 
 
 
 
 Application.ScreenUpdating = False 
 
 Application.ScreenUpdating = True 
设置appIE = CreateObject（InternetExplorer.Application ）
 
 
 sURL =http：//cctools/rportal/main.php？p = agentavaya
 
'指示宏打开IE并导航到SURL。 
与appIE 
 .Navigate sURL 
 .Visible = True 
 
 Application.Wait Now + TimeValue（00:00:02）
设置HTMLDOC = .Document 
结束
 
 
 
 Application.Wait Now + TimeValue（00:00:02）
 
 
 appIE.Document.getElementById（agentLob）selectedIndex =3
 
 appIE.Document.getElementById（timezone）。selectedIndex =1
 
 
 
 
 appIE.Document.getElementById（sdate）。Value = Range（Date_Start）
 
 
 appIE.Document.getElementById（ edate）Value = Range（Date_End）
 
 
 
 
 
 appIE.Document.getElementById（stenure）Value = Range （TenStart）
 
 appIE.Document.getElementById（etenure）。Value = Range（TenEnd）
 
 
 
 
 
 Application.Wait Now + TimeValue（00:00:02）
 
每个Btninput在appIE.Document.getElementsByTagName（INPUT）
如果Btninput。 Value =Run然后
 Btninput.Click 
例如它为
结束如果
下一个
 
 
 
 Application.Wait Now + TimeValue（00:00:02）
 
调用CSV 
 
 
 appIE.Quit 
 
表单（审核）。选择
范围（A1）。选择
贴纸（审核）。粘贴
 
 
 
 End Sub 
 
子CSV（）
 sCSVLink =http：// cctools / rportal / csvexport.php
 sfile =csvexport.php
 ssheet =Sheet10
 
设置wnd = ActiveWindow 
 
应用程序。 ScreenUpdating = False 
 
 
 Workbooks.Open文件名：= sCSVLink 
 
 Windows（sfile）.Activate 
 ActiveSheet.Cells.Copy 
 wnd .Activate 
范围（A1）。选择
表单（审核）。粘贴
 Application.DisplayAlerts = False 
 Windows（sfile）.Close False 
 Application.DisplayAlerts = True 
 Application.ScreenUpdating = True 
表单（审核）。选择
范围（A1）。选择
表格（审核）粘贴
 
 
 End Sub 
  

当这段代码由的成员r部门只是打开网站输入任何内容，不按网站上的RUN按钮。

有什么可能导致这个的想法？什么设置或什么我验证了PC的VBA引用都在那里，没有位置缺少路径。

解决方案

你永远不会检查网页是否已经完成加载！

您的 On Error Resume Next 语句也隐藏任何错误，并阻止您通过修复代码采取适当的补救措施。请报告带有错误的具体错误号和行。我有一个想法，哪一行和哪个错误，我的答案是为了：

在行上键入91错误：设置HTMLDOC = .Document

为什么？

当你等待 ， Application.Wait 是一种真正无效的方法来等待浏览器加载，并且2秒可能不总是足够的时间。假设您遇到类型91错误，我想到的最好的办法就是这样：

如何做好准备等待循环浏览器控件

基本上，您需要将线程放在循环中，直到浏览器加载页面。在伪代码中，您正在做：

 直到浏览器加载
 DoEvents等
循环
  

使用WinAPI休眠功能

您将需要使用WinAPI Sleep 函数（很多人使用 DoEvents 或 Application.Wait 但我发现这里睡眠是可取的。

因为它是一个 Declare ，你必须把它放在一个普通的模块中，它不能进入​​一个类或者用户模块。 p>

 '##这需要在一个普通的代码模块
公共声明Sub Sleep Libkernel32（ByVal dwMilliseconds As 
  

调用WinAPI休眠功能

 睡眠250'睡眠1/4秒，或睡眠1000睡眠一整秒等
  

将其全部放在一起

您将需要在 Do ... Loop > .Navigate 方法。

 '##这里进行导航方式：
使用appIE 
。导航sURL 
 
 Do 
睡眠250 
循环但不是.ReadyState<> 4'READYSTATE_COMPLETE 
 
设置HTMLDoc = .Document 
结束
  

如果仍然无效...

是的，总是有例外。我做了很少的基于Web的自动化，而我所做的大部分工作就是在这里修补东西来帮助人们。我注意到，越来越多的网站是动态服务的（AJAX也许？），而在这些情况下，浏览器可能是 .ReadyState = READYSTATE_COMPLETE 甚至 .Busy = False ，但 .Document 仍然是没有。

如果发生这种情况，你可以放一个二级循环，如：

  Dim numberOfAttempts 
尽管.Document不是
睡眠250 
 numberOfAttempts = numberOfAttempts + 1 
如果numberOfAttempts> 100然后
 MsgBox这太久了，再试一次。 
退出Do 
结束如果
循环
  

您可能想添加一个迭代器/计数器，就像我在那里做的那样，模拟一个超时，否则这可能会进入一个无限循环。

So I have this code I'm working on for my deptarment that goes to a website inputs data , clicks run and downloads the csv file to the worksheet. It works just fine on my PC and on my profile on the computers the other department uses. We are both using same versions of windows, excel , and IE. When i have someone from the other department run the macro it opens the website but never enters the data into the fields despite the site being the exact same coding as when i'm logged in.

Sub testmetric()



Dim appIE As Object
Dim Obj As Object

On Error Resume Next

Sheets("Audit").Select

Selection.AutoFilter

Sheets("Audit").Cells.Clear



Application.ScreenUpdating = False

Application.ScreenUpdating = True
Set appIE = CreateObject("InternetExplorer.Application")


sURL = "http://cctools/rportal/main.php?p=agentavaya"

' Instructes the macro to open IE and navigate to sURL.
With appIE
    .Navigate sURL
    .Visible = True

    Application.Wait Now + TimeValue("00:00:02")
     Set HTMLDOC = .Document
End With



Application.Wait Now + TimeValue("00:00:02")


appIE.Document.getElementById("agentLob").selectedIndex = "3"

appIE.Document.getElementById("timezone").selectedIndex = "1"




appIE.Document.getElementById("sdate").Value = Range("Date_Start")


   appIE.Document.getElementById("edate").Value = Range("Date_End")





   appIE.Document.getElementById("stenure").Value = Range("TenStart")

   appIE.Document.getElementById("etenure").Value = Range("TenEnd")





Application.Wait Now + TimeValue("00:00:02")

For Each Btninput In appIE.Document.getElementsByTagName("INPUT")
    If Btninput.Value = " Run " Then
        Btninput.Click
        Exit For
    End If
     Next



     Application.Wait Now + TimeValue("00:00:02")

Call CSV


appIE.Quit

Sheets("Audit").Select
Range("A1").Select
Sheets("audit").Paste



  End Sub

Sub CSV()
sCSVLink = "http://cctools/rportal/csvexport.php"
sfile = "csvexport.php"
ssheet = "Sheet10"

Set wnd = ActiveWindow

Application.ScreenUpdating = False


Workbooks.Open Filename:=sCSVLink

Windows(sfile).Activate
ActiveSheet.Cells.Copy
wnd.Activate
Range("A1").Select
Sheets("Audit").Paste
Application.DisplayAlerts = False
Windows(sfile).Close False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Sheets("Audit").Select
Range("A1").Select
Sheets("audit").Paste


End Sub

When this code is ran by a member of the other department it just opens the website inputs nothing and doesn't press the RUN button on the website.

Any ideas on what could be causing this? What setting or anything. I verified that both PC's VBA references in are there and no "Locations are missing paths" .

解决方案

You are never checking to see if the web page has finished loading!

Your On Error Resume Next statement is also hiding any errors and preventing you from taking the appropriate remedial action by fixing the code. Please report back with the specific error number and line which raises the error. I have an idea which line and which error, and my answer is intended for that:

Type 91 error on line: Set HTMLDOC = .Document

Why?

While you are waiting, Application.Wait is a really ineffective method to wait for the browser to load, and 2 seconds may not always be enough time anyways. The best way I can think of to handle this, assuming you're getting a type 91 error, is like so:

How to make a ready-waiting loop for Browser control

Basically you need to put the thread in a loop until the browser has loaded the page. In pseudo-code, you're doing:

Do Until Browser Is Loaded
   DoEvents 'etc.
Loop

Using the WinAPI Sleep Function

You will need to use the WinAPI Sleep function (a lot of people use DoEvents or Application.Wait but I have found here that Sleep is preferable.

Because it is a Declare, you must put it in an ordinary module, it can't go in a class or userform module.

'## This needs to go in an ordinary code module
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Calling the WinAPI Sleep Function

Sleep 250   'sleep for 1/4 of a second, or Sleep 1000 to sleep for a full second, etc.

Putting it all together

You will need to put a Do ... Loop that calls on this function immediately after the .Navigate method.

   '## This goes in where you do Navigate method:
    With appIE
        .Navigate sURL

        Do
            Sleep 250
        Loop While Not .ReadyState <> 4 'READYSTATE_COMPLETE

        Set HTMLDoc = .Document
    End With

If it still doesn't work...

Yes, there's always an exception. I do very little web-based automation, and most of what I do is just tinkering with stuff here to help people out. What I have noticed is that increasingly websites are served dynamically (AJAX maybe?) and that in these cases, the browser may be .ReadyState = READYSTATE_COMPLETE and even .Busy = False, yet the .Document is still Nothing.

IF that happens, you could put a secondary loop, like:

Dim numberOfAttempts
Do While .Document Is Nothing
    Sleep 250
    numberOfAttempts = numberOfAttempts + 1
    If numberOfAttempts > 100 Then 
        MsgBox "This is taking too long, try again later."
        Exit Do
    End If
Loop

You may want to add an iterator/counter as I have done there and simulate a timeout otherwise this could potentially go in to an infinite loop.

这篇关于VBA HTML不能在所有计算机上运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！