When it asks for username and password, excel will hold the code execution. So no use putting code for SendKeys in excel itself. I made similar automation program, of checking for new data on a web site and updating the new data in excel. This program runs 1:00 AM in the night. What I did is at 1:00 AM I launch a Winword document (Task Scheduler). This winword document in it's open even launches the excel workbook through Shell command, so that the excel application ID is known. Allowing some time for the username and password dialog box to appear, winword sends username and password to the excel using AppActivate first. Code in Winword is something like this. (In the winword document, there is a single table with single cell, to store the ApplicationID).
[CODE]
Private Sub Document_Open()
Application.OnTime Now + TimeValue("00:00:02"), "OpMyWeb"
End Sub
[.CODE]
Then the procedures are in modules as under:
Code:
Sub OpMyWeb()
Dim myIDnum As Long
myIDnum = Shell("C:\Program Files\Microsoft Office\OFFICE11
\EXCEL.EXE D:\IntraTrack\WebQuery.xls", vbNormalFocus)
ThisDocument.Tables(1).Cell(1, 1).Select
Selection.Text = myIDnum
Application.OnTime Now + TimeValue("00:00:10"), "EnQuer"
End Sub
So allowing 10 seconds, Procedure EnQuer runs, which is only for clicking on 'Enable Auto Web Query'. (You may not need this, and you can jump to SendPass.)
Code:
Sub EnQuer()
Dim myIDnum As Long
ThisDocument.Tables(1).Cell(1, 1).Select
myIDnum = Left(Selection.Text, Len(Selection.Text) - 2)
AppActivate myIDnum
SendKeys "{TAB}", True
SendKeys "{ENTER}", True
ThisDocument.Activate
Application.OnTime Now + TimeValue("00:00:30"), "SendPass"
End Sub
So allowing 30 seconds for username & pwd box to appear, I run SendPass procedure, which sends the user name and passowrd, and then winword
document is closed.
Code:
Sub SendPass()
Dim myIDnum As Long
ThisDocument.Tables(1).Cell(1, 1).Select
myIDnum = Left(Selection.Text, Len(Selection.Text) - 2)
AppActivate myIDnum
SendKeys "username", True
SendKeys "{TAB}", True
SendKeys "password", True
SendKeys "{ENTER}", True
ThisDocument.Activate
ThisDocument.Saved = True
If Application.Windows.Count = 1 Then Application.Quit
ThisDocument.Close
End Sub
Bookmarks