Web自动化IFrame故障

Question

我目前在一家大医院的人力资源部当临时工。为了说服当权者有必要从"；试用版"；到"；工资+福利"；我的雇佣合同的订阅模式我决定承担一个项目。

excel电子表格将登录供应商网站，下载一份报告，其中包含目前正在休病假的员工名单，然后它将登录我们保险公司的网站，提取性病索赔信息供工资单使用(目前这是由一位70岁的女士手动完成的，她在30 WPM时打字……不，她甚至不使用基本的复制和粘贴(。

一切都很好，直到我点击按钮下载报告。我很难弄清楚下载电子表格的语法。改用chrome并使用Selenium不是一种选择，因为我们的IT部门的官僚作风令人眼花缭乱。

这是我目前掌握的代码。参考资料中包括Office 16.0 Office库、HTML对象库和microsoft internet控件。

模块1

Option Private Module
Public Function OpenReport(doc As HTMLDocument) As Long
On Error GoTo Failed
doc.getElementsByClassName("k-button k-button-icontext k-grid-view")(3).Click
OpenReport = 1
Done:
Exit Function
Failed:
OpenReport = 0
End Function

模块2

Dim IE As Object
Dim doc As HTMLDocument
Dim i As Long
Dim rWin As HTMLDivElement
Set IE = CreateObject("InternetExplorer.application")
IE.Visible = True
IE.navigate "https://leavexpert.com/"
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop
Set doc = IE.document
'inputs a username and password then clicks the login button
doc.getElementById("UserName").Value = "*****"
doc.getElementById("Password").Value = "*****"
doc.getElementsByClassName("validateAndSubmit k-button")(0).Click
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop
'having the program wait while IE.busy does not seem to work, so wrapping the button click in it's 
'own function and using error handling to return a 1 or 0 on success or failure is my creative work 
'around
IE.navigate "https://leavexpert.com/Reports"
i = 0
Do While i = 0
i = OpenReport(doc)
Application.Wait DateAdd("s", 1, Now)
Loop
Set rWin = doc.getElementsByClassName("k-widget k-window")(0)
'this gives me some output which tells me im on the right track.
Debug.Print rWin.className
Debug.Print rWin.innerHTML

调试输出

k-widget k-window k-state-focused
<div class="k-window-titlebar k-header" style="margin-top: -27px;">&nbsp;<span class="k-window-title" 
id="reportWindow_wnd_title">Case Detail</span><div class="k-window-actions"><a class="k-window-action 
k-link" role="button" href="#"><span class="k-icon k-i-maximize" role="presentation">Maximize</span> 
</a><a class="k-window-action k-link" role="button" href="#"><span class="k-icon k-i-close" 
role="presentation">Close</span></a></div></div><div tabindex="0" class="k-window-content k-content 
k-window-iframecontent" id="reportWindow" role="dialog" aria-labelledby="reportWindow_wnd_title" 
data-role="window"><div class="k-loading-mask" style="left: 0px; top: 0px; width: 100%; height: 
100%;"><span class="k-loading-text">Loading...</span><div class="k-loading-image"></div><div 
class="k-loading-color"></div></div><iframe title="Case Detail" class="k-content-frame" 
src="/Areas/Reports/ReportViewer.aspx?reportId=1005" frameborder="0">This page requires frames in 
order to show content</iframe></div><div class="k-resiz
e-handle k-resize-n"></div><div class="k-resize-handle k-resize-e"></div><div class="k-resize-handle 
k-resize-s"></div><div class="k-resize-handle k-resize-w"></div><div class="k-resize-handle k-resize- 
se"></div><div class="k-resize-handle k-resize-sw"></div><div class="k-resize-handle k-resize-ne"> 
</div><div class="k-resize-handle k-resize-nw"></div>

我需要点击下载的元素的HTML截图。

屏幕截图

更新：今天很慢，所以我有时间做一些测试。

以下是我的子末尾的一些更新代码

Do While i = 0
i = OpenReport(doc)
Application.Wait DateAdd("s", 1, Now)
Loop

Set doc = IE.document

'New stuff starts here, I'll move the declarations once I have it 
'working.
Application.Wait DateAdd("s", 30, Now)

Set doc = IE.document
Dim rWin As HTMLDivElement
Set rWin = doc.getElementsByClassName("k-widget k-window")(0)


For i = 0 To 11
Debug.Print rWin.getElementsByTagName("div")(i).innerText
Next


End Sub

新的调试输出。

Case Detail
MaximizeClose
MaximizeClose
This page requires frames in order to show content

Iframe屏幕截图

Answer 1

IT工作原理！！！！！它有效！！！！！它有效！！！！我不得不将IE设置为始终允许从该网站弹出，但在那之后。。。。。。。。。它成功了！！！！！！！！！！！！现在来了解如何处理打开/保存框。

'standard declarations
Dim IE As Object
Dim doc As HTMLDocument
Dim i As Long
Dim iWin As HTMLIFrame
Set IE = CreateObject("InternetExplorer.application")
IE.Visible = True
IE.navigate "https://leavexpert.com/"
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop
Set doc = IE.document
'This part puts in the login details and clicks the login button
doc.getElementById("UserName").Value = "*******"
doc.getElementById("Password").Value = "*******"
doc.getElementsByClassName("validateAndSubmit k-button")(0).Click
'wait for the website to finish loading
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop
'Go to the report section of the website
IE.navigate "https://leavexpert.com/Reports"
Set doc = IE.document

'Having the program wait while IE.busy does not seem to work, so wrapping the button click in it's own function
'and using error handling to return a 1 or 0 on success or failure is my creative work around
i = 0
Do While i = 0
i = OpenReport(doc)
Application.Wait DateAdd("s", 1, Now)
Loop
'Not quite sure if i need to set the doc variable here or not.  I read somewhere that you need to do it every time you change webpages.
'Don't know if opening a child window counts, but better safe than sorry.
Set doc = IE.document
'Set the iWin object to the report window.
Set iWin = doc.getElementsByClassName("k-content-frame")(0)
'Wait for the report to finish loading.  I know the program is looking at the correct object, because the debug printouts match when the window
'finishes loading.
Do While iWin.readyState <> "complete"
Application.Wait DateAdd("s", 1, Now)
Loop
Application.Wait DateAdd("s", 1, Now)
'This is the line that clicks the button!!!!!
iWin.contentDocument.querySelector("a[title=Excel][onclick*=EXCELOPENXML]").Click