VB Macros freezes/Times out 2

[Jonnylaw91]

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 = "

http://www.americanbulls.com/SignalPage.aspx?lang=en&Ticker="

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

 

[Andrzejek]

First I would suggest to align your code (a lot easier to read):

Option Explicit

Public Sub WaitBrowserQuiet(objIE As InternetExplorer)

Do While objIE.Busy Or objIE.ReadyState <> READYSTATE_COMPLETE
    DoEvents
Loop

End Sub

Sub Update_Click90()[green]
'Variables[/green]
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 = "[URL unfurl="true"]http://www.americanbulls.com/SignalPage.aspx?lang=...="[/URL]

Set ie = New InternetExplorer
ie.Visible = True
[green]
'Delcare current as a worksheet variable[/green]
Dim Current As Worksheet
[green]
'Loop through worksheets[/green]
For Each Current In Worksheets

    If Current.Name <> "Sheet1" Then
        ie.Navigate url & Current.Name
        WaitBrowserQuiet ie
        
        Set Document = ie.Document
[green]        
        'only get one of the three tables[/green]
        Set dxgvTable = Document.getElementsByClassName("dxgvTable").Item(0)
        [green]
        'Get the tables cells from that table[/green]
        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[green]
                'If it's divisable by 5 then start a new row[/green]
                ColumnCount = 1
                RowCount = RowCount + 1
            Else[green]
                'Stay on the same row buy go to next column[/green]
                ColumnCount = ColumnCount + 1
            End If
        Next Element
    End If
Next Current
[green]
'Clean up ho[/green]
Application.WindowState = xlNormal
Set ie = Nothing
MsgBox "Finished Sloot!"

End Sub

Second, keep all your Dim's at the top of the rutine.

Just a guess here, but can you move your [tt]Set[/tt] statements outside your loops to the top of your procedure? To me looks like you are setting and re-setting to the same stuff, but I could be wrong

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[kjv1611]

Right at the top, this portion is just asking for freezing/extended wait times:

Do While objIE.Busy Or objIE.ReadyState <> READYSTATE_COMPLETE
    DoEvents
Loop

The DoEvents command on its own can sometimes cause slow-ups, and I'd imagine that to be amplified greatly in a loop of that sort. If you really need to wait, you might could try seeing what is the max # of seconds you typically have to want, and then build or copy/paste from the Internet a function like fnWait to handle how long you'd need to wait. At least that's an idea. I've used that before successfully.

