Query's keep updating when they dont need too!

Hi Guys,

I have the following code, After running the macro, the query's want to keep updating. I want the query's to get the data once, and then stop updating afterwards (become static text). Does anyone have a method of making the query's static text?
I think the auto updating of querys is ok when there are only a few, however I have a list of about 500, and which results in going very slow and eventually an error message coming up asking if I would like the query's to stop running, when I accept half of the querys become an error strings.

(Column B has a list of website addresses.)

Set Rng01 = Cells(3, 1)
Set Rng02 = Cells(100, 1)
'Call Functions_Module.BlanksToSkip(Rng01, "Down", Rng02, 1)

For i01 = 1 To Rng02.Row - Rng01.Row - 1
'URL = "http://www.wiseowl.co.uk/courses/"
'URL = "https://www.linkedin.com/salary/Project-Engineer-salaries-in-australia"
'Set ws = Worksheets.Add
'Set qt = ws.QueryTables.Add(Connection:="URL;" & URL, Destination:=Range("A1"))

URL = Cells(2 + i01, 2)
Set Rng01 = Cells(2 + i01, 4)
Set qt = ActiveSheet.QueryTables.Add(Connection:="URL;" & URL, Destination:=Rng01)

With qt
'    .RefreshOnFileOpen = True
'    .RefreshPeriod = 5 ' (5 = 5 minutes, 0 will not refresh automatically)
'    .Name = "OWLCourses"
'    .WebFormatting = xlWebFormattingRTF
    .WebSelectionType = xlEntirePage
'    .WebTables = "3"
'    .WebTables = "1,2"
    .Refresh
End With
Application.Wait (Now + TimeValue("0:00:5"))

Next i01

End Sub

I have attached the workbook with vba of what I am trying to do.
I it seems to go very very slowly, and becomes very laggy; anyone know how to speed this up?

*Bumping Thread to get responses*

*Bumping Thread to get responses*

*Bumping*
I really don't know what I can do to improve my code. I need a way to change the query's into static text (I believe that it's probably the query refreshing each time the macro is looped through that is causing it to slow down around 50 querys.

I have not much experience in this matter, but can you use this option?

.BackgroundQuery = False

B/R
Erwin

For me this worked, but I have to admit, I deleted the rows below 150, because it takes about 45 seconds to complete to row 150, just for getting the data from internet, for only 10 rows.....
With the first macro I turned background updating of the other cells off, I did this before executing the main procedure.
Of course you have to update once in a while.....

Sub Stop_Updating()
Dim i
   For i = 3 To 150
    If Cells(i, 4) <> "" Then
        Cells(i, 4).QueryTable.BackgroundQuery = False
    End If
   Next i
End Sub

Sub importwiseowlcourses()
Dim ws As Worksheet
Dim qt As QueryTable
Dim URL As String
Dim Rng01 As Range
Dim Rng02 As Range
Dim i01 As Long

Set Rng01 = Cells(3, 1)
Call Functions_Module.BlanksToSkip(Rng01, "Down", Rng02, 1)

For i01 = 140 To Rng02.Row - Rng01.Row - 1
'URL = "http://www.wiseowl.co.uk/courses/"
'URL = "https://www.linkedin.com/salary/Project-Engineer-salaries-in-australia"
'Set ws = Worksheets.Add
'Set qt = ws.QueryTables.Add(Connection:="URL;" & URL, Destination:=Range("A1"))

URL = Cells(2 + i01, 2)
Set Rng01 = Cells(2 + i01, 4)
Set qt = ActiveSheet.QueryTables.Add(Connection:="URL;" & URL, Destination:=Rng01)

Application.ScreenUpdating = False
With qt
'    .RefreshOnFileOpen = True
'    .RefreshPeriod = 5 ' (5 = 5 minutes, 0 will not refresh automatically)
'    .Name = "OWLCourses"
'    .WebFormatting = xlWebFormattingRTF
    .WebSelectionType = xlEntirePage
'    .WebTables = "3"
'    .WebTables = "1,2"
        .BackgroundQuery = False
        .Refresh
    End With
 '   Application.Wait (Now + TimeValue("0:00:05"))
 '   Cells(2 + i01, 4).Copy                                  'I deselected those rows)
 '   Cells(2 + i01, 4).PasteSpecial Paste:=xlPasteValues
Next i01
Application.ScreenUpdating = True

MsgBox ("ready")
End Sub

B/R
Erwin

Hi Eastw00d,

I've ran the "Sub Stop_Updating()" followed by your version of "Sub importwiseowlcourses()", however I am still getting the same problem.

The "Sub Stop_Updating()"
Doesn't do anything. (it's not called/used by the "Sub importwiseowlcourses()" and running "Sub Stop_Updating()" before running "Sub importwiseowlcourses()" doesnt add any extra value either.
Not being rude, just factual. Thanks for trying.

When I use your editted "Sub importwiseowlcourses()"
but I am still running into the same problem; that it takes forever to go through the list. I'm still looking for a way/method to speed this process up and believe that its due to querys being updated when they don't need to be.

I am able to get the code to work for about 50 querys and then it slows down, I need to exit sub and then start again.
Does anyone have a method where I could make this code run for 100's or 1000's of querys?
Thanks