如何点击从VBA这个特殊的按钮? [英] How to click on this particular button from VBA?
问题描述
我有一个简单的VBA code(见下文),即进入一个网页,选择一定的价值,点击开始下载按钮,然后保存文件。问题是我坚持的点击下载按钮的一部分。有人可以帮忙吗?
下面是code:
子Treasury_Auc_Notes() 昏暗IE作为对象 设置IE =什么
设置IE =的CreateObject(InternetExplorer.Application)
IE.Visible = TRUE
IE.Navigatehttp://www.treasurydirect.gov/RI/OFAuctions?form=ndnld&typesec=notes 虽然IE.Busy
的DoEvents
WEND IE.Document.All.Item(begYr)。值=2012
随着IE.Document.getElementsByName(COLS)
.Item(0).Checked =真
结束与 '点击开始下载按钮(这是我在哪里卡住)
选择保存或打开取消(我没有得到这部分还) ActiveWorkbook.SaveAs文件名
结束小组
这其中的猫腻,而且由于我的笔记本电脑严格的安全,我不能够验证这个100%,但尝试:
虽然IE.ReadyState<> 4
的DoEvents
WENDIE.Document.All.Item(begYr)。值=2012
随着IE.Document.getElementsByName(COLS)
.Item(0).Checked =真
结束与
昏暗ELE作为对象对于每个ELE在IE.Document.Forms
如果ele.Action =/ RI / OFAuctions然后
ele.Submit
对于出口
万一
下一个
您可能需要使用的SendKeys
(我认为 Application.SendKeysO
)方法来打开文件然后使用VBA的 ActiveWorkbook
保存到所需的位置。我没能测试的SendKeys
下面提到的原因。
或者说,我是pretty肯定有一个WinAPI的功能,可以比的SendKeys
这样做更可靠。你需要获得的hWnd
保存对话框,并做一些其他的东西,迫使它来打开/保存。这是相当先进的VBA,我可能必须从某个地方的引用,但很少需要使用它。如果你有这种特殊的一部分麻烦,我会劝你问一个单独的问题如何获取文件的hWnd保存从IE 对话框和下载文件或类似的东西。
注意:我无法测试的SendKeys
方法。当我使用上述code,我相当肯定的文件被下载,但它将会是隐藏的,并且很难找到一个临时文件夹。在任何情况下,它似乎与一些人工干预来下载。我得到这样的警告:
我点击忽略(我不知道如何将这种自动化的一部分,我只是想验证表单 .Submit
方法实际工作),以及一些有创意的搜索(Internet临时文件在陌生/隐藏文件夹,通常倾倒)后,我确认被下载的文件,但它显示为TXT扩展,而不是CSV。
如果不是使用VBA,我上的按钮手动点击,我选择打开文件打开为CSV并有到Internet临时位置相同的路径。
I have a simple VBA code (see below), that goes to a webpage, selects some value, clicks the "Begin download" button, and then saves the file. The problem is I am stuck at the "clicking the download button" part. Can someone help?
Here is the code:
Sub Treasury_Auc_Notes()
Dim IE As Object
Set IE = Nothing
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate "http://www.treasurydirect.gov/RI/OFAuctions?form=ndnld&typesec=notes"
While IE.Busy
DoEvents
Wend
IE.Document.All.Item("begYr").Value = "2012"
With IE.Document.getElementsByName("cols")
.Item(0).Checked = True
End With
'Click "Begin download" button (this is where I am stuck)
'Choose Save Open or Cancel (I haven’t got to this part yet)
ActiveWorkbook.SaveAs Filename
End Sub
This one's tricky, and due to restrictive security on my laptop, I'm not able to verify this 100%, but try:
While IE.ReadyState <> 4
DoEvents
Wend
IE.Document.All.Item("begYr").Value = "2012"
With IE.Document.getElementsByName("cols")
.Item(0).Checked = True
End With
Dim ele As Object
For Each ele In IE.Document.Forms
If ele.Action = "/RI/OFAuctions" Then
ele.Submit
Exit For
End If
Next
You may have to use SendKeys
(I think Application.SendKeys "o"
) method to open the file then use VBA to save the ActiveWorkbook
to the desired location. I'm not able to test SendKeys
for reasons mentioned below.
Or, I'm pretty sure there is a WinAPI functions that can do this more reliably than SendKeys
. You'll need to get the hWnd
of the Save dialog and do some other stuff to force it to open/save. This is fairly advanced VBA that I probably have a reference to somewhere, but rarely need to use it. If you have trouble with this particular part, I would urge you to ask a separate question "How to get the hWnd of File Save dialog and download file from IE" or something like that.
NOTE: I can't test the SendKeys
method. When I use the above code, I am fairly certain the file is being downloaded, but it is going to a temporary folder that is hidden, and difficult to find. In any case, it does appear to be downloading with some manual intervention. I get this warning:
I click to ignore that (I have no idea how to automate this part, I'm just trying to validate that the form .Submit
method actually worked), and after some creative searching (temporary internet files get dumped in a strange/hidden folder usually) I verify the file is downloaded, although it is showing as a TXT extension instead of CSV.
If instead of using VBA, I click on the button manually, and I choose to "open" the file opens as CSV and has the same path to that temporary internet location.
这篇关于如何点击从VBA这个特殊的按钮?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!