Run-time error '1004' - The file could not be accessed.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I try to make a web request with following code:

With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://www.kicker.de/fussball/bundesliga/vereine/spielersteckbrief/object/" + idnr + "/saison/2007-08" _
, Destination:=Range("A1"))
.Name = "2007-08"
.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 = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

The strange thing is that this code works poperly for around 30 queries.
Then it throws an exception (Run-time error '1004'). When I restart my makro
from the very beginning it throws this exception right at the first query,
althought it worked before. This also appears when I close Excel and start it
again.

Sometimes I can step over .Refresh BackgroundQuery:=False in Debug-Mode and
I get the web request.

The URL is defenetly there and accessible.

Does anyone have a solution for this? For any help I would be very thankful.

Thilo
 
Goto the web site>right click in the table>import to ms excel>import>name
sheet Data>make another sheet called List and put in your list in column A.
Assign this to a macro. Develop a macro or indirect formula to extact the
desired data to somewhere. If you ask OFF list ONLY, I will send a workbook.

Sub getdata() 'modified
For Each c In Sheets("List").Range("a2:a" & Cells(Rows.Count,
1).End(xlUp).Row)
'For Each c In Sheets("List").Range("a2:a5") 'for testing
With Sheets("Data").QueryTables(1)
.Connection = _
"URL;http://www.kicker.de/fussball/bundesliga/vereine/spielersteckbrief/object/"
& c & "/saison/2007-08"
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next c
End Sub
 
Unfortunately it still does not work.

When I navigate on this website
http://www.kicker.de/fussball/bundesliga/vereine/spielersteckbrief/object/28398/saison/2007-08

right mouse click/Export to Microsoft Excel

Excel opens the "New Web Query" browser. After I click on Import Excel tries
to open the website without success. After a while (1 minute or so) I get
the "Imort Data" alert where I click ok. It then writes in A1 "Getting
Data..." but also shows the error 1004. And later an error message which says
"Invalid web query".

What I absolutley can not understand is that somtimes it works and sometimes
it doesn't.

Once the error has occurred it will never work again.

When I run my script and the error occurres I can debug in and step over the
break point. It then works (But not all the time). Maybe the pace Excel tries
to import different websites is to fast? After a little pause it can.
 
I had this same problem. It had never happened on any other websites for me, so I tried this. It delays each query by a variable amount. Each time I ran my script it would be able to perform exactly 50 queries before stopping with the exact same error you posted. I think the server is blocking you to avoid automated info requests and prevent abuse.

Code: 
cheat = 0
For ...
  --Query Here
  Application.Wait (Now + TimeValue("0:00:0" & CStr(3 + cheat Mod 3)))
  cheat = cheat + 1
Next
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Web query - unable to extract data 3
How do you importing unicode from web 2
help with prompt to pull wed address 1
VBA: Delete name range 3
Recorded Excel Macro not working 1
How not to display any pop up message for macro? 4
QueryDef; Error 1004 1
Retrieving consecutive pages in web query What's wrong here? 1

Back
Top