I am automating some data compilation from reports in the web application of my workplace. The reports are found in different Hubs/Centers which I need to switch from one to the other. I am able to write the Excel Macro to navigate to the webpage to change the Hub/Center but when my code clicks the confirm button of the web application to change Hub there is a popup window activated by the onclick event. The popup requires me to click on its "yes" button manually in order for the macro to continue to the selected Hub/Centre to get the report. What I want is the Macro to do the clicking to fully automate the process.
The code that I have written is appended below and the part which clicks the web application's confirm button is in RED
The HTML source code for the confirm button is as below
<input type="button" style="WIDTH: 110px; HEIGHT: 24px; Background-Color:#1E90FF; Border-Color:white; Color:white; Font-Weight:bold;Font-family:Verdana; Cursor:hand; " alt="Save" id="btnSave" onclick="javascript:Validation()" value="Confirm">
The full HTML source code is attached as .txt file HTML source code.txt
If opened with Notepad++ the code is on line number 477
Thanks in advance for any help or guidance for the solution of the above problem
Dim StDat As String, NDat As String, ClgDat1 As String, ClgDat As String
Dim HubNo As Integer, i As Integer, i1 As Integer, r As Integer, n As Integer, n1 As Integer
Dim IE As InternetExplorer
Dim elem As IHTMLElement
Dim ieDoc As Object
StDat = InputBox("Please Enter the Starting Clearing Date, format dd/mm/yyyy or enter 'n/N' to terminate", _
"Enter Start Clearing Date or Terminate")
If StDat = "n" Or StDat = "N" Then GoTo Terminated
Do While Not IsDate(StDat) Or Weekday(StDat) = 1 Or Weekday(StDat) = 7 Or CDate(StDat) <= DateAdd("m", -3, Date) _
Or CDate(StDat) > DateAdd("d", -1, Date)
MsgBox ("You enter a date in the wrong format or an invalid date!" & vbNewLine & "Click 'OK' to reenter."), vbCritical, Error
StDat = InputBox("Please Enter the Clearing Date, format dd/mm/yyyy or enter 'n/N' to terminate", _
"Enter Start Clearing Date or Terminate")
If StDat = "n" Or StDat = "N" Then GoTo Terminated
Loop
NDat = InputBox("Please Enter the Ending Clearing Date, format dd/mm/yyyy or enter 'n/N' to terminate", _
"Enter End Clearing Date or Terminate")
If NDat = "n" Or NDat = "N" Then GoTo Terminated
Do While Not IsDate(NDat) Or Weekday(NDat) = 1 Or Weekday(NDat) = 7 Or CDate(NDat) <= DateAdd("m", -3, Date) _
Or CDate(NDat) > DateAdd("d", -1, Date) Or CDate(StDat) > CDate(NDat)
MsgBox ("You enter a date in the wrong format or an invalid date!" & vbNewLine & "Click 'OK' to reenter."), vbCritical, Error
NDat = InputBox("Please Enter the Ending Clearing Date, format dd/mm/yyyy or enter 'n/N' to terminate", _
"Enter End Clearing Date or Terminate")
If NDat = "n" Or NDat = "N" Then GoTo Terminated
Loop
ClgDat1 = Format(NDat, "yyyy") + "-" + Format(NDat, "mm") + "-" + Format(NDat, "dd")
Set IE = New InternetExplorer
With IE
.Visible = False
.Navigate "https://icsweb.hlbank.my/incheqs_ics/RegionBranch/Verification10kListing.asp?TaskID=1122&LoadDate=" & ClgDat1 & _
"&PreBankCode=&PreStateCode=&BankCode=&TransCode=&RejectCode=&BranchCode=&AccountNo=&ChequeNo=&UIC="
While .Busy = True Or .ReadyState < 4: DoEvents: Wend
.Document.execCommand "SelectAll", False
.Document.execCommand "Copy", False
End With
Range("A2").Select
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=True
ActiveSheet.Cells.WrapText = False
ActiveSheet.Cells.MergeCells = False
HubNo = CInt(Right$(Cells(10, 1), 1))
r = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(2, 1), Cells(r, 12)).ClearContents
r = Cells(Rows.Count, 13).End(xlUp).Row
Range(Cells(1, 13), Cells(r, 22)).ClearContents
For i = 1 to 4
If HubNo = 1 then Goto GetNCFHubRpt:
With IE
.Visible = False
.Navigate "https://icsweb.hlbank.my/incheqs_ics/RegionBranch/CentralizationHubSwitching.asp?TaskID=6011&LoadDate=" & ClgDat1 & _
"&CenCode=HUB " & i & "&AllCentralizeBranch="
While .Busy = True Or .ReadyState < 4: DoEvents: Wend
While .Document.ReadyState <> "complete": DoEvents: Wend
For Each elem In .Document.getElementsByName("userCenter")
If elem.ID = "userCenterparam" Then
elem.Click
End If
Next
For Each elem In .Document.getElementsByTagName("input")
If elem.ID = "btnSave" Then
elem.Click
End If
Next
End With
GetNCFHubRpt:
n = DateDiff("d", StDat, NDat)
n1 = 0
For i1 = 1 to n + 1
ClgDat = DateAdd("d", i1-1, StDat)
If Weekday(ClgDat) = 1 Or Weekday(ClgDat) = 7 Then Goto NextDay
With IE
.Visible = False
.Navigate "https://icsweb.hlbank.my/incheqs_ics/Report/rptNCF7.asp?LoadDate=" & Format(ClgDat, "dd/mm/yyyy") & _
"&PayBranch=&sChequeStatus="
While .Busy = True Or .ReadyState < 4: DoEvents: Wend
.Document.execCommand "SelectAll", False
.Document.execCommand "Copy", False
End With
Range("A2").Select
ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False
ActiveSheet.Cells.WrapText = False
ActiveSheet.Cells.MergeCells = False
r = Cells(Rows.Count, 1).End(xlUp).Row
If r = 13 Then Goto NextDay
n1 = n1 + 1
If i = 1 then Cells(n1, 13) = Format(Clgdat, "dd/mm/yyyy")
Cells(n1, 13 + i) = Cells(1, 3).Value
If Weekday(ClgDat) = 3 Then Cells(n1, 18 + i) = Cells(1, 5).Value
Range(Cells(2, 1), Cells(r, 12)).ClearContents
NextDay:
Next i1
Next i
If HubNo = 4 Then Goto Finished
With IE
.Visible = False
.Navigate "https://icsweb.hlbank.my/incheqs_ics/RegionBranch/CentralizationHubSwitching.asp?TaskID=6011&LoadDate=" & ClgDat1 & _
"&CenCode=HUB " & HubNo & "&AllCentralizeBranch="
While .Busy = True Or .ReadyState < 4: DoEvents: Wend
While .Document.ReadyState <> "complete": DoEvents: Wend
For Each elem In .Document.getElementsByName("userCenter")
If elem.ID = "userCenterparam" Then
elem.Click
End If
Next
For Each elem In .Document.getElementsByTagName("input")
If elem.ID = "btnSave" Then
elem.Click
End If
Next
End With
Finished:
MsgBox "Program execution finished"
IE.Quit
Set IE = Nothing
ThisWorkbook.Worksheets(1).Activate
Application.Goto Range("A1"), True
ThisWorkbook.Save
End Sub