Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recognizing When An IE window changes URLs and closing the IE wind
I am running a script that does an automatic login for 100's of training
ID's. We have the script that opens the browser (uses followhyperlink) and enters the user name and password using sendkeys. What I need to know how to do is determine when the window has correctly opened and when it hasn't. When it is correct, it goes to a very specific URL. Ideally, I would check the URL after our Wait statement and determine if the URL is the correct one and annotate on the spreadsheet error or complete. In either case, I need to close the IE window after the check because the VBA blows up after 50 IE windows. Right now I am using Excel 2003. To Clarify: How do I get the address of an open IE window using VBA? How do I close an open IE window using VBA? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recognizing When An IE window changes URLs and closing the IE wind
Here is one I modded from the following website:
http://www.codingforums.com/showthread.php?t=125767 Maybe it will get you started. Mark Ivey Sub test() Set oIE = CreateObject("InternetExplorer.Application") oIE.Navigate "about:blank" Do Loop While oIE.Busy oIE.Visible = True Application.Wait Now + TimeValue("00:00:05") oIE.Document.WriteLn "<html<head<titlePage Title</title</head<body<div id='output'test</div</body</html" Set oDIV = oIE.Document.All("output") 'Wait 5 seconds then change the output int the window Application.Wait Now + TimeValue("00:00:05") oDIV.InnerHTML = "I changed it" 'Wait 5 seconds then close the application Application.Wait Now + TimeValue("00:00:05") oIE.Quit End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recognizing When An IE window changes URLs and closing the IE
Mark:
I used someone else's solution I eventually found. It involved using a Class Module. I am posting the code here. Your code did help me amend the code. I was just making the instance of IE not visible instead of quitting. To the person who supplied the code, thanks. Class Module Code: Public WithEvents x As InternetExplorer Public y As InternetExplorer Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal _ lpClassName As String, ByVal lpWindowName As String) As Long Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam _ As Long) As Long Private Sub x_NewWindow2(ppDisp As Object, Cancel As Boolean) Set y = ppDisp End Sub Public Sub SetVisible(visible As Boolean) x.visible = visible End Sub Public Sub Navigate(destURL) x.Navigate2 destURL LoadPage End Sub Public Sub LoadPage() ' Pauses execution until the browser window has finished loading Do While x.Busy Or x.ReadyState < READYSTATE_COMPLETE PostMessage FindWindow("#32770", "Microsoft Internet Explorer"), _ &H10, 0&, 0& DoEvents Loop End Sub Public Function Button_name(tagType, Caption As String) As Boolean ' Clicks the element of type tagType containing Caption or returns false if element cannot be found Dim Element Button = True Dim AllElements Set AllElements = x.Document.getElementsByTagName(tagType) For Each Element In AllElements tempAlt = Element.Name If InStr(Element.Name, Caption) 0 Then Call Element.Click Call LoadPage Exit Function End If Next Element Button = False End Function Module 1 Subroutine Sub URL_Test2() Dim ie1 As New IEClass Set ie1.x = New InternetExplorer ie1.SetVisible True Dim varURL As String varURL = "http://secure website" ie1.Navigate varURL Dim user, pword As String ' Decalres the variables for the suername, old password and new password Dim curRow, CurCol As Integer ' Declares the variables for the current row and current column, used to change to the next user Dim NoRows 'Declares the variable to set the iterations of the loop Dim IEPage, IESuccess As String On Error GoTo Here ' Error handling sequence that breaks the loop, displays a messagebox, and ends the subroutine ' curRow = 1 ' sets the current row to the first row in the spreadsheet CurCol = 2 ' sets the current column to the second column in the spreadsheet NoRows = Worksheets("Sheet1").Cells(curRow, CurCol).Value 'Assigns the number of rows variable NoRows to the value located in the current cell at row 1, column 2 curRow = curRow + 3 ' increments the current row by 3 to make the current row the fourth row in the spreadsheet CurCol = CurCol - 1 ' decreases the current column by 1 to make the current column the first column in the spreadsheet IESuccess = "http://Correctly changed website" For Counter = 1 To NoRows ' The beginning of the loop with a range of 1 to the number of rows stored in the NoRows variable newHour = Hour(Now()) ' An undimensioned variable that is set to the current hour based on the computer system time newMinute = Minute(Now()) ' An undimensioned variable that is set to the current minute based on the computer system time newSecond = Second(Now()) + 3 ' An undimensioned variable that is set to the current second based on the computer system time and adds 3 seconds waitTime = TimeSerial(newHour, newMinute, newSecond) ' An undimensioned variable that is set to the current hour, minute and seconds plus based on the computer system time This is used as a pause in the loop user = Worksheets("Sheet1").Cells(curRow, CurCol).Value ' assigns the user variable to the username in the first column and the current row as defined by the for next loop CurCol = CurCol + 1 ' changes the current column to the second column on the worksheet pword = Worksheets("Sheet1").Cells(curRow, CurCol).Value ' assigns the oldpword variable to the password in the second column and the current row as defined by the for next loop varURL = "http://secure web site" ie1.Navigate varURL 'ActiveWorkbook.FollowHyperlink Address:="https://secure website", _ 'NewWindow:=True ' Opens a new web browser window to the change password URL Application.Wait waitTime Application.SendKeys (user) Application.SendKeys ("{TAB}") Application.SendKeys (pword) Application.SendKeys ("~") Application.SendKeys ("{TAB}") Application.SendKeys ("~") newSecond = Second(Now()) + 7 waitTime = TimeSerial(newHour, newMinute, newSecond) Application.Wait waitTime IEPage = ie1.x.LocationURL If IEPage = IESuccess Then Worksheets("Sheet1").Cells(curRow, 5).Value = "complete" Application.Cells(curRow, 5).Activate Application.ActiveCell.Font.Color = RGB(0, 255, 0) ' Changes the font to the color green to indicate the value has been changed. Else Worksheets("Sheet1").Cells(curRow, 5).Value = "ERROR" Application.Cells(curRow, 5).Activate Application.ActiveCell.Font.Color = RGB(255, 0, 0) ' Changes the font to the color red to indicate the value has been changed. End If curRow = curRow + 1 ' Increments the current row by one CurCol = CurCol - 1 ' Changes to current column to the first column on the worksheet Next Counter ' The end of the For Next Loop. If the loop is not finished it restarts the loop. If the loop is finished, it ends the loop and goes to the next line of the subroutine GoTo EndSub ' Jumps to a bookmark titled EndSub He ' A bookmark called Here used by the error handler MsgBox ("An error has occurred. Check the last window to determine the stopped point.") ' A pop up message box to inform the user that an error has occurred EndSub: ' A bookmark called EndSub used to jump over the error handler code ie1 Quit End Sub "Mark Ivey" wrote: Here is one I modded from the following website: http://www.codingforums.com/showthread.php?t=125767 Maybe it will get you started. Mark Ivey Sub test() Set oIE = CreateObject("InternetExplorer.Application") oIE.Navigate "about:blank" Do Loop While oIE.Busy oIE.Visible = True Application.Wait Now + TimeValue("00:00:05") oIE.Document.WriteLn "<html<head<titlePage Title</title</head<body<div id='output'test</div</body</html" Set oDIV = oIE.Document.All("output") 'Wait 5 seconds then change the output int the window Application.Wait Now + TimeValue("00:00:05") oDIV.InnerHTML = "I changed it" 'Wait 5 seconds then close the application Application.Wait Now + TimeValue("00:00:05") oIE.Quit End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Closing a window after transferring data | Excel Discussion (Misc queries) | |||
Self closing "modal" window ? | Excel Programming | |||
Can I insert a pop up window in Excel to ask a ? b/4 closing? | Excel Discussion (Misc queries) | |||
Closing DOS window after shell command | Excel Programming | |||
Closing a Window | Excel Programming |