0

Hello I'm trying to automate the downloading part of my report automation. I was able to get the raw data and all via navigation but i'm now stuck in downloading it as my IE is older version. A pop out of "Open", "Save","Cancel". I can't post the website since it's a client web tool.

Commented part is what I've tried so far.

Sub Get_RawFile()
'
'
'
    Dim IE As New InternetExplorer
    Dim HTMLDoc As HTMLDocument
    Dim HTMLselect As HTMLSelectElement
    'Dim saveInFolder As String, saveAsFilename As String
    'saveInFolder = "C:"
    'saveAsFilename = "test_" & Format(Sheets("Attendance").Range("X6").Value, "yyyymmdd")

    With IE
        .Visible = True
        .Navigate ("---------------------")

    While IE.Busy Or IE.readyState <> 4: DoEvents: Wend

    Set HTMLDoc = IE.document
    HTMLDoc.all.UserName.Value = Sheets("Data Dump").Range("A1").Value
    HTMLDoc.all.Password.Value = Sheets("Data Dump").Range("B1").Value
    HTMLDoc.getElementById("login-btn").Click

    While IE.Busy Or IE.readyState <> 4: DoEvents: Wend
    Application.Wait (Now + TimeValue("0:00:05"))

    Set objButton = HTMLDoc.getElementById("s2id_ddlReportType")
    Set HTMLselect = HTMLDoc.getElementById("ddlReportType")
    objButton.Focus
    HTMLselect.Value = "2"

    Set HTMLselectZone = HTMLDoc.getElementById("ddlTimezone")
    HTMLselectZone.Value = "PST8PDT"

    Set subgroups = HTMLDoc.getElementById("s2id_ddlSubgroups")
    subgroups.Click
    Set subgroups2 = HTMLDoc.getElementById("ddlSubgroups")
    subgroups2.Value = "1456_17"

    HTMLDoc.getElementById("dtStartDate").Value = Format(Sheets("Attendance").Range("B6").Value, "yyyy-mm-dd")
    HTMLDoc.getElementById("dtEndDate").Value = Format(Sheets("Attendance").Range("X6").Value, "yyyy-mm-dd")

    HTMLDoc.getElementById("btnGetReport").Focus
    HTMLDoc.getElementById("btnGetReport").Click
    Application.Wait (Now + TimeValue("0:00:10"))

    HTMLDoc.getElementById("btnDowloadReport").Click 'Download report button

    'saveInFolder, saveAsFilename

    End With
End Sub
Hervie
  • 23
  • 6
  • When you download a file from Internet, you are making a `GET` http request for a resource. Check out [this answer](https://stackoverflow.com/a/17877390/3111149) to see how to set it up in VBA. As for your request, open your website on Google Chrome, Developer Tools, go into the Network tab and click on the button to download the file: you will see what is the request you send. – Matteo NNZ Jul 24 '19 at 11:59
  • You could refer to [this thread](https://stackoverflow.com/questions/32145437/controlling-ie11-do-you-want-to-open-save-dialogue-window-buttons-in-vba/32152712#32152712) and try to use "Application.SendKeys "%{S}"" to click the save button. Besides, you can also check [this thread](https://stackoverflow.com/questions/31489801/ie11-frame-notification-bar-save-button/49695810#49695810) to handle the download prompt. – Zhi Lv Jul 24 '19 at 15:22

0 Answers0