Web自动化IFrame故障

Question

我目前在一家大医院的人事部做临时工。为了说服那些需要从“试用版”升级到“工资+福利”订阅模式的员工，我决定做一个小小的项目。

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

在我单击按钮下载报告之前，一切都很正常。我很难弄清楚下载电子表格的语法。切换到铬和使用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屏幕截图。

screenshot

更新:慢的一天，所以我有空闲时间做一些测试。

下面是我的sub结尾处的一些更新代码

    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 screenshot

Answer 1

它成功了！它成功了！它成功了！我必须将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