Another hit (though it shouldn't be near as big) would be in this line:

If Current.Name <> "Sheet1" Then

Instead of checking for <> (not equal to), try checking for equal to, then set your action codes in the Else piece. So

If Current.Name = "Sheet1" Then
Else
  'Do Stuff
End If

I've not had a need thus far to query from a website table into Excel, but I would think there would be a better way than looping through the individual fields in VBA. So this section:

        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

If at all possible, I'd look for ways to build a QueryTable, perhaps, that points to your web source, and queries it as an external data source. Since you can already get a table object and fields, you should be able to query it, I would imagine. That should also greatly speed up the process.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

The DoEvents loop IS the proper way to wait for an ASYNCHRONOUS event to complete.

It IS a good idea to put the IE objects and othe IE code before the For...Next loop, since it seems that you're just chopping up the IE data into data in sheets.

 

[combo]

Your code seems to be ok, i.e it opens websites and copies some data to worksheets in a loop. For me the main factor that slows down the code is linked to the time of refreshing pages. You can easily test it, you made the IE visible, so you should see what is going on. For testin you can modify the code:

Public Sub WaitBrowserQuiet(objIE As InternetExplorer)
Msgbox "started " & objIE.LocationURL
Do While objIE.Busy Or objIE.ReadyState <> READYSTATE_COMPLETE
DoEvents
Msgbox "finished " & objIE.LocationURL
Loop

If you have access to IE developer's tools you can analyse the web page structure to see what you try to pick.
What do yum mean that the macro "stopped out"? Endless loop in testing Busy state? Unexpected break? In the latter case try to change VBE option "Notify Before State Loss" checkbox to checked.

combo

 

[Jonnylaw91]

kjv1611 - I changed the block of code like you recommended, noticed a definite increase of speed. Thanks for the suggestion.

​

If Current.Name = "Sheet1" Then

​

Else

​

'Do Stuff

​

End If

However, I am not too certain what you mean by creating a query table.


Combo - When running the macro, sometimes it will run all the way through all of the excel workbook sheets(2200 sheets roughly) as designed to do. However, lately more often than not, it will randomly stop on a sheet and "time out" i guess? I have to literally close the IE window, which pops up an error code "Run-time error '-2147467259 (8000040005)': Automation error, Unspecified error" Then I click "End" then restart the macro again in hopes it goes all the way through. I have noticed that when it isn't working that the IE page isn't showing the "loading" icon up in the url bar.

 

[SkipVought]

You can do a web query via Data > Get external data > From web... And drill down to your site/table. This places a QueryTable on your sheet that can be Refreshed on command to return the most current resultset. Then ALL your analysis can be in Excel!

 

[Jonnylaw91]

SkipVough - I have also tried this route, and it would not work. I had it populated with about 15 sheets. It would work that day, but the following days it would not, If i remember correctly, it said "could not find the webpage data" or something along those lines.

 

[combo]

When you close browser you loose connection with it, so the error.
When looping through urls and the browser stops, instead of closing IE hit CTRL+BREAK keys and choose "debug". In VBE open the Locals and Immediate windows. In Locals check objIE or IE properties (as url, state etc.) and other local variables. After testing try manually navigate to the page. As in my previous post, in case of problems try to analyse the problm in IE in development mode. The loop in WaitBrowserQuiet normally will continue until full refresh, this may be the problem.
Finally you can add time control to the loop, after certain number of seconds quit the loop, notify failure and go to next page.

combo

 

[Jonnylaw91]

Alright, I see what you are saying. I did the break as you recommended, but I wasn't sure what I was looking for. But I think at this point you are right as far as the time control in the loop. Unfortunately I'm not familiar with that function. How would you suggest I write the block, and where would I insert it. Would I nest it inside the Do while objIE.Busy loop?

 

[combo]

In break mode you still have objects/values in variables, they are visible in Locals windows and accessible in Immediate window. In immediate window you can write ? objIE.Readystate + ENTER and will get this property.

An example of controlling looping (just time testing):

t = Now()
Do While objIE.Busy Or objIE.ReadyState <> READYSTATE_COMPLETE
    If Now > t + TimeSerial(0, 0, 5) Then ' 5 seconds
        If MsgBox("terminate loop?", vbYesNo + vbDefaultButton2 + vbQuestion, "time control") = vbYes Then
            Exit Do
        Else
            t = Now()
        End If
    End If
Loop

You can use Stop to pause the macro execution by code, check variables and continue.

Concerning IE developer tools, depending on IE version:

https://msdn.microsoft.com/library/ie/bg182326(v=vs.85).

You can get detailed info on current page.

combo

 

[Jonnylaw91]

Hey guys, I have to admit that the code isn't working in its entirety. The program still does not run through all of the way on the first attempt. I cannot figure out for the life of me why not. It would be greatly appreciated if you could help a brother out.
In light of this issue, I have started to piece together a program in MQL, most of which was copy and pasted from what I found on the net but it only allows me to use it with the program MetaTrader 5. I guess what I'm getting at, where my mind is, is there anyway to create an independent program that will analyze the candlestick trends so I don't have to scrape the data from the internet? If so, I really wouldn't know how to approach such a problem. Best/Easiest language to write it in? Is there already a language/reference/program out there that has candlestick handles/references? Any data you guys have on this would be greatly appreciated.