How to fix Runtime Error 91 in Excel VBA script
I have created a little script that takes some data from a website table and fills it into cells in Excel. I need this in order to make searching through that data and filtering it easier for myself. However I have ran into a little problem, every time I run my script it does what it's supposed to do for around 9 to 11 pages (about 250 cell entries) and then stops with an error message popping up saying "Runtime Error 91" Object variable or With block variable not set. It never runs all the way through till the end and I only get a small fraction of the data that I was supposed to get.
I am not an expert in programming and even less of an expert in VBA, so if I'm doing something wrong please let me know. I've been stuck on this problem for a few days. I do not know what to do to fix this problem. I've looked for fixes on Google, YouTube and even Microsoft's own website, but can't figure out why this error is popping up in my own script.
I know that this problem is encountered when; 1) you attempt to use an object variable that isn't yet referencing a valid object or when 2) you attempt to use an object variable that has been set to Nothing. But I can't figure in which part of my code I am making that mistake. Help much appreciated.
UPDATE:
When I start the page number from say 8 and it goes up to say 15 it has no problem pulling that data from the site, so I don't think it has anything to do with the specific page numbers (I could be wrong about this). But when I set the page number to 17 instead of 15 the error message pops up almost immediately. So, could it be the range between pages that's causing this?
UPDATE 2:
It's also kind of inconsistent when the error message pops up, when I run the script again with the same page numbers (8 to 17) it runs through the entire thing with no hiccups. (Weird) Then I change the page numbers from (8 to 18) and it gets through 2 pages and stops.
UPDATE 3:
Is it possible that this is happening because I am being rate limited?
Sub GrabInfo()
Dim objIE As InternetExplorer
Dim r As Integer
Dim p As Integer
Dim c As Integer
r = 0
p = 0
c = 0
Dim sDD1 As String
Dim sDD2 As String
Dim sDD3 As String
Set objIE = New InternetExplorer
'objIE.Visible = True
Do While p < 53
p = p + 1
'Setting row to zero after the new page loads but before the data gets pulled
r = 0
objIE.navigate "URLGOESHERE.com/123456789?pageNum=" & p
Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
Do While r < 25
'Increment the row (gets reset after each page)
r = r + 1
'Increment the cell (never gets reset)
c = c + 1
'Pulling data from site row by row and setting each child to temp sDD strings
sDD1 = objIE.document.getElementsByClassName("table-content")(r - 1).Children(0).innerText
sDD2 = objIE.document.getElementsByClassName("table-content")(r - 1).Children(2).innerText
sDD3 = objIE.document.getElementsByClassName("table-content")(r - 1).Children(3).innerText
Debug.Print sDD1 & " | " & sDD2 & " | " & sDD3 & " - " & _
"Cell # = " & c & " ROW # = " & r & " PAGE # = " & p
'Filling in the temp sDD strings into excel cells
Range("A" & (c + 1)).Value = sDD1
Range("B" & (c + 1)).Value = sDD2
Range("C" & (c + 1)).Value = sDD3
Loop
Loop
End Sub
excel vba excel-vba
asked Nov 15 '18 at 0:36
notamathwiznotamathwiz
155310
|
show 4 more comments
I have created a little script that takes some data from a website table and fills it into cells in Excel. I need this in order to make searching through that data and filtering it easier for myself. However I have ran into a little problem, every time I run my script it does what it's supposed to do for around 9 to 11 pages (about 250 cell entries) and then stops with an error message popping up saying "Runtime Error 91" Object variable or With block variable not set. It never runs all the way through till the end and I only get a small fraction of the data that I was supposed to get.
I am not an expert in programming and even less of an expert in VBA, so if I'm doing something wrong please let me know. I've been stuck on this problem for a few days. I do not know what to do to fix this problem. I've looked for fixes on Google, YouTube and even Microsoft's own website, but can't figure out why this error is popping up in my own script.
I know that this problem is encountered when; 1) you attempt to use an object variable that isn't yet referencing a valid object or when 2) you attempt to use an object variable that has been set to Nothing. But I can't figure in which part of my code I am making that mistake. Help much appreciated.
UPDATE:
When I start the page number from say 8 and it goes up to say 15 it has no problem pulling that data from the site, so I don't think it has anything to do with the specific page numbers (I could be wrong about this). But when I set the page number to 17 instead of 15 the error message pops up almost immediately. So, could it be the range between pages that's causing this?
UPDATE 2:
It's also kind of inconsistent when the error message pops up, when I run the script again with the same page numbers (8 to 17) it runs through the entire thing with no hiccups. (Weird) Then I change the page numbers from (8 to 18) and it gets through 2 pages and stops.
UPDATE 3:
Is it possible that this is happening because I am being rate limited?
Sub GrabInfo()
Dim objIE As InternetExplorer
Dim r As Integer
Dim p As Integer
Dim c As Integer
r = 0
p = 0
c = 0
Dim sDD1 As String
Dim sDD2 As String
Dim sDD3 As String
Set objIE = New InternetExplorer
'objIE.Visible = True
Do While p < 53
p = p + 1
'Setting row to zero after the new page loads but before the data gets pulled
r = 0
objIE.navigate "URLGOESHERE.com/123456789?pageNum=" & p
Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
Do While r < 25
'Increment the row (gets reset after each page)
r = r + 1
'Increment the cell (never gets reset)
c = c + 1
'Pulling data from site row by row and setting each child to temp sDD strings
sDD1 = objIE.document.getElementsByClassName("table-content")(r - 1).Children(0).innerText
sDD2 = objIE.document.getElementsByClassName("table-content")(r - 1).Children(2).innerText
sDD3 = objIE.document.getElementsByClassName("table-content")(r - 1).Children(3).innerText
Debug.Print sDD1 & " | " & sDD2 & " | " & sDD3 & " - " & _
"Cell # = " & c & " ROW # = " & r & " PAGE # = " & p
'Filling in the temp sDD strings into excel cells
Range("A" & (c + 1)).Value = sDD1
Range("B" & (c + 1)).Value = sDD2
Range("C" & (c + 1)).Value = sDD3
Loop
Loop
End Sub
excel vba excel-vba
asked Nov 15 '18 at 0:36
notamathwiznotamathwiz
155310
Ok, thanks. Good to know.
– notamathwiz
Nov 15 '18 at 0:45
Wondering if the class is changing which causes the issue
– urdearboy
Nov 15 '18 at 0:46
Yea, I don't know. As far as I can see, sDD1, sDD2 and sDD3 stay as Strings. And p, r and c stay as Integers. My only suspicion is objIE is somehow changing class, but I don't see where.
– notamathwiz
Nov 15 '18 at 0:49
1
You need to narrow down the issue using the debugger. First, try to isolate it to a specific page using anif p = somevalue then Debug.Print("something"), and set a breakpoint on the print statement. Start with a high value forp, run the code, and see if you hit the breakpoint. If not, reduce the value and run again to see if you get to the break. Repeat as needed until you see if it's a specific page. Once you've figured that out, add the same sort ofifin the inner loop, checking forp = thepage and c = somevalue. That should narrow it down to something you can then step through.
– Ken White
Nov 15 '18 at 1:02
(continued) You have several possibilities: GetElementsByClassName could fail, Children() could as well. Range() wiith an invalid value for c could be an issue. Isolating the problem is the first step to solving it.
– Ken White
Nov 15 '18 at 1:07
|
show 4 more comments
I have created a little script that takes some data from a website table and fills it into cells in Excel. I need this in order to make searching through that data and filtering it easier for myself. However I have ran into a little problem, every time I run my script it does what it's supposed to do for around 9 to 11 pages (about 250 cell entries) and then stops with an error message popping up saying "Runtime Error 91" Object variable or With block variable not set. It never runs all the way through till the end and I only get a small fraction of the data that I was supposed to get.
I am not an expert in programming and even less of an expert in VBA, so if I'm doing something wrong please let me know. I've been stuck on this problem for a few days. I do not know what to do to fix this problem. I've looked for fixes on Google, YouTube and even Microsoft's own website, but can't figure out why this error is popping up in my own script.
I know that this problem is encountered when; 1) you attempt to use an object variable that isn't yet referencing a valid object or when 2) you attempt to use an object variable that has been set to Nothing. But I can't figure in which part of my code I am making that mistake. Help much appreciated.
UPDATE:
When I start the page number from say 8 and it goes up to say 15 it has no problem pulling that data from the site, so I don't think it has anything to do with the specific page numbers (I could be wrong about this). But when I set the page number to 17 instead of 15 the error message pops up almost immediately. So, could it be the range between pages that's causing this?
UPDATE 2:
It's also kind of inconsistent when the error message pops up, when I run the script again with the same page numbers (8 to 17) it runs through the entire thing with no hiccups. (Weird) Then I change the page numbers from (8 to 18) and it gets through 2 pages and stops.
UPDATE 3:
Is it possible that this is happening because I am being rate limited?
Sub GrabInfo()
Dim objIE As InternetExplorer
Dim r As Integer
Dim p As Integer
Dim c As Integer
r = 0
p = 0
c = 0
Dim sDD1 As String
Dim sDD2 As String
Dim sDD3 As String
Set objIE = New InternetExplorer
'objIE.Visible = True
Do While p < 53
p = p + 1
'Setting row to zero after the new page loads but before the data gets pulled
r = 0
objIE.navigate "URLGOESHERE.com/123456789?pageNum=" & p
Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
Do While r < 25
'Increment the row (gets reset after each page)
r = r + 1
'Increment the cell (never gets reset)
c = c + 1
'Pulling data from site row by row and setting each child to temp sDD strings
sDD1 = objIE.document.getElementsByClassName("table-content")(r - 1).Children(0).innerText
sDD2 = objIE.document.getElementsByClassName("table-content")(r - 1).Children(2).innerText
sDD3 = objIE.document.getElementsByClassName("table-content")(r - 1).Children(3).innerText
Debug.Print sDD1 & " | " & sDD2 & " | " & sDD3 & " - " & _
"Cell # = " & c & " ROW # = " & r & " PAGE # = " & p
'Filling in the temp sDD strings into excel cells
Range("A" & (c + 1)).Value = sDD1
Range("B" & (c + 1)).Value = sDD2
Range("C" & (c + 1)).Value = sDD3
Loop
Loop
End Sub
excel vba excel-vba
asked Nov 15 '18 at 0:36
notamathwiznotamathwiz
155310
I have created a little script that takes some data from a website table and fills it into cells in Excel. I need this in order to make searching through that data and filtering it easier for myself. However I have ran into a little problem, every time I run my script it does what it's supposed to do for around 9 to 11 pages (about 250 cell entries) and then stops with an error message popping up saying "Runtime Error 91" Object variable or With block variable not set. It never runs all the way through till the end and I only get a small fraction of the data that I was supposed to get.
I am not an expert in programming and even less of an expert in VBA, so if I'm doing something wrong please let me know. I've been stuck on this problem for a few days. I do not know what to do to fix this problem. I've looked for fixes on Google, YouTube and even Microsoft's own website, but can't figure out why this error is popping up in my own script.
I know that this problem is encountered when; 1) you attempt to use an object variable that isn't yet referencing a valid object or when 2) you attempt to use an object variable that has been set to Nothing. But I can't figure in which part of my code I am making that mistake. Help much appreciated.
UPDATE:
When I start the page number from say 8 and it goes up to say 15 it has no problem pulling that data from the site, so I don't think it has anything to do with the specific page numbers (I could be wrong about this). But when I set the page number to 17 instead of 15 the error message pops up almost immediately. So, could it be the range between pages that's causing this?
UPDATE 2:
It's also kind of inconsistent when the error message pops up, when I run the script again with the same page numbers (8 to 17) it runs through the entire thing with no hiccups. (Weird) Then I change the page numbers from (8 to 18) and it gets through 2 pages and stops.
UPDATE 3:
Is it possible that this is happening because I am being rate limited?
Sub GrabInfo()
Dim objIE As InternetExplorer
Dim r As Integer
Dim p As Integer
Dim c As Integer
r = 0
p = 0
c = 0
Dim sDD1 As String
Dim sDD2 As String
Dim sDD3 As String
Set objIE = New InternetExplorer
'objIE.Visible = True
Do While p < 53
p = p + 1
'Setting row to zero after the new page loads but before the data gets pulled
r = 0
objIE.navigate "URLGOESHERE.com/123456789?pageNum=" & p
Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
Do While r < 25
'Increment the row (gets reset after each page)
r = r + 1
'Increment the cell (never gets reset)
c = c + 1
'Pulling data from site row by row and setting each child to temp sDD strings
sDD1 = objIE.document.getElementsByClassName("table-content")(r - 1).Children(0).innerText
sDD2 = objIE.document.getElementsByClassName("table-content")(r - 1).Children(2).innerText
sDD3 = objIE.document.getElementsByClassName("table-content")(r - 1).Children(3).innerText
Debug.Print sDD1 & " | " & sDD2 & " | " & sDD3 & " - " & _
"Cell # = " & c & " ROW # = " & r & " PAGE # = " & p
'Filling in the temp sDD strings into excel cells
Range("A" & (c + 1)).Value = sDD1
Range("B" & (c + 1)).Value = sDD2
Range("C" & (c + 1)).Value = sDD3
Loop
Loop
End Sub
excel vba excel-vba
excel vba excel-vba
asked Nov 15 '18 at 0:36
notamathwiznotamathwiz
155310
asked Nov 15 '18 at 0:36
notamathwiznotamathwiz
155310
edited Nov 15 '18 at 20:04
notamathwiz
asked Nov 15 '18 at 0:36
notamathwiznotamathwiz
155310
asked Nov 15 '18 at 0:36
notamathwiznotamathwiz
155310
asked Nov 15 '18 at 0:36
notamathwiznotamathwiz
155310
155310
Ok, thanks. Good to know.
– notamathwiz
Nov 15 '18 at 0:45
Wondering if the class is changing which causes the issue
– urdearboy
Nov 15 '18 at 0:46
Yea, I don't know. As far as I can see, sDD1, sDD2 and sDD3 stay as Strings. And p, r and c stay as Integers. My only suspicion is objIE is somehow changing class, but I don't see where.
– notamathwiz
Nov 15 '18 at 0:49
1
You need to narrow down the issue using the debugger. First, try to isolate it to a specific page using anif p = somevalue then Debug.Print("something"), and set a breakpoint on the print statement. Start with a high value forp, run the code, and see if you hit the breakpoint. If not, reduce the value and run again to see if you get to the break. Repeat as needed until you see if it's a specific page. Once you've figured that out, add the same sort ofifin the inner loop, checking forp = thepage and c = somevalue. That should narrow it down to something you can then step through.
– Ken White
Nov 15 '18 at 1:02
(continued) You have several possibilities: GetElementsByClassName could fail, Children() could as well. Range() wiith an invalid value for c could be an issue. Isolating the problem is the first step to solving it.
– Ken White
Nov 15 '18 at 1:07
|
show 4 more comments
Ok, thanks. Good to know.
– notamathwiz
Nov 15 '18 at 0:45
Wondering if the class is changing which causes the issue
– urdearboy
Nov 15 '18 at 0:46
Yea, I don't know. As far as I can see, sDD1, sDD2 and sDD3 stay as Strings. And p, r and c stay as Integers. My only suspicion is objIE is somehow changing class, but I don't see where.
– notamathwiz
Nov 15 '18 at 0:49
1
You need to narrow down the issue using the debugger. First, try to isolate it to a specific page using anif p = somevalue then Debug.Print("something"), and set a breakpoint on the print statement. Start with a high value forp, run the code, and see if you hit the breakpoint. If not, reduce the value and run again to see if you get to the break. Repeat as needed until you see if it's a specific page. Once you've figured that out, add the same sort ofifin the inner loop, checking forp = thepage and c = somevalue. That should narrow it down to something you can then step through.
– Ken White
Nov 15 '18 at 1:02
(continued) You have several possibilities: GetElementsByClassName could fail, Children() could as well. Range() wiith an invalid value for c could be an issue. Isolating the problem is the first step to solving it.
– Ken White
Nov 15 '18 at 1:07
Ok, thanks. Good to know.
– notamathwiz
Nov 15 '18 at 0:45
Ok, thanks. Good to know.
– notamathwiz
Nov 15 '18 at 0:45
Wondering if the class is changing which causes the issue
– urdearboy
Nov 15 '18 at 0:46
Wondering if the class is changing which causes the issue
– urdearboy
Nov 15 '18 at 0:46
Yea, I don't know. As far as I can see, sDD1, sDD2 and sDD3 stay as Strings. And p, r and c stay as Integers. My only suspicion is objIE is somehow changing class, but I don't see where.
– notamathwiz
Nov 15 '18 at 0:49
Yea, I don't know. As far as I can see, sDD1, sDD2 and sDD3 stay as Strings. And p, r and c stay as Integers. My only suspicion is objIE is somehow changing class, but I don't see where.
– notamathwiz
Nov 15 '18 at 0:49
1
1
You need to narrow down the issue using the debugger. First, try to isolate it to a specific page using an
if p = somevalue then Debug.Print("something"), and set a breakpoint on the print statement. Start with a high value for p, run the code, and see if you hit the breakpoint. If not, reduce the value and run again to see if you get to the break. Repeat as needed until you see if it's a specific page. Once you've figured that out, add the same sort of if in the inner loop, checking for p = thepage and c = somevalue. That should narrow it down to something you can then step through.– Ken White
Nov 15 '18 at 1:02
You need to narrow down the issue using the debugger. First, try to isolate it to a specific page using an
if p = somevalue then Debug.Print("something"), and set a breakpoint on the print statement. Start with a high value for p, run the code, and see if you hit the breakpoint. If not, reduce the value and run again to see if you get to the break. Repeat as needed until you see if it's a specific page. Once you've figured that out, add the same sort of if in the inner loop, checking for p = thepage and c = somevalue. That should narrow it down to something you can then step through.– Ken White
Nov 15 '18 at 1:02
(continued) You have several possibilities: GetElementsByClassName could fail, Children() could as well. Range() wiith an invalid value for c could be an issue. Isolating the problem is the first step to solving it.
– Ken White
Nov 15 '18 at 1:07
(continued) You have several possibilities: GetElementsByClassName could fail, Children() could as well. Range() wiith an invalid value for c could be an issue. Isolating the problem is the first step to solving it.
– Ken White
Nov 15 '18 at 1:07
|
show 4 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53310839%2fhow-to-fix-runtime-error-91-in-excel-vba-script%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53310839%2fhow-to-fix-runtime-error-91-in-excel-vba-script%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Ok, thanks. Good to know.
– notamathwiz
Nov 15 '18 at 0:45
Wondering if the class is changing which causes the issue
– urdearboy
Nov 15 '18 at 0:46
Yea, I don't know. As far as I can see, sDD1, sDD2 and sDD3 stay as Strings. And p, r and c stay as Integers. My only suspicion is objIE is somehow changing class, but I don't see where.
– notamathwiz
Nov 15 '18 at 0:49
1
You need to narrow down the issue using the debugger. First, try to isolate it to a specific page using an
if p = somevalue then Debug.Print("something"), and set a breakpoint on the print statement. Start with a high value forp, run the code, and see if you hit the breakpoint. If not, reduce the value and run again to see if you get to the break. Repeat as needed until you see if it's a specific page. Once you've figured that out, add the same sort ofifin the inner loop, checking forp = thepage and c = somevalue. That should narrow it down to something you can then step through.– Ken White
Nov 15 '18 at 1:02
(continued) You have several possibilities: GetElementsByClassName could fail, Children() could as well. Range() wiith an invalid value for c could be an issue. Isolating the problem is the first step to solving it.
– Ken White
Nov 15 '18 at 1:07