VBA Script error and may be from going from Excel 2019 to Excel 365

Saskatchewan

New Member
Joined
Mar 15, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I am trying to run an excel workbook that I originally wrote in Excel 2019, but, when I run it in Excel 365. I think that I am posting code correctly, so, if I didn't apologies ahead of time.
This is an Excel VBA workbook that I wrote in September but have not touched VBA since, so I am super rusty.
When I try running it, I get a "Run time error '424' Object Required"
Is there something that I forgot to enable? I guess that I can't upload the workbook...

Code:
Option Explicit

Sub HamsWednesday()

'Wednesday Date Update
Sheets("Weekly Prices_Wednesday").Range("C1").Copy
Sheets("Weekly Prices_Wednesday").Range("H2").PasteSpecial Paste:=xlPasteValues

'Define Variables
        Dim Obj(2) As Object, S$(), V, T$, R&
        Set Obj(0) = CreateObject("WinHttp.WinHttpRequest.5.1")
        Set Obj(1) = CreateObject("htmlfile")
        ReDim S(1 To Rows.Count, 0)

'Clear Data From Wednesday Cells
Worksheets("Weekly Prices_Wednesday").Columns(1).ClearContents
Worksheets("WednesdayValues").Columns("A:B").ClearContents
 Sheets("Weblinks_Wednesday").Columns("G").Copy
 Sheets("WednesdayValues").Range("A1").PasteSpecial Paste:=xlPasteValues

'Source Data from Wednesday Websites
        Application.Cursor = xlWait
        Worksheets("WednesdayValues").Activate
    For Each V In [A1].CurrentRegion.Value2
            T = ""
            Obj(0).Open "GET", V, False
            Obj(0).setRequestHeader "DNT", "1"
            On Error Resume Next
            Obj(0).send
            If Obj(0).Status = 200 Then T = Obj(0).responseText
            On Error GoTo 0
        If T > "" Then
                 Obj(1).body.innerHTML = T
            With Obj(1).getElementsByTagName("H2")(4)
                    R = R + 1:  S(R, 0) = .innerText
                For Each Obj(2) In .NextSibling.Children
                    R = R + 1:  S(R, 0) = Obj(2).innerText
                Next
            End With
                DoEvents
        End If
    Next
        If R Then [B1].Resize(R).Value2 = S
        Application.Cursor = xlDefault
        Erase Obj
        
'Return Wednesday Values from Sites for Calculations
Sheets("WednesdayValues").Columns("B").Copy
Sheets("Weekly Prices_Wednesday").Range("A1").PasteSpecial Paste:=xlPasteValues

End Sub
 

Attachments

  • runtimeerror.JPG
    runtimeerror.JPG
    18.8 KB · Views: 14

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
That would suggest that Obj(1).getElementsByTagName("H2")(4) is not returning anything. Perhaps the source page has changed in the meantime.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

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