Excel vba to access Open/Save dialog

[viktiw]

Hello everyone,
I am trying to download a document from a website using vba.
So far I can click the print icon on website (using vba) but I am stuck when it shows Open/Save dialog box.
Please help on how I can access or click the "Save" button and save file to a given location.

Any help is appreciated.

-Vik

 

[Dan_W]

If you have a URL to that actual document, you don't need a browser to actually download the file.

Option Explicit
#If VBA7 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As LongPtr, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As LongPtr, ByVal lpfnCB As LongPtr) As Long
#Else
    Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If

Public Function DownloadFile(ByVal URL As String, ByVal DestinationFile As String) As Boolean
    ' If the API returns ERROR_SUCCESS (0), DownloadFile returns True.
    DownloadFile = (URLDownloadToFile(0&, URL, DestinationFile, &H10, 0&) = CLng(ERROR_SUCCESS))
End Function

This will need to be put into a new standard module, but then can be called from anywhere with a simple:

DownloadFile DocumentURL, "D:\TEMP\SaveDocumentHere.Doc"

Does that help?

 

[viktiw]

I also tried Application. SendKeys "%{S}" (Save) method.
If Send key is the only method, how can I use Save As

If you have a URL to that actual document, you don't need a browser to actually download the file.

Option Explicit
#If VBA7 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As LongPtr, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As LongPtr, ByVal lpfnCB As LongPtr) As Long
#Else
    Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If

Public Function DownloadFile(ByVal URL As String, ByVal DestinationFile As String) As Boolean
    ' If the API returns ERROR_SUCCESS (0), DownloadFile returns True.
    DownloadFile = (URLDownloadToFile(0&, URL, DestinationFile, &H10, 0&) = CLng(ERROR_SUCCESS))
End Function

This will need to be put into a new standard module, but then can be called from anywhere with a simple:

DownloadFile DocumentURL, "D:\TEMP\SaveDocumentHere.Doc"

Does that help?

Hi Dan,
Thank you for your reply.
I checked, the print icon when clicked, displays the open/save dialog box but doesn't have the url to document.
The url for print icon is same as the main page of website.
I am thinking of using send keys.
Application.SendKeys"{S}"

 

[Dan_W]

For the reasons you've discovered ("How do I SaveAs"), Sendkeys is probably not the best approach.
I note your point about the URL being the same as the main page - that sounds like the URL is being generated by Javascript. If you're using Internet Explorer, is there a "Copy Shortcut" option available when you right click on the hyperlink/print button? Failing that, you could always check out John's approach: IE Automation to Save as File Download from website with LogIn