Refresh object problem
 

Dear master

I am created a function to download the data from serveral website, th
function is running well if i am using the debug mode to run it line b
line, but once i using the marco to the full set, the program seem kee
loop within the refresh :confused: condition, seem refresh = 1 can no
return to me, i may guess that because i am keep looping until th
refresh = 1 return to system, can you help me to solve it, many thanks
:)

Sub GetWebData(strSheet, strURL, strWebTables)

Dim iRefresh As Integer
Dim cRows As Long
Dim i As Long

iRefresh = 0

cRows = Worksheets(strSheet).Cells(Rows.Count, "A").End(xlUp).Row

Set shFirstQtr = Worksheets(strSheet)

Set qtQtrResults = shFirstQtr.QueryTables _
.Add(Connection:="URL;" & strURL & "", _
Destination:=shFirstQtr.Cells(cRows + 1, 1))

With qtQtrResults
.WebFormatting = xlNone
.WebSelectionType = xlSpecifiedTables
.RefreshStyle = xlInsertDeleteCells
.WebTables = strWebTables
.Refresh

Do Until iRefresh = 1
If .Refreshing Then
iRefresh = 0
Else
iRefresh = 1
End If
Loop
End Wit

--
Louis LA
-----------------------------------------------------------------------
Louis LAI's Profile: http://www.excelforum.com/member.php...fo&userid=1562
View this thread: http://www.excelforum.com/showthread.php?threadid=27174

Refresh object problem
 

Try using ' DoEvents ' immediately after ' Do Until iRefresh = 1'

Sharad

"Louis LAI" wrote in message
...

Dear master

I am created a function to download the data from serveral website, the
function is running well if i am using the debug mode to run it line by
line, but once i using the marco to the full set, the program seem keep
loop within the refresh :confused: condition, seem refresh = 1 can not
return to me, i may guess that because i am keep looping until the
refresh = 1 return to system, can you help me to solve it, many thanks.
:)

Sub GetWebData(strSheet, strURL, strWebTables)

Dim iRefresh As Integer
Dim cRows As Long
Dim i As Long

iRefresh = 0

cRows = Worksheets(strSheet).Cells(Rows.Count, "A").End(xlUp).Row

Set shFirstQtr = Worksheets(strSheet)

Set qtQtrResults = shFirstQtr.QueryTables _
Add(Connection:="URL;" & strURL & "", _
Destination:=shFirstQtr.Cells(cRows + 1, 1))

With qtQtrResults
WebFormatting = xlNone
WebSelectionType = xlSpecifiedTables
RefreshStyle = xlInsertDeleteCells
WebTables = strWebTables
Refresh

Do Until iRefresh = 1
If .Refreshing Then
iRefresh = 0
Else
iRefresh = 1
End If
Loop
End With


--
Louis LAI
------------------------------------------------------------------------
Louis LAI's Profile:
http://www.excelforum.com/member.php...o&userid=15629
View this thread: http://www.excelforum.com/showthread...hreadid=271745

Refresh object problem
 

Take out your loop all together and set the BackGroundQuery property to
false. Then your code waits for the refresh. I am sure it will be more
reliable than your loop.

change
.Refresh

to

.Refresh BackGroundQuery:=False


--
Regards,
Tom Ogilvy

"Louis LAI" wrote in message
...

Dear master

I am created a function to download the data from serveral website, the
function is running well if i am using the debug mode to run it line by
line, but once i using the marco to the full set, the program seem keep
loop within the refresh :confused: condition, seem refresh = 1 can not
return to me, i may guess that because i am keep looping until the
refresh = 1 return to system, can you help me to solve it, many thanks.
:)

Sub GetWebData(strSheet, strURL, strWebTables)

Dim iRefresh As Integer
Dim cRows As Long
Dim i As Long

iRefresh = 0

cRows = Worksheets(strSheet).Cells(Rows.Count, "A").End(xlUp).Row

Set shFirstQtr = Worksheets(strSheet)

Set qtQtrResults = shFirstQtr.QueryTables _
Add(Connection:="URL;" & strURL & "", _
Destination:=shFirstQtr.Cells(cRows + 1, 1))

With qtQtrResults
WebFormatting = xlNone
WebSelectionType = xlSpecifiedTables
RefreshStyle = xlInsertDeleteCells
WebTables = strWebTables
Refresh

Do Until iRefresh = 1
If .Refreshing Then
iRefresh = 0
Else
iRefresh = 1
End If
Loop
End With


--
Louis LAI
------------------------------------------------------------------------
Louis LAI's Profile:
http://www.excelforum.com/member.php...o&userid=15629
View this thread: http://www.excelforum.com/showthread...hreadid=271745