Jonnylaw91
Technical User
Hey guys,
This is my first post on here, so be gentle with me. I have obviously created a vb macro that goes out to a website adds the sheet name to the end of the URL, pulls the data from the chart on the website into the respective worksheet for further analysis.
Recently, I have been running into an issue where the macro will not always run through all of the way and I say "stop out" within the first couple of loops. Also, if there is a way that it can be sped up, I will not be opposed to it. Please and thank you. Below is the code in which I wrote with a lot of help. I do understand the coding, just not able to produce it on my own.
Option Explicit
Public Sub WaitBrowserQuiet(objIE As InternetExplorer)
Do While objIE.Busy Or objIE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop
End Sub
Sub Update_Click90()
'Variables
Dim ie As InternetExplorer
Dim Document As htmlDocument
Dim Elements As IHTMLElementCollection
Dim Element As IHTMLElement
Dim url As String
Dim ColumnCount As Long
Dim RowCount As Long
Dim dxgvTable As IHTMLElement
url = "
Set ie = New InternetExplorer
ie.Visible = True
'Delcare current as a worksheet variable
Dim Current As Worksheet
'Loop through worksheets
For Each Current In Worksheets
If Current.Name <> "Sheet1" Then
ie.Navigate url & Current.Name
WaitBrowserQuiet ie
Set Document = ie.Document
'only get one of the three tables
Set dxgvTable = Document.getElementsByClassName("dxgvTable").Item(0)
'Get the tables cells from that table
Set Elements = dxgvTable.getElementsByClassName("dxgv")
ColumnCount = 1
RowCount = 5
For Each Element In Elements
Current.Cells(RowCount, ColumnCount).Value = Trim(Element.InnerText)
If (ColumnCount Mod 5) = 0 Then
'If it's divisable by 5 then start a new row
ColumnCount = 1
RowCount = RowCount + 1
Else
'Stay on the same row buy go to next column
ColumnCount = ColumnCount + 1
End If
Next Element
End If
Next Current
'Clean up ho
Application.WindowState = xlNormal
Set ie = Nothing
MsgBox "Finished Sloot!"
End Sub
This is my first post on here, so be gentle with me. I have obviously created a vb macro that goes out to a website adds the sheet name to the end of the URL, pulls the data from the chart on the website into the respective worksheet for further analysis.
Recently, I have been running into an issue where the macro will not always run through all of the way and I say "stop out" within the first couple of loops. Also, if there is a way that it can be sped up, I will not be opposed to it. Please and thank you. Below is the code in which I wrote with a lot of help. I do understand the coding, just not able to produce it on my own.
Option Explicit
Public Sub WaitBrowserQuiet(objIE As InternetExplorer)
Do While objIE.Busy Or objIE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Loop
End Sub
Sub Update_Click90()
'Variables
Dim ie As InternetExplorer
Dim Document As htmlDocument
Dim Elements As IHTMLElementCollection
Dim Element As IHTMLElement
Dim url As String
Dim ColumnCount As Long
Dim RowCount As Long
Dim dxgvTable As IHTMLElement
url = "
Set ie = New InternetExplorer
ie.Visible = True
'Delcare current as a worksheet variable
Dim Current As Worksheet
'Loop through worksheets
For Each Current In Worksheets
If Current.Name <> "Sheet1" Then
ie.Navigate url & Current.Name
WaitBrowserQuiet ie
Set Document = ie.Document
'only get one of the three tables
Set dxgvTable = Document.getElementsByClassName("dxgvTable").Item(0)
'Get the tables cells from that table
Set Elements = dxgvTable.getElementsByClassName("dxgv")
ColumnCount = 1
RowCount = 5
For Each Element In Elements
Current.Cells(RowCount, ColumnCount).Value = Trim(Element.InnerText)
If (ColumnCount Mod 5) = 0 Then
'If it's divisable by 5 then start a new row
ColumnCount = 1
RowCount = RowCount + 1
Else
'Stay on the same row buy go to next column
ColumnCount = ColumnCount + 1
End If
Next Element
End If
Next Current
'Clean up ho
Application.WindowState = xlNormal
Set ie = Nothing
MsgBox "Finished Sloot!"
End Sub