vba 使用VBA处理IE中打开/保存/取消对话窗口

Dealing with the open/save/cancel dialogue window in IE when using VBA

提问by akondo

First and foremost, thank you for taking the time and interest into this question. I have been using VBA to automate manual tasks within Excel for sometime now, but just recently started exploring accessing the web using VBA.

首先，感谢您抽出时间和对这个问题感兴趣。一段时间以来，我一直在使用 VBA 在 Excel 中自动执行手动任务，但最近才开始探索使用 VBA 访问网络。

Goal: automate file downloads (about 15-20 xlsx files daily) from a website where the file url is nowhere to be found in the page's source code.

目标：从一个在页面源代码中找不到文件 url 的网站自动下载文件（每天大约 15-20 个 xlsx 文件）。

Below are the steps that I usually take when downloading these manually.

以下是我在手动下载这些时通常采取的步骤。

1. Open login page and enter login credential to access webpage of interest (i.e. the one with all the reports)
2. After login in, navigate to the webpage with the report note1: it is setup so that 1 webpage (unique URL) = displays top 55 results in the first page

1. 打开登录页面并输入登录凭证以访问感兴趣的网页（即包含所有报告的网页）
2. 登录后，导航到带有报告说明1的网页：设置为1个网页（唯一网址）=在第一页中显示前55个结果

note2: the same page also has a button to export/save the entire report in different formats

注意2：同一页面还有一个按钮，可以以不同的格式导出/保存整个报告

1. Download the report

2. Navigate to next webpage (within the same website) and repeat steps 2 and 3 (there's about 15-20 reports/webpages to navigate)

1. 下载报告

2. 导航到下一个网页（在同一网站内）并重复第 2 步和第 3 步（大约有 15-20 个报告/网页要导航）

I have gotten as far as downloading the first report by clicking save using the SendKeys. Although sometimes it stops as soon as the dialogue window appears, this has worked up to this point the farthest. It is after this that I have not been able to navigate to another webpage and repeat the same steps to download. My gut feeling is that the Open/Open file/View downloads dialogue window that appears after clicking on the save button is not allowing me to repeat the download/saving process...

我已经通过使用 SendKeys 单击保存来下载第一个报告。虽然有时它会在对话窗口出现时立即停止，但到目前为止，这是最有效的。在此之后，我无法导航到另一个网页并重复相同的步骤进行下载。我的直觉是，单击保存按钮后出现的打开/打开文件/查看下载对话框窗口不允许我重复下载/保存过程......

I tried looking at the source code of the website to see if I could find the url to the file, but could not find it (not sure if it has to do that the export only occurs after clicking on the submit button which hides the file url or something else like running a script). I'm not very familiar with WinHttpRequest, but seems to be the preferred method after doing my google research. It also looks like this would require to have a file URL, but not sure on this either...

我试着查看网站的源代码，看看我是否能找到文件的 url，但找不到它（不确定是否必须这样做，导出仅在单击隐藏文件的提交按钮后发生url 或其他类似运行脚本的东西）。我对 WinHttpRequest 不是很熟悉，但在我谷歌研究后似乎是首选方法。看起来这也需要有一个文件 URL，但也不确定...

Below is the code that I put together so far. Any help would be very very much appreciated. Thank you! :)

下面是我到目前为止放在一起的代码。任何帮助将不胜感激。谢谢！:)

Sub webMacro()

Dim IE As New InternetExplorer
    IE.Visible = True   'change False --> True to open the IE window
    IE.navigate "https://websiteURL.net//apps/login.aspx"

Do
    DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE

Dim Doc As HTMLDocument: Set Doc = IE.document

Doc.getElementById("username").Value = "myusername"  'login to the website
Doc.getElementById("pass").Value = "mypassword"
Doc.getElementById("Enter").Click


Sleep (1000)

Do
    DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE

IE.navigate "https://firstReportWebPage.net//apps/....."        'navigates to the first webpage (report) to download after login

Do
    DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE

Doc.getElementById("##########").Click     'ID of the Export/Save button


Do
    DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE


Doc.getElementById("###########").Click     'ID of the Submit button

Do
    DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE


Doc.getElementById("############").Click        'ID of the field right before it enters the Open/Save/Cancel dialogue window

Do
    DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE


Application.Wait (Now + TimeValue("0:00:02"))  'here I'm using the SendKeys to mimic what I would manually do on the keyboard to get to the "Save" button
   SendKeys "{TAB}", True
   SendKeys "{TAB}", True
   SendKeys "{DOWN}", True
   SendKeys "{ENTER}", True

Sleep (1000)

Do
    DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Sleep (1000)


''***This is where it almost always gets stuck...here I'm attempting to get to the Open/Open file/View downloads dialogue window by clicking on the field right before entering the dialogue window using the tab key; same as above when trying to click on the "Save" button in the Open/Save/Cancel dialogue window.

Doc.getElementById("############").Click        'ID of the field right before it enters the Open/Open File/View Downloads dialogue window

Sleep (1000)

Do
    DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE

Sleep (1000)

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

Sleep (1000)
   SendKeys "{TAB}", True
Sleep (1000)
   SendKeys "{TAB}", True
Sleep (1000)
   SendKeys "{TAB}", True
Sleep (1000)
   SendKeys "{TAB}", True
Sleep (1000)
   SendKeys "{ENTER}", True
Sleep (1000)


'some other code to go here...

End Sub

回答by akondo

I had always seen 'dont use sendkeys' advised by others, but i didnt truly know what they meant when i tried to do something similar to this.

我一直看到其他人建议“不要使用发送密钥”，但是当我尝试做类似的事情时，我并不真正知道他们的意思。

SendKeys will randomly duplicate a key send sometimes (i was using it to control 16 windows at the same time), 1 set of instructions per window and 18,000 instructions that had to be processed.

SendKeys 有时会随机复制一个密钥发送（我用它同时控制 16 个窗口），每个窗口 1 组指令和必须处理的 18,000 条指令。

It happened about 2-3 times for every 500 instructions that were parsed by the browsers, and i couldnt find a workaround.

对于浏览器解析的每 500 条指令，它发生了大约 2-3 次，我找不到解决方法。

The navigating the website, i wrote something that does that, and then i also wrote something that downloads the HTML of the page.

在浏览网站时，我写了一些这样做的东西，然后我还写了一些下载页面 HTML 的东西。

Are you able to download the HTML source of the page with the Open/Save/Cancel dialog, and see if the URL to the file exists on that page within the button etc?

您是否能够使用“打开/保存/取消”对话框下载页面的 HTML 源代码，并查看该文件的 URL 是否存在于按钮等页面上？

If it does, you could perhaps automate navigating to that page, then downloading the HTML (i have code you can have IF the url is in the source), and then parsing the HTML within VBA to calculate the download URL?

如果是这样，您也许可以自动导航到该页面，然后下载 HTML（如果 url 在源中，我有您可以拥有的代码），然后在 VBA 中解析 HTML 以计算下载 URL？