VBA lost Access to IE and stopped working? Runtime 438 Error

Photomofo

Active Member
Joined
Aug 20, 2012
Messages
259
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Here are the references I have picked up.

https://www.dropbox.com/s/60c8voin2njk771/vba help.jpg

vba%20help.jpg
 
Upvote 0

Forum statistics

Threads
1,216,575
Messages
6,131,501
Members
449,654
Latest member
andz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top