Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 22

Thread: VBA to Download documents using URLs & Save As to folder

  1. #1
    VBAX Regular
    Joined
    Oct 2017
    Posts
    10
    Location

    VBA to Download documents using URLs & Save As to folder

    Hello,

    I have a list of URLs in column A which are for separate reports in Excel format. (The URLs are formatted like in the attachment below)

    In column B, I have a list of the names I would like to save/name each file as, e.g Bookings.

    I would like help to create a macro that does the following:
    1. Opens the URL from cell A1
    2. Macro waits until the Open/Save option appears before continuing
    3. Choose to "Save As" the file as name stated in cell B1, to desktop without opening
    4. Choose yes to replace file that already exists with that name
    5. Close the IE window opened in step 1
    6. Opens the URL from cell A2
    7. Macro waits until the Open/Save option appears before continuing
    8. Choose to "Save As" the file as name stated in cell B2, to desktop without opening
    9. Choose yes to replace file that already exists with that name
    10. Close the IE window opened in step 6
    11. etc until end of list of URLs in Column A is reached

    Hoping someone can help - sounds simple when I type it out but I have tried before and can't seem to figure out the whole script for the macro.
    Many Thanks!
    Attached Images Attached Images
    Last edited by MRedfern; 10-07-2017 at 08:00 AM.

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Welcome to the forum!

    Since we don't know the url, I don't see how we can help. If the url is a file, an API can be used. If you want to attach an excel file with the link, click Go Advanced button in lower right of reply box. Click the paperclip icon in toolbar or Manage Attachments hyperlink below reply box.

  3. #3
    VBAX Regular
    Joined
    Oct 2017
    Posts
    10
    Location
    Hi Kenneth,

    Thanks for the reply.

    As the URL is a private company file, I wouldn't be allowed to include it and even if I could, you would have to be an employee for it to work!

    Why would you need the actual genuine URL when i've described the process in great detail? Just curious.

    Thanks

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    As I said, if the URL is an actual file, an API method can be used. Even some that may not be an actual file url, the url opens as a file depending on the source code. e.g. http://www.vbaexpress.com/forum/atta...2&d=1506990455

    Since you say it is a file, it is easy to post a similar url in a sample file. e.g. A url to an attached file on the website such as that above. Of course Workbooks.Open is an easy way to open/save such urls as well. The API routes is best when you don't want to Open it though and especially so if not an Excel compatible file.

    If the website is showing some selection list or whatever, we have to know how the source code is setup. Some are secure and javascript hides what the actual file path is. In those cases, the Internet Explorer object is about your only path to solution. You still need to know source code for the names of say a Submit button.

    In cases where the API method does not work, I can get the source code if I know the url. If secure, you would be violating security if you even post the link or source code. So, now you see why helping can be hard in those cases.

    Of course the sample file would also tell me if the link url is an actual link or a formula link. Solutions vary depending on which link type was used.

    If you have not posted an example file when I get time later, I will make up an example using the url that I detailed, I will show you the API solution. I would show that code now but you would still need the parsing of the link code.

  5. #5
    VBAX Regular
    Joined
    Oct 2017
    Posts
    10
    Location
    Hi Kenneth.

    Many thanks - I have limited knowledge i'm afraid and have never heard of API before or what it means but if you can help, i'd really appreciate it.

    Maybe if explain the process I go through?

    Each report I can access is listed in a website location
    Each report in the list as an individual URL
    When I click a particular report in the list, the page acts like its loading or has been refreshed
    When the report is ready, a "Would you like to Open or Save the file" bar appears
    I usually just select open and the report opens in Excel

  6. #6
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Post #5 makes me think you will have your work cut out for you. If you know about a web site's source code, that might give you a clue for what you will need for the explorer object method. Search for "excel getelementsbyid" to find examples that use source code elements. Chrome makes looking at a site's source code elements easy. This search show some code that may help as well. "excel explorer submit button"

    Attached is a simple example using the API method that I explained. In it, notice that I had to know that the file extension was gif. Normally, one would know such.

  7. #7
    VBAX Regular
    Joined
    Oct 2017
    Posts
    10
    Location
    Thanks Kenneth,

    I'm ideally in need of a macro that replaces the clicks and save as steps I go through - I will take a look at your suggestions.

    Thankyou!

  8. #8
    VBAX Regular
    Joined
    Oct 2017
    Posts
    10
    Location
    Hi Kenneth,

    Maybe I haven't explained properly - the list of URLs I have are in a list in an excel workbook so I can click on each like a hyperlink to run a specific report, I don't have to click the link provided in our company website although I must be signed into our company's security section.

    In other words, the macro only needs to click a hyperlink in the list in excel, wait for the 'save' window to appear, click save (sendkeys?), save the file as the name listed next to the hyperlink to specific folder, replace old file, close, ......click next in list and repeat until list ends

    Maybe this will make a difference?

  9. #9
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Oh, I know "almost exactly" what you need. Since I have no access, I can not help directly. All I can do is to teach you to solve it yourself. This is the 3rd thread this week that I have helped with for a similar issue. In the other two, I was able to help directly. It is funny how things come in 3's sometimes.

    The best solution to my mind is the Internet Explorer object method which is problem enough. I guess you can use SendKeys() but I try to avoid it whenever possible. If you want go that route, go for it. Keep in mind SendKeys() success is all about focus and timing, rinse and repeat.

    Obviously, just clicking the hyperlink is not going to do it for you even if you go the SendKeys() route. You are going to need a macro to intercept the link's click and to act on the hyperlink's address (url). For that part, this site explains how to run a macro from a link click. https://www.extendoffice.com/documen...hyperlink.html
    We'll deal with that issue later if you can get a macro to do what you want.

    If you want to learn the most reliable method, read on. Of course no method is totally reliable as webmaster's change their sites without notice.

    First off, you need to learn how to use Chrome or Internet Explorer's Inspector. Right click and Inspect on an inputbox or button, will quickly show you what element you need to deal with. Some elements can be handled by their: ID, Name, or Classname. Form buttons can be "cliicked" by Click or Submit. It just depends.

    The second step is to use what you have inspected. There are many threads about how to use the Internet Explorer object. One of the best ways to learn it though is a real world example. e.g. We want to login into mail.yahoo.com automatically. Note how a button is "clicked" after username so code has to wait for the new url to activate before sending the password and another button "click".

    'https://chandoo.org/forum/threads/how-to-get-logged-in-in-yahoo-com.36038
    Sub Test_LoginYahoo2()
        LoginYahoo2 "User", "Password"
    End Sub
    
    
    ' Add references in Tools > References for:
    ' Microsoft HTML Object Library
    ' Microsoft Forms 2.0 Object Library
    ' Microsoft Internet Controls
    Sub LoginYahoo2(username As String, password As String)
        Const strURL_c As String = "http://mail.yahoo.com"
        Dim url As String
        Dim objIE As SHDocVw.InternetExplorer
        Dim ieDoc As MSHTML.HTMLDocument
        Dim tbxPwdFld As MSHTML.HTMLInputElement
        Dim tbxUsrFld As MSHTML.HTMLInputElement
        Dim btnSubmit As MSHTML.HTMLInputElement
        
        Excel.Application.Cursor = xlWait
        If InStr(username, "@") = 0 Then username = username & "@yahoo.com"
        
        On Error GoTo Err_Hnd
        
        'Create Internet Explorer Object
        Set objIE = New SHDocVw.InternetExplorer
        'Navigate the URL
        objIE.navigate strURL_c
        objIE.Visible = False
        'Wait for page to load
        Do Until objIE.readyState = READYSTATE_COMPLETE: Loop
        Set ieDoc = objIE.document
        url = ieDoc.url
        
        ieDoc.getElementsByName("username").Item(0).Value = username
        'Do Until ieDoc.getElementsByName("username").Item(0).Value = username: Loop
        ieDoc.forms("signin").submit
        'ieDoc.getElementById("login-signin").Click
        
        Do Until ieDoc.url <> url: Loop
        'Do Until objIE.readyState = READYSTATE_COMPLETE '4: Loop
        Set ieDoc = objIE.document
        
        'ieDoc.getElementsByName("password").Item(0).Value = password
        'Do Until ieDoc.getElementsByName("password").Item(0).Value = password: Loop
        ieDoc.getElementById("login-passwd").Value = password
        Do Until ieDoc.getElementById("login-passwd").Value = password: Loop
        ieDoc.forms("verifyPassword").submit
        'ieDoc.getElementById("login-signin").Click
        
    Err_Hnd: '(Fail gracefully)
        objIE.Visible = True
        On Error GoTo 0
        Excel.Application.Cursor = xlDefault
    End Sub

  10. #10
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Give this a try. I've tested on these locations for a publicly available file.

    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 LongPtr 
    Dim Ret As LongPtr 
     
    Sub DownloadFile()
        pth = "C:\VBAX\"
        For Each link In ActiveSheet.Hyperlinks
        fname = Split(link.Address, "/")(UBound(Split(link.Address, "/")))
            filename = pth & fname
            URLDownloadToFile 0, link.Address, filename, 0, 0
        Next link
        End
    End Sub
    Last edited by mdmackillop; 10-11-2017 at 02:44 AM. Reason: API changed to PtrSafe
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  11. #11
    VBAX Regular
    Joined
    Oct 2017
    Posts
    10
    Location
    Thanks mdmackillop,

    But looking at the macro, isn't it just splitting the link address into a filename? I'm also not sure what the 2 excel files are as they have no hyperlinks in them?

  12. #12
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Try this. These files were just downloadable examples
    Attached Files Attached Files
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  13. #13
    VBAX Regular
    Joined
    Oct 2017
    Posts
    10
    Location
    I seem to get an error on opening. Any ideas?....
    macroshot1.jpg

  14. #14
    Try changing the red line with this line

    #If VBA7 And Win64 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

  15. #15
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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 LongPtr
    Dim Ret As LongPtr
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  16. #16
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    You can open any workbook using
    Workbooks.open "http://www.example.com/files/example.xlsx"
    or any document

    Documents.open "http://www.example.com/files/example.docx"

  17. #17
    VBAX Regular
    Joined
    Oct 2017
    Posts
    10
    Location
    Thanks! Got it to work
    That's an interesting file - I just need to try it with my hyperlinks tomorrow. I expect I will have to insert some additional parts which deal with the IE loading aspect etc

    Will have play around!
    Thanks again :-)

  18. #18
    VBAX Regular
    Joined
    Oct 2017
    Posts
    10
    Location
    Hi Kenneth,

    I've played around with a few suggestions bit with little success i'm afraid.

    I have tried to replicate the file i'm trying to use with some dummy links (see attached). The image shows what I see after the URL has been clicked and the report has loaded in Internet Explorer.

    Any help appreciated!
    Attached Images Attached Images
    Attached Files Attached Files

  19. #19
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    Unfortunately, snips like that don't help me help you.

    As I said, you have two solution paths.
    1. IE object
    2. SendKeys()

    If you can view the source code's text when you click the link, can you see the value Reports File.xlsx and the path to it blocked out? If you can, code can get that and use the API or Workbooks.Open methods to get and save the file. My suspicion is that they used a security method to hide such details.

    If it was just one file, a SendKeys() might have an off chance of working. For more than one file, SendKeys() will likely not be of much help. Of course a hybrid of the two methods might have a remote chance of working.

    I did project many years ago where I had to get all items in a web listbox and then chose an item by code. As I explained, one has to get source code to know if that kind of thing is even possible.

    If you can learn how I coded the #9 post's example, you might be able to discover a like solution for yourself. Try to work through doing that example with only the link known, mail.yahoo.com. Of course if you cheat and look at the solution example, I won't tell. In fact I recommend stealing, err, using the shown solution but in steps. Think, how would I enter my username, click Next button, enter my password, and click Ok button to login. As you will see in code, the Next button loads another website. As such, it has to wait until it loads before interacting with it further. Loops often work but I added an extra step where it waits for the new url to <> the first url. Sometimes, one just has to add and Application.Wait.




    Feel free to PM me for some private conversation.
    Last edited by Kenneth Hobs; 10-12-2017 at 09:32 AM.

  20. #20
    VBAX Regular
    Joined
    Oct 2017
    Posts
    10
    Location
    HI Kenneth,

    I've done right click and inspect element to look at the code and I can't find any .xlsx documents or anything similar.

    I did play around with the following code a while ago but couldn't get the "readystate" check to work so had to get the macro to pause until the report loaded and again when it was opening. The only part I couldn't master was saving it down and replacing the previous version.

    Sub Open_URL()


    Do
    Dim ieDoc As Object
    Dim Report As Variant
    Set ie = CreateObject("Internetexplorer.Application")
    ie.Visible = True
    ie.Navigate Sheets("Reports").Range("A1").Value
    Application.Wait (Now + TimeValue("00:00:20"))
    Application.SendKeys "{Enter}"
    Application.Wait (Now + TimeValue("00:00:20"))
    If IsEmpty(ActiveCell) Then
    Exit Do
    End If
    Loop

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •