在使用VBA时处理IE中的打开/保存/取消对话窗口 [英] Dealing with the open/save/cancel dialogue window in IE when using VBA
问题描述
目标：从网页的源代码中找不到文件网址的网站自动执行文件下载（每天约15-20个xlsx文件）。
以下是手动下载时通常采取的步骤。
- 打开登录页面并输入登录凭据以访问感兴趣的网页（即具有所有报告的页面）
- 登录后，导航到带有报告的网页
note1：it被设置为使得1个网页（唯一URL）=在第一页显示前55个结果
note2：同一页面也有按钮导出/保存不同格式的整个报表
-
下载报告
-
导航到下一个网页（在同一网站内），并重复步骤2和3（大约15-20个报告/网页导航）
我已经通过点击使用SendKeys保存下载第一个报告。虽然有时它会在对话窗口出现时停止，但这已经到了最远。在此之后，我无法浏览到另一个网页，并重复下载相同的步骤。我的直觉是，点击保存按钮后出现的打开/打开的文件/查看下载对话窗口不允许我重复下载/保存过程...
我试着看网站的源代码，看看是否可以找到该文件的URL，但找不到（不知道是否必须这样做，导出只发生在点击提交按钮隐藏文件url或其他类似运行脚本的东西）。我对WinHttpRequest不太熟悉，但是在做了google研究之后，似乎是首选的方法。它也似乎需要有一个文件URL，但不能确定这一点...
以下是我现在整理的代码。任何帮助将非常感激。谢谢！ ：）
Sub webMacro（）
Dim IE As New InternetExplorer
IE.Visible = True'change False - >真的打开IE窗口
IE.navigatehttps://websiteURL.net//apps/login.aspx
Do
DoEvents
循环直到IE .readyState = READYSTATE_COMPLETE
Dim Doc As HTMLDocument：Set Doc = IE.document
Doc.getElementById（username）。Value =myusername'登录到网站
Doc.getElementById（pass）。Value =mypassword
Doc.getElementById（Enter）。点击
睡眠（1000）
Do
DoEvents
循环直到IE.readyState = READYSTATE_COMPLETE
IE.navigatehttps：//firstReportWebPage.net//apps / .... 导航到第一个网页（报告），登录后下载
Do
DoEvents
循环直到IE.readyState = READYSTATE_COMPLETE
Doc。 getElementById（##########）。单击导出/保存按钮的ID
执行
DoEvents
循环直到IE .readyState = READYSTATE_COMPLETE
Doc.getElementById（###########）。点击 e提交按钮
Do
DoEvents
循环直到IE.readyState = READYSTATE_COMPLETE
Doc.getElementById（##### #######）。点击进入打开/保存/取消对话窗口之前的字段ID
Do
DoEvents
循环直到IE。 readyState = READYSTATE_COMPLETE
Application.Wait（Now + TimeValue（0:00:02））'这里我正在使用SendKeys来模拟我将在键盘上手动执行的操作进入保存按钮
SendKeys{TAB}，True
SendKeys{TAB}，True
SendKeys{DOWN}，True
SendKeys {ENTER}，True
睡眠（1000）
Do
DoEvents
循环直到IE.readyState = READYSTATE_COMPLETE
睡眠（1000 ）
''***这是几乎总是被卡住的地方...在这里我试图通过点击打开/打开文件/查看下载对话窗口使用Tab键进入对话窗口之前的字段;与打开/保存/取消对话窗口中的保存按钮相同。
Doc.getElementById（############）。点击进入打开/打开文件/查看下载对话框之前的字段ID
睡眠（1000）
Do
DoEvents
循环直到IE.readyState = READYSTATE_COMPLETE
睡眠（1000）
Application.Wait（Now + TimeValue（0:00:02））
睡眠（1000）
SendKeys{TAB}，True
睡眠（1000）
SendKeys{TAB}，True
睡眠（1000）
SendKeys{TAB}，True
睡眠（1000）
SendKeys {TAB}，True
睡眠（1000）
SendKeys{ENTER}，True
睡眠（1000）
'要去这里...
End Sub
我一直看到别人建议不要使用sendkey，但是当我试图做类似的事情时，我并没有真正知道他们的意思。
SendKeys会随机复制一个键发送有时（我用它来控制16风ows在同一时间），每窗口1个说明和18,000个指令必须被处理。
每500个指令发生约2-3次浏览器解析，我找不到解决方法。
导航网站，我写了一些这样做的东西，然后我也写了一些下载HTML该页面。
您是否可以使用打开/保存/取消对话框下载页面的HTML源，并查看该页面上存在该文件的URL在这个按钮等中？
如果是这样，你可能会自动导航到该页面，然后下载HTML（我有一个代码，如果url在源），然后在VBA中解析HTML以计算下载URL？
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.
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.
Below are the steps that I usually take when downloading these manually.
- Open login page and enter login credential to access webpage of interest (i.e. the one with all the reports)
- 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
note2: the same page also has a button to export/save the entire report in different formats
Download the report
Navigate to next webpage (within the same website) and repeat steps 2 and 3 (there's about 15-20 reports/webpages to navigate)
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...
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...
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
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.
It happened about 2-3 times for every 500 instructions that were parsed by the browsers, and i couldnt find a workaround.
The navigating the website, i wrote something that does that, and then i also wrote something that downloads the HTML of the page.
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?
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?
这篇关于在使用VBA时处理IE中的打开/保存/取消对话窗口的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
