I'm scraping a website for some data using the following program. The program was working as expected until I decided to add a save file element that was a copy paste of some code a friend gave me (attached below). Funny thing is the save file functionality works but now I can't get IE to load the webpage - I get a 438 Error. I'm thinking there's a problem with something in the references menu.
Code:
Sub GET_PVI()
Dim Days_in_Month(1 To 12) As Integer
Dim Name As String
Dim Month(1 To 12) As String
Dim PVI(0 To 20) As String
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
Dim Filename As String
Dim theURL, theFile As String
Dim didItGo As Boolean
Month(1) = "Jan"
Month(2) = "Feb"
Month(3) = "Mar"
Month(4) = "Apr"
Month(5) = "May"
Month(6) = "Jun"
Month(7) = "Jul"
Month(8) = "Aug"
Month(9) = "Sep"
Month(10) = "Oct"
Month(11) = "Nov"
Month(12) = "Dec"
Days_in_Month(1) = 31
Days_in_Month(3) = 31
Days_in_Month(4) = 30
Days_in_Month(5) = 31
Days_in_Month(6) = 30
Days_in_Month(7) = 31
Days_in_Month(8) = 31
Days_in_Month(9) = 30
Days_in_Month(10) = 31
Days_in_Month(11) = 30
Days_in_Month(12) = 31
'Start here
Month_x = 2
Year_x = 2013
Day_of_Month = 13
Step_x = 0
Do While Year_x < 2014
If Year_x = 2012 Then
Days_in_Month(2) = 29
ElseIf Year_x = 2016 Then
Days_in_Month(2) = 29
Else
Days_in_Month(2) = 28
End If
If Day_of_Month > 9 Then
Name = Month(Month_x) & "-" & Day_of_Month & "-" & Year_x
Else
Name = Month(Month_x) & "-" & 0 & Day_of_Month & "-" & Year_x
End If
If Cells(1, 194 + Step_x) <> 0 Then
Step_x = Step_x + 1
Else
'Go to PVI
theURL = "http://pvinsights.com/"
'ThisWorkbook.FollowHyperlink Address:=theURL, NewWindow:=False
Filename = "S" & 3300 + Step_x & " - PVinsights Price Update - " & Name & ".html"
Filename = "C:\Users\John\Desktop\SOLAR\Sources\" & Filename
didItGo = SaveWebFile(theURL, Filename)
IE.Navigate = theURL
IE.Visible = True
While IE.Busy
DoEvents
Wend
IE.ExecWB 17, 0 '// SelectAll
IE.ExecWB 12, 2 '// Copy selection
Application.Wait DateAdd("s", 5, Now)
'Create Scratch worksheet and paste PVinsights page
Worksheets.Add().Name = "Scratch"
Sheets("Scratch").Activate
ActiveSheet.PasteSpecial Format:="text", link:=False, DisplayAsIcon:=False
'Record data into Array
ActiveSheet.Range("A278").Select
PVI(0) = Name
PVI(1) = ActiveSheet.Range("A278")
PVI(2) = ActiveSheet.Range("A292")
PVI(3) = ActiveSheet.Range("A343")
PVI(4) = ActiveSheet.Range("A357")
PVI(5) = "N/A"
PVI(6) = ActiveSheet.Range("A371")
PVI(7) = ActiveSheet.Range("A415")
PVI(8) = ActiveSheet.Range("A429")
PVI(9) = ActiveSheet.Range("A443")
PVI(10) = "N/A"
PVI(11) = ActiveSheet.Range("A457")
PVI(12) = ActiveSheet.Range("A501")
PVI(13) = ActiveSheet.Range("A515")
'Add a column
Cells(1, 194 + Step_x).Select
Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
'Paste Data to PVI worksheet
Sheets("PVinsights").Select
ARG = 0
Do While ARG < 14
Cells(ARG + 1, 194 + Step_x) = PVI(ARG)
ARG = ARG + 1
Loop
Sheets("Scratch").Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
GoTo Escape_Hatch
End If
Next_Day_of_Month = Day_of_Month + 7
If Next_Day_of_Month <= Days_in_Month(Month_x) Then
Day_of_Month = Next_Day_of_Month
Else
Day_of_Month = Next_Day_of_Month - Days_in_Month(Month_x)
If Month_x + 1 = 13 Then
Month_x = 1
Year_x = Year_x + 1
Else
Month_x = Month_x + 1
End If
End If
Loop
Escape_Hatch:
End Sub
Code:
Function SaveWebFile(ByVal vWebFile As String, ByVal vLocalFile As String) As Boolean
On Error GoTo theEnd:
Dim oXMLHTTP As Object, vFF As Long, oResp As String
Set oXMLHTTP = CreateObject("Microsoft.XMLHTTP")
oXMLHTTP.Open "GET", vWebFile, False
oXMLHTTP.Send
oResp = oXMLHTTP.ResponseText
vFF = FreeFile
If Dir(vLocalFile) <> "" Then Kill vLocalFile
Open vLocalFile For Output As #vFF
Print #vFF, oResp
Close #vFF
Set oXMLHTTP = Nothing
SaveWebFile = True
Exit Function
theEnd:
On Error GoTo 0
SaveWebFile = False
End Function