Why Doesn't My Code Execute Unless I have a BreakPoint and Step Through the Code
I have the following VBA Code in my Excel Spreadsheet, and when I click F5 to run it, it opens the Website but does not populate the spreadsheet unless I have a breakpoint in the Code and F8 through it.
Also, when it does populate the spreadsheet (because I F8 through it), it starts populating in Cell "B1". How can I have it start populating in Cell "A1"?
Code:
Sub extractTablesData()Dim IE As Object, obj As ObjectDim t As Integer, r As Integer, c As IntegerDim elemCollection As ObjectDim xlApp As Excel.ApplicationDim wb As WorkbookDim ws As WorksheetSet IE = CreateObject("InternetExplorer.Application")Set xlApp = CreateObject("excel.application")Set wb = ActiveWorkbookWith IE .Visible = True .navigate ("https://app.powerbi.com/view?r=eyJrIjoiODM0MmVhOGEtZWM1My00NTlkLWI3OWUtYTdkNDVkMmM5YWRiIiwidCI6IjE0MTk0ZTdlLTgwYjctNGM4Zi1hMTgwLTNmZTc4YWE1OGExZiIsImMiOjZ9")While IE.ReadyState <> 4DoEventsWendDo While IE.busy: DoEvents: LoopWith ws Sheets("Stuff").Range("A1:AK500").ClearContentsEnd WithSet elemCollection = IE.Document.getElementsByTagName("TABLE")For t = 0 To (elemCollection.Length - 1) For r = 0 To (elemCollection(t).Rows.Length - 1) For c = 0 To (elemCollection(t).Rows(r).Cells.Length - 1) With ws Sheets("Stuff").Cells(r + 1, c + 1) = elemCollection(t).Rows(r).Cells(c).innertext DoEvents End With Next c Next rNext tEnd WithSet IE = NothingEnd Sub
First question: Put a DoEvents where you had the breakpoint.
Second question: Put a breakpoint on line 36. What is the value of "c" when it gets there the first time?
gdunn59
ASKER
Martin Liss,
I put the "DoEvents" on Line 24. Still only works if I F8 through the code.
I also put a BreakPoint on Line 36 of the Code, but it never gets there. It opens the Webpage and that's it.
So I added a BreakPoint on Line 31 and F8 through the code and when it gets to Line 34 (blank line) I ?c in the Immediate Window, and it shows "0".
Thanks,
gdunn59
Martin Liss
You didn't define elemCollection, but from it's name it looks like a Collection so it should have a Count property. Put a Debug.Print elemCollection.count statement following line 29. After it runs look in the Immediate window.Does elemCollection have any records?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Norie
I think this might be some sort of timing issue, as far as I can see the page kind of loads in two stages.
Try adding some Application.Wait statements to pause the code will the page is loading
PS The first column of the table on the page appears to be blank, that's why data starts appearing in column B on the worksheet.
Subodh Tiwari (Neeraj)
The problem arises when the web page loads internally. So in that case IE.Busy and IE.Readystate don't help.
Rather Try this where you check in the Do loop whether the element within the web page is set properly and check it's one attribute. In this case you check the length of the element collection and if it is not 0, the code will exit the Do loop and proceed further.
Sub extractTablesData()Dim IE As Object, obj As ObjectDim t As Integer, r As Integer, c As IntegerDim elemCollection As ObjectDim xlApp As Excel.ApplicationDim wb As WorkbookDim ws As WorksheetSet IE = CreateObject("InternetExplorer.Application")Set xlApp = CreateObject("excel.application")Set wb = ActiveWorkbookWith IE .Visible = True .navigate ("https://app.powerbi.com/view?r=eyJrIjoiODM0MmVhOGEtZWM1My00NTlkLWI3OWUtYTdkNDVkMmM5YWRiIiwidCI6IjE0MTk0ZTdlLTgwYjctNGM4Zi1hMTgwLTNmZTc4YWE1OGExZiIsImMiOjZ9")On Error Resume NextDo While IE.Busy Or elemCollection.Length = 0 DoEvents Set elemCollection = IE.Document.getElementsByTagName("TABLE")LoopWith ws Sheets("Stuff").Range("A1:AK500").ClearContentsEnd WithFor t = 0 To (elemCollection.Length - 1) For r = 0 To (elemCollection(t).Rows.Length - 1) For c = 0 To (elemCollection(t).Rows(r).Cells.Length - 1) With ws Sheets("Stuff").Cells(r + 1, c + 1) = elemCollection(t).Rows(r).Cells(c).innertext DoEvents End With Next c Next rNext tEnd WithIE.QuitSet IE = NothingMsgBox "Done"End Sub
That seemed to work, but something strange going on. While I'm stepping through the code, It is putting certain information in the spreadsheet that matches the right-side portion of the screen on the website, but when the process is complete, it puts something different.
I've attached screen shots for your reference. The first one shows the Website and the information. The second screen shot shows the information that is being copied from the right side of the website, and the 3rd screen shot shows the information that is being copied from the left side of the website.
Why would the final results show something different?
Unlimited question asking, solutions, articles and more.
Subodh Tiwari (Neeraj)
I addressed your issue which you talked in your original question only and obviously I didn't debug the code to know the algorithm used in there.
So you will have to figure it out yourself. :)
gdunn59
ASKER
I didn't address it originally, because I was focused on the fact that the code wasn't populating the spreadsheet at first.
I also addressed in my original question that it is not starting the populating of data in Cell "A1", but in "B1". How can I get it to populate in Cell "A1"?
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
You are right. Didn't notice that. Actually didn't pay much attention to that section. That is not needed at all in this case. Good catch.
But the thing is I cannot edit that post.
Second question: Put a breakpoint on line 36. What is the value of "c" when it gets there the first time?