troubleshooting Question

Why Doesn't My Code Execute Unless I have a BreakPoint and Step Through the Code

Avatar of gdunn59
gdunn59 asked on
VBAMicrosoft ExcelMicrosoft Office
12 Comments1 Solution126 ViewsLast Modified:
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
What am I doing wrong?

Thanks,
gdunn59
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros