I am new to writing macros in Excel, but have created a macro that saves pictures to my hard drive from using a list of 40,000 internet explorer links. The code works fine for what appears to be 2 hours and 40 minutes and then stops returning the message.
Run-time error ‘-214074726 (800700aa)’
I can restart the program again right away, but it continues to stop after another 2 hours and 40 minutes. I know real programmers do not like using SendKeys, but this seems to work without an issue.
Does anyone have an idea of why the code would stop and what I could simply modify to avoid the stoppage? Thanks.
Wrcarr
Below is the code.
Sub View_and_Paste_Picture()
Dim rowcount As Integer
Dim urlToOpen As String
Dim file_name As String
Dim file_location As String
rowcount = 1
Set IE = CreateObject("InternetExplorer.Application")
' Show browser
IE.Visible = True
' Go row by row in Excel until a blank cell is found
Do While Sheets("Raw Data").Range("A" & rowcount) <> ""
urlToOpen = Sheets("Raw Data").Range("A" & rowcount).Value
file_name = Sheets("Raw Data").Range("M" & rowcount).Value
file_location = Sheets("Raw Data").Range("L" & rowcount).Value
' Open the links in the same browser
IE.Navigate2 urlToOpen
' Wait while IE loading...
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
' Ctrl s keys to call up the save window
SendKeys "^s"
Application.Wait (Now + TimeValue("0:00:01")) 'Time delay in H:MM:SS
‘ Type the file name from column M of Excel sheet
SendKeys file_name
Application.Wait (Now + TimeValue("0:00:01")) 'Time delay in H:MM:SS
‘ Five tab keys to get to the file location
SendKeys "{Tab}"
SendKeys "{Tab}"
SendKeys "{Tab}"
SendKeys "{Tab}"
‘ Enter key to enable editing of the file location
SendKeys "{Enter}"
Application.Wait (Now + TimeValue("0:00:01")) 'Time delay in H:MM:SS
‘ Type file location from column L of Excel sheet
SendKeys file_location
Application.Wait (Now + TimeValue("0:00:01")) 'Time delay in H:MM:SS
‘ Type enter to confirm file location
SendKeys "{Enter}"
Application.Wait (Now + TimeValue("0:00:01")) 'Time delay in H:MM:SS
‘ Alt s to save file in desired location
SendKeys "%s
Application.Wait (Now + TimeValue("0:00:01")) 'Time delay in H:MM:SS
‘ Go to the next row in Excel
rowcount = rowcount + 1
Loop
'Close Browser
IE.Quit
End Sub
Bookmarks