Need help opening an Excel file from IE and calling a new Sub on it

[KingInThePNW]

Hi all,

So here's where I'm at: I have a macro that will open IE, navigate to my page, fill in the fields, and generate a file for me, but here's the problem:

• There's some Java (I think) going on behind the scenes in my page that creates the Excel file that I need to download when I press a certain button on the screen, so I can't use DownloadToFile to get it
• In order to bypass this, I borrowed a 'Single Click' function that clicks 'Open' in the IE download dialog box.
• When I execute Single Click in my macro, the Excel file doesn't open! I have a loop after the Single Click to DoEvents and Activate all workbooks until it's open, but this doesn't happen. No errors, just a continuous loop after "clicking" Open.

Code is below. Any help would be super appreciated. Apologies if anything I wrote below is unconventional or inefficient, I've been teaching myself VBA and am not familiar with common protocol!

Sub IEopener()


Dim wbk As Workbook


Dim CYStartDate As Variant
CYStartDate = InputBox( _
    "Type in CY Start Date", _
    "Choose CY Start Date", _
    "MM/DD/YYYY")
    
If Len(CYStartDate) = 0 Then
MsgBox "Reporting Cancelled"
    Exit Sub
End If


Dim CYEndDate As Variant
CYEndDate = InputBox( _
    "Type in CY End Date", _
    "Choose CY End Date", _
    "MM/DD/YYYY")
    
If Len(CYEndDate) = 0 Then
MsgBox "Reporting Cancelled"
    Exit Sub
End If


Dim LYStartDate As Variant
LYStartDate = InputBox( _
    "Type in LY Start Date", _
    "Choose LY Start Date", _
    "MM/DD/YYYY")
    
If Len(LYStartDate) = 0 Then
MsgBox "Reporting Cancelled"
    Exit Sub
End If


Dim LYEndDate As Variant
LYEndDate = InputBox( _
    "Type in LY End Date", _
    "Choose LY End Date", _
    "MM/DD/YYYY")
    
If Len(LYEndDate) = 0 Then
MsgBox "Reporting Cancelled"
    Exit Sub
End If


Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
apiShowWindow ie.hwnd, SW_MAXIMIZE
ie.navigate "Ihavetokeepthisprivate.com\onlyafewpeopleareallowedtousethisreportingsiteatmycompany"
Do
DoEvents
Loop Until ie.READYSTATE = 4


Call ie.Document.GetElementByID("ctl144_ctl00_ctl03_txtValue").SetAttribute("value", CYStartDate)
Call ie.Document.GetElementByID("ctl144_ctl00_ctl05_txtValue").SetAttribute("value", CYEndDate)
Call ie.Document.GetElementByID("ctl144_ctl00_ctl07_txtValue").SetAttribute("value", LYStartDate)
Call ie.Document.GetElementByID("ctl144_ctl00_ctl09_txtValue").SetAttribute("value", LYEndDate)
Call ie.Document.GetElementByID("ctl144_ctl00_ctl11_ddValue").SetAttribute("value", "1")
Call ie.Document.GetElementByID("ctl144_ctl00_ctl13_ddValue").SetAttribute("value", "1")
Call ie.Document.GetElementByID("ctl144_ctl00_ctl00").Click


Do
DoEvents
Loop Until ie.READYSTATE = 4


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


Call ie.Document.GetElementByID("ctl144_ctl01_ctl05_ctl00").SetAttribute("value", "EXCEL")


Application.Wait Now + TimeValue("0:01:0")


Call ie.Document.GetElementByID("ctl144_ctl01_ctl05_ctl01").Click


Application.Wait Now + TimeValue("0:0:20")


Call SingleClick


Do
DoEvents
For Each wbk In Workbooks
wbk.Activate
'Workbooks("nameofgeneratedreport").Worksheets("nameofgeneratedreportsheet").Activate
Next wbk
Loop Until ActiveSheet.Name = "nameofgeneratedreportsheet"


Call Format


End Sub

Public Declare Function SetCursorPos Lib "user32" (ByVal x As Long, ByVal y As Long) As Long
Public Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Public Const MOUSEEVENTF_LEFTDOWN = &H2
Public Const MOUSEEVENTF_LEFTUP = &H4
Public Const MOUSEEVENTF_RIGHTDOWN As Long = &H8
Public Const MOUSEEVENTF_RIGHTUP As Long = &H10


Sub SingleClick()
  SetCursorPos 1200, 1020 'x and y position
  mouse_event MOUSEEVENTF_LEFTDOWN, 0, 0, 0, 0
  mouse_event MOUSEEVENTF_LEFTUP, 0, 0, 0, 0
End Sub