Set downloaded workbook as active workbook which h

Question

I have a macro which will download an excel from website. It works until clicking on open in the website. But the excel file is not getting opened. Also i like to have variable set for the newly downloaded file. Below is the code whcih i have used. It gets struck in While Workbooks.Count <= w.

Sub download2()

Dim ie As Object
Dim myValue As Variant
Dim downloadBook As Workbook
Dim w As Long
w = Workbooks.Count

Set ie = CreateObject("INTERNETEXPLORER.Application")
    ie.navigate "https:www.safdsg.com"
    ie.Visible = True

Application.Wait (Now + TimeValue("00:00:05"))

    With ie.document

        .getElementById("AJS_DROPDOWN__15").Click

Application.Wait (Now + TimeValue("00:00:05"))

        .getElementById("currentExcelFields").Click

Application.Wait (Now + TimeValue("00:00:05"))

    End With

SendKeys "%{O}"

While Workbooks.Count <= w
    DoEvents
Wend

Set downloadBook = ActiveWorkbook

MsgBox "Completed"

End Sub

Also Do While ie.readyState = 4: DoEvents: Loop or Do Until ie.readyState = 4: DoEvents: Loop is not working in this code. Hence i have used application.wait. Please let me know if anything needs to be added in reference to make it work.

Answer 1

Assuming it opens by default in the same instance of Excel.Application, it is probably then the ActiveWorkbook, or Workbooks(Workbooks.Count), and you can declare a workbook variable and assign from that.

Dim downloadBook as Workbook
Dim w as Long
w = Workbooks.Count
' code that downloads the workbook...
' ...
' 
SendKeys "%{O}"
' ## Wait until the new file has opened
While Workbooks.Count < w + 1
    DoEvents
Wend
Set downloadBook = ActiveWorkbook
' OR:
' Set downloadBook = Workbook(w+1)