VBA - 转到网站并从保存提示下载文件 [英] VBA - Go to website and download file from save prompt

问题描述

我一直在花几个小时试图找出如何使用VBA将文件保存到计算机上。下面的代码模板，我发现在另一个论坛似乎很有希望，除了当我去桌面访问它，.csv文件有什么看起来像页面的源代码，而不是我想要的实际文件。这可能是因为当我去URL时，它不会自动下载文件;相反，我被要求将文件保存到某个位置（因为我不知道网站上传的文件的路径名称）。
有没有办法改变这个代码以适应这个，或者我必须完全使用不同的代码？

  Sub Test（）
 Dim FileNum As Long 
 Dim FileData（）As Byte 
 Dim MyFile As String 
 Dim WHTTP As Object 
 
关于错误恢复Next 
设置WHTTP = CreateObject（WinHTTP.WinHTTPrequest.5）
如果Err.Number<> 0然后
设置WHTTP = CreateObject（WinHTTP.WinHTTPrequest.5.1）
结束如果
错误GoTo 0 
 
 
 MyFile =MY_URL_HERE 
 
 WHTTP.OpenGET，MyFile，False 
 WHTTP.send 
 FileData = WHTTP.responseBody 
设置WHTTP =没有
 
如果Dir（C：\Users\BLAHBLAH\Desktop，vbDirectory）= Empty Then MkDirC：\Users\BLAHBLAH\Desktop
 
 FileNum = FreeFile 
打开C：\Users\BLAHBLAH\Desktop\memberdatabase.csv对于二进制访问写为#FileNum 
放置#FileNum，1，FileData 
关闭#FileNum 
 
 End Sub 
  

交叉帖子：

http://www.ozgrid.com/forum/showthread.php?t=178884

http://www.excelforum.com/excel- program-vba-macros / 925352-vba-to-website-and-download-file-from-save-prompt.html

解决方案

多年来，我发现更多的方式如何使用vba保存/下载数据

• 我喜欢并推荐的第一个选项是使用 user32库 URLDownloadToFile函数 c $ c>使用以下解决方案


• 第二个也被提到是你自己。这里的要点是使用 Microsoft WinHTTP服务（Interop.WinHttp）COM库。为了实现这一点，您还可以添加Interop.WinHttp参考到您的项目链接。之后，您可以使用像这样更简单的符号链接


• 我知道的第三个选项是要求浏览器为我们保存，然后使用Santosh提到的 Save_Over_Existing_Click_Yes 函数。在这种情况下，我们使用COM接口打开Internet Explorer并导航到正确的站点。所以我们必须添加 Microsoft Internet Controls （ Interop.SHDocVw ）和 Microsoft HTML对象库（ Microsoft.mshtml ）引用我们的项目，以获得编辑器的智能感知功能。
  我不喜欢这个下载方法，因为这是一个黑客的工作。但是，如果您的IE会话已经建立认证等，这将很好地工作。由于安全性问题，因特网控制器的保存功能被删除了。参见例如：链接

• 所以请尝试确保你使用的URL是正确的它在浏览器中如果它打开正确的.csv文件比您的源可以工作。


• 另请尝试发送更多信息：例如.csv文件的URL。

I've been spending the last few hours trying to figure out how to save a file onto the computer using VBA. The code template below that I found on another forum seems promising, except when I go to the desktop to access it, the .csv file has what looks like the page's source code instead of the actual file I want. This may be because when I go to the URL, it doesn't automatically download the file; rather, I am asked to save the file to a certain location (since I don't know the path name of the uploaded file on the site). Is there any way to alter this code to accommodate this, or will I have to use a different code entirely?

Sub Test()
Dim FileNum As Long
Dim FileData() As Byte
Dim MyFile As String
Dim WHTTP As Object

On Error Resume Next
    Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
    If Err.Number <> 0 Then
        Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
    End If
On Error GoTo 0


MyFile = "MY_URL_HERE"

WHTTP.Open "GET", MyFile, False
WHTTP.send
FileData = WHTTP.responseBody
Set WHTTP = Nothing

If Dir("C:\Users\BLAHBLAH\Desktop", vbDirectory) = Empty Then MkDir "C:\Users\BLAHBLAH\Desktop"

FileNum = FreeFile
Open "C:\Users\BLAHBLAH\Desktop\memberdatabase.csv" For Binary Access Write As #FileNum
    Put #FileNum, 1, FileData
Close #FileNum

End Sub

Cross posts:
http://www.ozgrid.com/forum/showthread.php?t=178884
http://www.excelforum.com/excel-programming-vba-macros/925352-vba-go-to-website-and-download-file-from-save-prompt.html

解决方案

I found over the years more ways how to save/download data using vba:

• The firs option witch I prefer and would recommend is to use the URLDownloadToFile function of the user32 library using the following solution
• The second one which was also mentioned be yourself. The point here is to use the Microsoft WinHTTP Services (Interop.WinHttp) COM library. In order to achieve this you can also add the Interop.WinHttp reference to your project link. After that you are able to use simpler notation like here link
• The third option I aware is to ask the browser to save it for us and then using the Save_Over_Existing_Click_Yes function was mentioned by Santosh. In this case we open an Internet Explorer using the COM interface and navigate to the proper site. So we have to add the Microsoft Internet Controls (Interop.SHDocVw) and the Microsoft HTML Object Library (Microsoft.mshtml) references to our project in order to gain intellisense feature of the editor. I don't like this download method because this is a work around by hacking. BUT if your IE session was already established authenticated etc. this gonna work nicely. The save function of the Internet Controls was dropped because of security concern. See for example: link

Newer the less you have to have the correct url to download what you want. If you pick the wrong one you will download something else :)

• So please try to make sure the the url you use is correct by enter it in a browser. If it opens the right .csv file than your source could work too.
• Also please try to send some more information: for example the url to the .csv file

这篇关于VBA - 转到网站并从保存提示下载文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！