使用VBA自动化IE时URLDownloadtofile的替代方法 [英] Alternative to URLDownloadtofile when automating IE with VBA

问题描述

我已经将InternetExplorer.application与Excel VBA一起使用了相当长的一段时间，几乎没有出现任何问题.我遇到的一个问题是从网站下载文件.我可以看到出现打开/另存为"按钮，但那才是我所困的地方.

I have been using InternetExplorer.application with Excel VBA for quite a while with few issues. One problem I have is downloading a file from website. I can get as far as having the "Open/Save As" buttons appear but that is where I am stuck.

我尝试使用URLDownloadToFile，它似乎无法通过与我拥有的InternetExplorer.application对象相同的会话来工作.它通常返回网页的HTML文本，说明需要进行身份验证.如果我打开了多个浏览器，并且某些旧的浏览器已经通过身份验证，则它将在大多数时间下载文件.

I've tried using URLDownloadToFile and it does not seem to work through the same session as the InternetExplorer.application objects that I have. It usually returns the HTML text for a webpage stating that authentication is required. If I have multiple browsers open and some of the old ones are already authenticated then it will download the file most of the time.

是否可以使用InternetExplorer.application对象本身下载文件?如果没有，是否可以通过某种方法将URLDownloadtofile函数与已通过身份验证并登录到网站的对象相关联?

Is there a way to download the file using the InternetExplorer.application object itself? If not, is there some way I can associate the URLDownloadtofile function with the object that is already authenticated and logged into the website?

我一直在使用的代码是:

The code I've been using is:

    IE2.navigate ("https://...")
    strURL = "https://..."
    strPath = "c:\..."
    Ret = URLDownloadToFile(0, strURL, strPath, 0, 0)

我也尝试过:

    Do While IE2.Readystate <> 4
        DoEvents
    Loop
    SendKeys "%S"
    IE2.ExecWB OLECMDID_SAVEAS, OLECMDEXECOPT_DODEFAULT

并且:

    Dim Report As Variant
    Report = Application.GetSaveAsFilename("c:\...", "Excel Files (*.xls), *.xls")

除第一个有时会保存实际文件，但有时会保存指出身份验证错误的网站之外，在所有这些方法中均未成功.

No success in any of these, except for the first one which sometimes saves the actual file, but sometimes saves the website that states the authentication error.

谢谢

戴夫

推荐答案

这样的事情怎么样?

Public Sub OpenWebXLS()
' *************************************************
' Define Workbook and Worksheet Variables
' *************************************************
Dim wkbMyWorkbook As Workbook
Dim wkbWebWorkbook As Workbook
Dim wksWebWorkSheet As Worksheet

Set wkbMyWorkbook = ActiveWorkbook

' *************************************************
' Open The Web Workbook
' *************************************************
Workbooks.Open ("http://www.sportsbookreviewsonline.com/scoresoddsarchives/nba/nba%20odds%202015-16.xlsx")

' *************************************************
' Set the Web Workbook and Worksheet Variables
' *************************************************
Set wkbWebWorkbook = ActiveWorkbook
Set wksWebWorkSheet = ActiveSheet

' *************************************************
' Copy The Web Worksheet To My Workbook and Rename
' *************************************************
wksWebWorkSheet.Copy After:=wkbMyWorkbook.Sheets(Sheets.Count)
wkbMyWorkbook.Sheets(ActiveSheet.Name).Name = "MyNewWebSheet"

' *************************************************
' Close the Web Workbook
' *************************************************
wkbMyWorkbook.Activate
wkbWebWorkbook.Close

End Sub

这篇关于使用VBA自动化IE时URLDownloadtofile的替代方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！