Excel to fetch data from web url on loop

sathish_fun

Board Regular
Joined
Feb 15, 2008
Messages
247
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

HTML:
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

HTML:
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Posting my full code tell me how and where to add time delay on loop

HTML:
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
 
Upvote 0

Forum statistics

Threads
1,211,697
Messages
6,103,360
Members
447,861
Latest member
LllopezXC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top