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 Object
Dim t As Integer, r As Integer, c As Integer
Dim elemCollection As Object
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet

Set IE = CreateObject("InternetExplorer.Application")

Set xlApp = CreateObject("excel.application")

Set wb = ActiveWorkbook

With IE
  .Visible = True
  .navigate ("https://app.powerbi.com/view?r=eyJrIjoiODM0MmVhOGEtZWM1My00NTlkLWI3OWUtYTdkNDVkMmM5YWRiIiwidCI6IjE0MTk0ZTdlLTgwYjctNGM4Zi1hMTgwLTNmZTc4YWE1OGExZiIsImMiOjZ9")
   
While IE.ReadyState <> 4
DoEvents
Wend

Do While IE.busy: DoEvents: Loop

With ws
    Sheets("Stuff").Range("A1:AK500").ClearContents
End With

Set 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 r
Next t

End With

Set IE = Nothing

End Sub

Open in new window

What am I doing wrong?

Thanks,
gdunn59
LVL 1
gdunn59Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
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?
0
gdunn59Author Commented:
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
0
Martin LissOlder than dirtCommented:
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?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

NorieAnalyst Assistant Commented:
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.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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 Object
Dim t As Integer, r As Integer, c As Integer
Dim elemCollection As Object
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet

Set IE = CreateObject("InternetExplorer.Application")

Set xlApp = CreateObject("excel.application")

Set wb = ActiveWorkbook

With IE
  .Visible = True
  .navigate ("https://app.powerbi.com/view?r=eyJrIjoiODM0MmVhOGEtZWM1My00NTlkLWI3OWUtYTdkNDVkMmM5YWRiIiwidCI6IjE0MTk0ZTdlLTgwYjctNGM4Zi1hMTgwLTNmZTc4YWE1OGExZiIsImMiOjZ9")


On Error Resume Next
Do While IE.Busy Or elemCollection.Length = 0
    DoEvents
    Set elemCollection = IE.Document.getElementsByTagName("TABLE")
Loop
With ws
    Sheets("Stuff").Range("A1:AK500").ClearContents
End With


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 r
Next t

End With
IE.Quit
Set IE = Nothing
MsgBox "Done"
End Sub

Open in new window


Hope that helps.
0
gdunn59Author Commented:
Subodh Tiwari (Neeraj),

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?

Thanks,
gdunn59
C--Users-msc551-Desktop-COGCCScreenS.jpg
C--Users-msc551-Desktop-InformationC.jpg
C--Users-msc551-Desktop-InformationC.jpg
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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. :)
0
gdunn59Author Commented:
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"?

Thanks,
gdunn59
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
To populate the data from column A, please try this...
Sub extractTablesData()
Dim IE As Object, obj As Object
Dim t As Integer, r As Integer, c As Integer
Dim elemCollection As Object
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet

Set IE = CreateObject("InternetExplorer.Application")

Set xlApp = CreateObject("excel.application")

Set wb = ActiveWorkbook

With IE
  .Visible = True
  .navigate ("https://app.powerbi.com/view?r=eyJrIjoiODM0MmVhOGEtZWM1My00NTlkLWI3OWUtYTdkNDVkMmM5YWRiIiwidCI6IjE0MTk0ZTdlLTgwYjctNGM4Zi1hMTgwLTNmZTc4YWE1OGExZiIsImMiOjZ9")


    On Error Resume Next
    Do While IE.Busy Or elemCollection.Length = 0
        DoEvents
        Set elemCollection = IE.Document.getElementsByTagName("TABLE")
    Loop
    With ws
        Sheets("Stuff").Range("A1:AK500").ClearContents
    End With
    
    
    For t = 0 To (elemCollection.Length - 1)
      For r = 0 To (elemCollection(t).Rows.Length - 1)
         For c = 1 To (elemCollection(t).Rows(r).Cells.Length - 1)
    
      With ws
        Sheets("Stuff").Cells(r + 1, c) = elemCollection(t).Rows(r).Cells(c).innertext
        DoEvents
      End With
      
       Next c
      Next r
    Next t
End With
IE.Quit
Set IE = Nothing
MsgBox "Done"
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gdunn59Author Commented:
Subodh Tiwari (Neeraj),

That worked.

Now I just need to try and figure out why it's changing at the end what it posts.

Thanks much!
0
NorieAnalyst Assistant Commented:
Just curious, why is an instance of Excel created here and then never used?
Set xlApp = CreateObject("excel.application")

Open in new window

Also, that instance is never closed down
1
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.