6

We need to download file from a NASDAQ website automatically. My existing VBA code is opening an IE "Do you want to Open/Save" dialogue window. How to click on that save button and give a path via VBA ? I have tried various windows api methods described in this link here also but that is giving a result of "Window Not Found".

My current code is as below:

Sub MyIEauto()

    Dim ieApp As InternetExplorer
    Dim ieDoc As Object
    'Dim ieTable As Object

    'create a new instance of ie
    Set ieApp = New InternetExplorer

    'you don’t need this, but it’s good for debugging
    ieApp.Visible = True
    'assume we’re not logged in and just go directly to the login page
    ieApp.Navigate "https://indexes.nasdaqomx.com/Account/LogOn"
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop

    Set ieDoc = ieApp.Document
    'fill in the login form – View Source from your browser to get the control names
    With ieDoc.forms(0)
        .UserName.Value = "xxxxxxx"
        .Password.Value = "xxxxxxx"
        .submit
    End With
    Do While ieApp.Busy: DoEvents: Loop
    Do Until ieApp.readyState = READYSTATE_COMPLETE: DoEvents: Loop

    'now that we’re in, go to the page we want
    ieApp.Navigate "https://indexes.nasdaqomx.com/Index/ExportWeightings/NDX?tradeDate=2015-08-19T00:00:00.000&timeOfDay=SOD/SODWeightings_2015"

    'next below line commented as it is failing
    'ieApp.ExecWB 4, 2, "D:\VBA code work\SODWeightings_20150819_NDX.xlsx" 

    set ieApp=Nothing
    set ieDoc=Nothing

End Sub

The screenshot below shows where I have reached. How do I progress from here?

enter image description here

Community
  • 1
  • 1
pmr
  • 998
  • 2
  • 13
  • 27

4 Answers4

17

It's solved finally...

Option Explicit

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private 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

Public Sub AddReference()

    ThisWorkbook.VBProject.References.AddFromFile "C:\Windows\SysWOW64\UIAutomationCore.dll"

End Sub

'after my original code as posted in question then this below lines

Dim o As IUIAutomation
    Dim e As IUIAutomationElement
    Set o = New CUIAutomation
    Dim h As Long
    h = ieApp.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
pmr
  • 998
  • 2
  • 13
  • 27
  • 1
    This is cool & congrats for finding a new solution to this; would you care to elaborate/explain the answer a little so that it may be more helpful to others with similar problems in the future? – David Zemens Oct 14 '16 at 13:47
  • 1
    I tried to utilize windows api here , references are as below https://msdn.microsoft.com/en-us/library/system.windows.automation.invokepattern(v=vs.110).aspx – pmr Oct 18 '16 at 15:24
  • 1
    Awsome!! Just to add -The reference to the dll UIAutomationCore.dll can be added only after changing the Macro Security(Macro Security(developer tab) -Macro Settings - Trust Access to the VBA Project object model). Also the path may be in system32 as well depending on windows version – sjd Nov 20 '17 at 07:38
  • thanks for that I totaly love it :) it helped me a lot – Filip Ondo Mar 22 '18 at 12:46
  • For IE11, have a look [here](https://stackoverflow.com/a/49695810/7991036). – Jerome Apr 06 '18 at 15:10
  • Hi, im sorry for being so stupid. but can you help me convert this into 64 window? im trying this code and it says it needs to be converted to 64 – deejay Jun 27 '21 at 14:08
4

Another way to do this is to send the keystrokes of the shortcut keys to click the save button in IE11. I should note your IE window will need to be the active window for this to work. Thus, it won't work while in debug mode.

The code below calls the shortcut key. I'm just showing the shortcut key so you have a better idea what's happening.

  • Shortcut key:Alt+S
  • VBA: Application.SendKeys "%{S}"
Tony L.
  • 17,638
  • 8
  • 69
  • 66
  • I'm confused. Event though my IE window is "activated" in order to scrap its HTML, this doesn't work for me. I am confused because it worked twice out of 30 tests I ran and I am not sure on why – Seb Apr 12 '17 at 09:57
  • @Seb when I say activated, I mean that the window has focus. If a different window has focus, `SendKeys` will go to that window. – Tony L. Apr 12 '17 at 18:15
2

as ieApp.hWnd in a 64bit environment is LongLong, where h is Long this yields a Type Mismatch which can easily been solved by

h = Clng(ieApp.hWnd)
Johan de Wispelaere
  • 21
  • 1
0

SendKeys was the solution for me.

myfile = "C:\Users\User\Downloads\myfile.xls" 
checkmyfile = Dir(myfile, vbArchive)

Do While checkmyfile = ""
    On Error Resume Next
    checkmyfile = Dir(myfile , vbArchive)
    If checkmyfile = "myfile.xls" Then Exit Do 
    AppActivate "Title - Internet Explorer"
    SendKeys "%(g)"
    Application.Wait Now + TimeValue("0:0:1")
Loop
Flash
  • 1