I have put together some code in VBA to naviate to a webpage in IE, wait for it to load, find a hyptertext link on the page named "spreadsheet", click it, wait for the "download file" window to load and the go through the process of saving the file into a specific directory using send keys.
The problem is, I am constantly losing focus on the windows. My code actually repeats itself 21 times for different webpages I am downloading this spreadsheet from. I need help finding the windows that I need to send keys to. Below is a sample piece of the code to navigate to one webpage: I have put notes to reflect where I am having issues. They are throughout this code and in red
Function import_atl_aspec() '(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim o2
Set o2 = CreateObject("internetexplorer.application")
o2.navigate "www.mywebpage.com," 'IE navigates to a webpage
mySleep 50 'wait 5 seconds
SendKeys "~" 'Here a "connect to" window pop's up to login to the site, it asks for a user name and password, which is already saved on the machine I am using. This window randomly loses focus, especially if there is another instance of IE running in the background, because the previous instance wasn't shut down "properly" Is there a way to put the username and password into the code so that it can automatically login without it having to be saved onto a particular PC Also How can I find this window and get focus to it, so that I can log into it.
o2.Visible = False 'hides IE
While o2.busy: DoEvents: Wend
Set o = o2.Document.All.tags("A")
M = o.length: mySubmit = -1
For r = 0 To M - 1
If InStr(1, o.Item(r).innerhtml, "Spreadsheet", vbTextCompare) Then
o.Item(r).focus
o.Item(r).Click: Exit For
End If
Next
mySleep 200 'wait 60 seconds
SendKeys "%S" 'Hits the save button in the "FILE DOWNLOAD" window
mySleep 50 'wait five seconds and starts typing in the path to save the file
SendKeys "G:\subgroup\access data\lBX\Raw Data\Current Week\ASPEC\tbl_atl_aspec.csv" ' ' Types the path in the Save As Dialog Box
mySleep 10 'waits 1 second
SendKeys "%S" 'Hits the save button
mySleep 20 'Waits 2 seconds and Msgbox appears: 'file exists - do you want to overwrite'
SendKeys "%y" 'Sends an ALT-Y to say YES overwrite the file
mySleep 200 ' Waits 10 Seconds
SendKeys "~" 'Sends the enter key to close the "Download Complete" window
o2.Quit
mySleep 20
Set o2 = Nothing
End Function