VBA - Run Time Error Object Variable

Question

I am running into a problem with the following code, specifically when hitting run I am getting the error message "Run Time Error 91 : Object Variable or With Block Variable Not Set".

I have attached a picture of what line is highlighted in yellow. Thanks

VBA Code, run time error object

Sub Data_Get()

Dim ActiveSheet As Worksheet
Dim EndDate As Date
Dim StartDate As Date
Dim Symbol As String
Dim qurl As String
Dim nQuery As Name
Dim LastRow As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Columns("B:G").ClearContents

StartDate = Range("K2").Value
EndDate = Range("K3").Value
Symbol = Range("K1").Value


qurl = "http://finance.google.com/finance/historical?q=" & Symbol
qurl = qurl & "&startdate=" & MonthName(Month(StartDate), True) & _
       "+" & Day(StartDate) & "+" & Year(StartDate) & _
       "&enddate=" & MonthName(Month(EndDate), True) & _
       "+" & Day(EndDate) & "+" & Year(EndDate) & "&output=csv"

QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=Range("b1"))
    .BackgroundQuery = True
    .TablesOnlyFromHTML = False
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With

Range("B1").CurrentRegion.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
                                                       TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                                       Semicolon:=False, Comma:=True, Space:=False, other:=False

Columns("B:G").ColumnWidth = 12

LastRow = ActiveSheet.UsedRange.Row - 2 + ActiveSheet.UsedRange.Rows.Count

ActiveSheet.Sort.SortFields.Add Key:=Range("B2:B" & LastRow), _
                                   SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveSheet.Sort
    .SetRange Range("B1:G" & LastRow)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    .SortFields.Clear
End With

End Sub

Please enter the code in question using the <code></code> tags instead of just plugging in a link to an image. And if you do link an image, actually entering an image description other than "enter image description here" might help as well... — Christian Severin
– Christian Severin, Commented Feb 19, 2017 at 15:34
Im new to this, I tried pasting the code and kept getting error messages. — Texcel
– Texcel, Commented Feb 19, 2017 at 15:43
OK, and what happens when you enclose the code in <code> </code> tags? Just paste your code, mark it and click on the code button on top of the question edit window. — Christian Severin
– Christian Severin, Commented Feb 19, 2017 at 15:44
Good job! Now we'll just wait for someone to come along with an answer. Oh, and: welcome to StackOverflow! :-) — Christian Severin
– Christian Severin, Commented Feb 19, 2017 at 15:47

Texcel · Accepted Answer · 2017-02-19 22:02:54Z

couldn't tell you what or why, but the I changed it up slightly and the following worked:

    Sub Data_Get()

Dim ws As Worksheet
Dim EndDate As Date
Dim StartDate As Date
Dim Symbol As String
Dim qurl As String
Dim nQuery As Name
Dim LastRow As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Columns("B:G").ClearContents

Set ws = ActiveSheet

StartDate = ws.Range("K2").Value
EndDate = ws.Range("K3").Value
Symbol = ws.Range("K1").Value
Range("b1").CurrentRegion.ClearContents


qurl = "http://finance.google.com/finance/historical?q=" & Symbol
qurl = qurl & "&startdate=" & MonthName(Month(StartDate), True) & _
       "+" & Day(StartDate) & "+" & Year(StartDate) & _
       "&enddate=" & MonthName(Month(EndDate), True) & _
       "+" & Day(EndDate) & "+" & Year(EndDate) & "&output=csv"

QueryQuote:
With ws.QueryTables.Add(Connection:="URL;" & qurl, Destination:=Range("b1"))
    .BackgroundQuery = True
    .TablesOnlyFromHTML = False
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With

ws.Range("b1").CurrentRegion.TextToColumns Destination:=ws.Range("b1"), DataType:=xlDelimited, _
                                                       TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                                       Semicolon:=False, Comma:=True, Space:=False, other:=False

ws.Columns("B:G").ColumnWidth = 12

LastRow = ws.UsedRange.Row - 2 + ws.UsedRange.Rows.Count

ws.Sort.SortFields.Add Key:=Range("B2:B" & LastRow), _
                                   SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ws.Sort
    .SetRange Range("B1:G" & LastRow)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    .SortFields.Clear
End With

End Sub

Collectives™ on Stack Overflow

VBA - Run Time Error Object Variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related