VBA - Go to the site and download the file from the save prompt

Question

I spent the last few hours trying to figure out how to save a file to a computer using VBA. The code template below what I found on another forum seems promising, unless I go to the desktop to access it, the .csv file has what looks like the source code of the page, and not the actual file that I want. Perhaps this is due to the fact that when I go to the URL, it does not automatically download the file; rather, they will ask me to save the file in a specific place (since I do not know the path name for the downloaded file on the site). Is there a way to change this code for 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 messages:
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

Answer 1

Try entering the code:

Copied from here (not verified)

 Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long Declare Function SetForegroundWindow Lib "user32" Alias "SetForegroundWindow" (ByVal hwnd As Long) As Long Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long) Private Sub Save_Over_Existing_Click_Yes() Dim hWnd As Long Dim timeout As Date Debug.Print "Save_Over_Existing_Click_Yes" 'Find the Download complete window, waiting a maximum of 30 seconds for it to appear. Timeout value is dependent on the 'size of the download, so make it longer for bigger files timeout = Now + TimeValue("00:00:30") Do hWnd = FindWindow(vbNullString, "Save As") DoEvents Sleep 200 Loop Until hWnd Or Now > timeout Debug.Print " Save As window "; Hex(hWnd) If hWnd Then 'Find the child Close button hWnd = FindWindowEx(hWnd, 0, "Button", "&Yes") Debug.Print " Yes button "; Hex(hWnd) End If If hWnd Then 'Click the Close button SetForegroundWindow (hWnd) Sleep 600 'this sleep is required and 600 miiliseconds seems to be the minimum that works SendMessage hWnd, BM_CLICK, 0, 0 End If End Sub 

Answer 2

I have found over the years more ways to use save/download data with vba:

• I guess I prefer to use and recommend using the URLDownloadToFile function user32 library using the following solution
• The second, which was also mentioned, was himself. Here you need to use the Microsoft WinHTTP Services (Interop.WinHttp) COM library . To do this, you can also add the Interop.WinHttp link to your project link . After that you can use a simpler notation, as the link here
• The third option that I know is to ask the browser to save it for us, and then use the Save_Over_Existing_Click_Yes function mentioned by Santos. In this case, we open Internet Explorer using the COM interface and go to the corresponding site. Therefore, we must add the Microsoft Internet Controls ( Interop.SHDocVw ) and Microsoft HTML Object Library ( Microsoft.mshtml ) links to our project in order to get the intellisense editor function. I do not like this download method because it is hacking. BUT, if your IE session has already been authenticated, etc., this will work well. The save feature of Internet controls has been disabled due to a security issue. See for example: link

The bigger, the less you have to have the correct URL to download what you want. If you choose the wrong one, you will upload something else :)

• Therefore, please try to make sure that the URL you are using is correct, enter it in your browser. If it opens the right .csv file that may work with your source.
• Also try sending additional information: for example, the URL of the .csv file