Excel to fetch data from web url on loop

[sathish_fun]

Hi All

i am trying to get data from a url and the URL belongs to my company the below code is working and i am able to retrieve data and print in excel but some times i am getting Run Time error 1004 and on debug it is stopping on (.Refresh BackgroundQuery:=False)

can some one help me to solve my problem

Error details

1004 -- The file could not be accessed. Try one of the following:

• Make sure the specified folder exists.
• Make sure the folder that contains the file is not read-only.
• Make sure the file name does not contain any of the following characters: < > ? [ ] :  or *
• Make sure the file/path name doesn't contain more than 218 characters.

Code i am using

a = Range("Z1").Value
With ActiveSheet.QueryTables.Add(Connection:="URL;http://10.101.8.127:8000/webcmd/devicecap?msisdn=91" & a & "&hardware_gprs&msisdn&brand&model", Destination:=Range("$A$1"))
.Name = "q?s=goog_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1,2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

 

[sathish_fun]

Posting my full code tell me how and where to add time delay on loop

Sub Basic_Web_Query()
Dim Raga As Long, G As Long, K As Long
Sheets("Base").Select
Raga = Range("A" & Rows.Count).End(xlUp).row
For F = 2 To Raga
If Range("A" & F).Text <> "" Then
Range("A" & F).Select
Selection.Copy
Sheets("Work").Select
Range("Z1").Select
ActiveSheet.Paste
Range("A1").Select
a = Range("Z1").Value
With ActiveSheet.QueryTables.Add(Connection:="URL;http://10.101.8.127:8000/webcmd/devicecap?msisdn=91" & a & "&hardware_gprs&msisdn&brand&model", Destination:=Range("$A$1"))
.Name = "q?s=goog_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "1,2"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Range("Z1").Delete
If Range("A1").Text = "0 Ok" Then
Range("A1:A5").Select
Selection.Copy
Sheets("Output").Select
Q = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).row
Range("A1").Select
Range("A" & Q).Select
ActiveSheet.Paste
Sheets("Work").Select
Selection.EntireRow.Delete
Selection.Delete Shift:=xlUp
Else
End If
Sheets("Base").Select
Else
End If
Next F
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End With

End Sub