0

I have issues with a code I am writing on VBA. I am basically using vba to open a website, input information and then click a download button, which downloads a csv that I can then copy and put onto my excel file. I do manage to get everything done up to the clicking download button. When I do that a Dialogue window shows up for IE11 asking me whether I want to save or open the file. I have no idea how solve this issue and how to click open or save. I have tryed everything mentioned in a solution for the same problem in Controlling IE11 "Do you want to Open/Save" dialogue window buttons in VBA , but even though I use this solution, the code runs but just does not do anything.

I have tried using the UIAutomation but I have been unsuccessful . I used the solution provided in Controlling IE11 "Do you want to Open/Save" dialogue window buttons in VBA. I have also tried using Sendkeys but I am not entirely sure how to set the focus on Internet explorer so that the sendkeys do what they are supposed to.

Code is actually very simple

Sub GetHTMLDocument()

Dim ie As New SHDocVw.InternetExplorer
Dim HTMLDoc As HTMLDocument
Dim HTMLInput As MSHTML.IHTMLElement
Dim HTMLButtons As MSHTML.IHTMLElementCollection
Dim HTMLButton As MSHTML.IHTMLElement


ie.Visible = True
ie.Navigate "this is where i put the website"

Do While ie.ReadyState <> READYSTATE_COMPLETE
Loop

Set HTMLDoc = ie.Document
Set HTMLInput = HTMLDoc.getElementById("Search")
HTMLInput.Value = "Hello"
Set HTMLInput = HTMLDoc.getElementById("downloadCSV")
HTMLInput.Click


End Sub

Unfortunately I cannot give the website and I cannot use a public one, after that I get the message whether to open or save it on internet explorer

Wolf Kolev
  • 121
  • 1
  • 1
  • 8
  • 1
    public url at all? – QHarr Jul 04 '19 at 19:18
  • to use the url you need a vpn access unfortunately so I won't be able to send the url, I know that complicates things up – Wolf Kolev Jul 04 '19 at 19:29
  • Can you find a different website where the basic structure of your code will reproduce the problem? Also, including your current best coding attempt will help to avoid any downvotes as why isn't my code working questions usually need to include the code. It is clear you have done research. – QHarr Jul 04 '19 at 20:18
  • I have edited the post – Wolf Kolev Jul 04 '19 at 21:08
  • Someone should probably close this and marks as duplicate of https://stackoverflow.com/questions/11652890/vba-interaction-with-internet-explorer – spioter Jul 05 '19 at 12:48

1 Answers1

0

Possible duplicate of VBA interaction with internet explorer

Though this answer may be more clear for this specific question.

1) add this to top of your module - It creates a function that will allow you to give any window focus. I have no idea on the details. 

Public Declare Function SetForegroundWindow Lib "user32" (ByVal HWND As Long) As Long

2) Modify this code to taste in order to use SendKeys

        'give IE focus to prepare for SendKeys
            ' IE.HWND = the handle of the Windows Internet Explorer main window.
                Dim HWNDSrc As Long
                HWNDSrc = IE.HWND
                SetForegroundWindow HWNDSrc

        'Make sure IE is not busy
            Do While IE.Busy
                Application.Wait DateAdd("s", 1, Now)
            Loop

        'send Alt-S to save
            Application.SendKeys "%{S}"

        'Make sure IE is not busy
             Do While IE.Busy
                 Application.Wait DateAdd("s", 1, Now)
             Loop
spioter
  • 1,829
  • 1
  • 13
  • 19
  • I will give it a try and update on whether it has worked for me. I do thank you in advance for this though – Wolf Kolev Jul 05 '19 at 13:48
  • Huge thanks, this worked and it is the only solution that has worked for me, Really appreciate the help. – Wolf Kolev Jul 05 '19 at 15:40
  • You are welcome. I am just paying it forward for all the help I've received here. I recommend you do the same whether here or real life, you will thank yourself for doing so ;-) – spioter Jul 07 '19 at 20:55