1

My question is about IE automation in VBA. I got my code to work through the pages and click on the download button. After searching for hours, I came across this page on StackOverFlow:

Automate saveas dialogue for IE9 (vba)

The code they suggested worked perfectly for saving pending files that need to be downloaded... However, I want to save the file into a specific directory (save as). Questions:

  1. How can I change the following code, so it will perform what I need to do? I tried to change "Save" to "Save As" on line 3, but it didn't work.

  2. If number 1 is not possible, how can I modify the code, so I can get the name of the file and the exact folder address in which the file has been saved into?

    Option Explicit

    Dim ie As InternetExplorer
    Dim h As LongPtr
    Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias 
    "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal 
    lpsz1 As String, ByVal lpsz2 As String) As LongPtr

    Sub Download()
        Dim o As IUIAutomation
        Dim e As IUIAutomationElement
        Set o = New CUIAutomation
        h = ie.Hwnd
        h = FindWindowEx(h, 0, "Frame Notification Bar", vbNullString)
        If h = 0 Then Exit Sub

        Set e = o.ElementFromHandle(ByVal h)
        Dim iCnd As IUIAutomationCondition
        Set iCnd = o.CreatePropertyCondition(UIA_NamePropertyId, "Save")

        Dim Button As IUIAutomationElement
        Set Button = e.FindFirst(TreeScope_Subtree, iCnd)
        Dim InvokePattern As IUIAutomationInvokePattern
        Set InvokePattern = Button.GetCurrentPattern(UIA_InvokePatternId)
        InvokePattern.Invoke
    End Sub
Armani
  • 21
  • 4
  • What's the `OuterHTML` code of the "Download Button"? Is it a javascript action of direct path? What file type is it? If it's a pdf link that automatically opened in Adobe Reader etc, you can search for it in IE's cache folders. – PatricK Jan 28 '18 at 22:23
  • Could you please share the URL (any URL that can be used to reproduce the issue, check [MCVE](https://stackoverflow.com/help/mcve)) and VBA code you use to start downloading. Also consider using XHR instead of IE (check [1](https://stackoverflow.com/a/33484763/2165759), [2](https://stackoverflow.com/a/44091766/2165759), [3](https://stackoverflow.com/a/32429348/2165759), [4](https://stackoverflow.com/a/32801430/2165759)). – omegastripes Jan 28 '18 at 22:42
  • @PatricK: The code doesn't belong to me... – Armani Jan 28 '18 at 22:43
  • @omegastripes: Done! Thanks for the comments... I'm looking into them right now. Do you have anything to say regarding the second part of my question? If I can't solve that, my first question becomes irrelevant. – Armani Jan 28 '18 at 22:51
  • 1
    @Armani That is why in SO you are supposed to ask one question at a time. – K.Dᴀᴠɪs Jan 28 '18 at 22:54
  • @K.Dᴀᴠɪs : Are you suggesting I should ask my second question in a separate post? – Armani Jan 28 '18 at 22:58
  • @Armani you may just edit the question and temporarily remove the first part, add more details for remaining part (VBA code with URL, screenshot or description what you need to click, and finally to download). After you have managed with retrieving download links, you may edit the question again to ask about how actually to automate downloading of the file by that link. – omegastripes Jan 28 '18 at 23:02
  • @omegastripes: I solved the second part using Inner Text method... I modified the question... – Armani Jan 29 '18 at 15:56
  • If the `span class="x-menu-item-text"` doesn't change, loop through `For Each oItem In oIE.Document.getElementsByClassName("x-menu-item-text")`, when `InStr(1,oItem.innerText,"Export List to XLSX",vbTextCompare)>0` then try `oItem.Parent.Click`. You may also need to change how IE handles xlsx file - try force it download instead of prompt for Open/etc. Then rename the file afterwards. – PatricK Jan 29 '18 at 21:41
  • @PatricK : I solved it using .InnerText method. Basically that part was the same in each iteration. For part 1, I used to code above fo download the pending file to my Downloads folder and then used a different code to look for the latest .xlsx file to access the file. For now, I’m good. – Armani Jan 29 '18 at 21:53
  • Good work @Armani, feel free to post your code as an Answer and tick it as accepted answer. – PatricK Jan 29 '18 at 22:01

1 Answers1

0

I solved my question while ago, but I forgot to post it here. Instead of trying to save the document as "Save As", I used the above code to save the file into my Downloads folder. Then, I looked for the latest file in that folder. See the link below:

finding latest file in a folder and opening it (vba access)

At the end, moved the file from that directory to the folder I want it to be.

Armani
  • 21
  • 4