VBA Web Query Generating Error 1004 1

[bob135]

I have several web queries that get data from yahoo finance and google finance. It generates the following Error:

Run Time Error 1004

the file could not be accessed. Try one of the following:
-Make sure the folder exists
-Make sure the file isn't read only
-Make sure no < > | [and other special characters] are in the filename
-Make sure the file path is less than 218 [characters]

This is the code for the refresh:

Range("YHOOCompanyData").QueryTable.Refresh BackgroundQuery:=False Range("GoogleWebData").QueryTable.Refresh BackgroundQuery:=False

These are the actual queries: ([Ticker] just refers to some other cell)

WEB 1

http://finance.yahoo.com/lookup?s=

["Ticker"]
Selection=EntirePage
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False

WEB 1

http://finance.google.com/finance?fstype=ii&q=

["Ticker"]
Selection=EntirePage
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False


Possible solutions I have tried already and haven't had much success...
1) Deleting the IE cache and restarting excel
2) adding a loop around the web query (maybe i'm doing it wrong, if anyone has sample code that would be nice) so it re-queries until it gets the data. maybe adding a 5 second wait time would help?

I try refreshing manually and it works maybe 70% of the time. It really varies.

 

[PHV]

Each refresh call should be on its own line of code:
Range("YHOOCompanyData").QueryTable.Refresh BackgroundQuery:=False
Range("GoogleWebData").QueryTable.Refresh BackgroundQuery:=False

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Or you could...

dim qt as querytable
for each qt in ActiveSheet.QueryTables
  qt.Refresh BackgroundQuery:=False
next

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[bob135]

Oh sorry that was a pasting error. They are on separate lines in the macro, so that definitely isn't the issue.

 

[SkipVought]

"Oh sorry that was a pasting error. They are on separate lines in the macro, so that definitely isn't the issue."

So, you have no further questions and everything has been resolved?

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[bob135]

NO! My macro is still returning data some of the time and throwing errors other times.

 

[SkipVought]

If you'ld like help, you need to be forthcoming with your responses.

You can DOCUMENT each query, and use the results to determine any problems with connestion string.

dim ws as worksheet, qt as querytable, lrow as long
worksheets.add
lrow = 1
for each ws in worksheets
  for each qt in ws.querytables
     with activesheet
        .cells(lrow, "A").value = qt.parent.name
        .cells(lrow, "B").value = qt.name
        .cells(lrow, "C").value = qt.connection
        lrow = lrow + 1
     end with
  next
next

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[bob135]

If theres a way to display more detailed debug information that would be great. I'd be happy to post if it if you link me to a guide explaining how to do this.

Otherwise, I'll try to clarify. I'm using these two commands and queries i posted above (on separate lines..as i noted in the reply). Sometimes it gets the data. Sometimes it doesn't. This is using the EXACT SAME TICKER. It doesn't matter if I manually refresh or use the program, it still doesn't consistently get results.

Does that help?

 

[SkipVought]

I cannot duplicate you advertised conditions. Sorry.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[bob135]

You mean you run the query and it works 100% of the time?

 

[SkipVought]

100% of 5 times.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[bob135]

What OS/Excel Version are you using?

 

[SkipVought]

Windows XP Professional/Office Professional 2003 SP3

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[bob135]

Well I have it on loop so it goes through 30 or so tickers in about a minute. Can you do that with no problems?

 

[SkipVought]

Option Explicit

Sub Macro1()
    With Sheet1.QueryTables(1)
        .Connection = "URL;[URL unfurl="true"]http://finance.yahoo.com/lookup?s=NG"[/URL]
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    Application.OnTime Now + TimeValue("00:00:02"), "Macro1"
End Sub
Sub GetIt()
    Application.OnTime Now + TimeValue("00:00:02"), "Macro1"
End Sub

I'm running this query every two seconds. It's been running for nearly TEN minutes.
[tt]
Connecting to web...
Retrieving data from web site...
copying data to sheet...
Ready
[/tt]
The essential data...
[tt]
Symbol Name Last Trade Type Industry/Category Exchange
600965.SS FORTUNE NG FUNG FO 'A'CNY1 7.68 Stock SHH
V1:NG-TV.CBT Five-Year Interest Rate Swap Fu 0 Stock CBT
LIKMF.PK LIM KAM NG AM BHD 0.05 Stock PNK
NG-B.L NAT GRID NCP -B- 09 65 Stock LSE
NG-B.VX NAT GRID NCP -B- 09 0 Stock VTX
NG.L NATIONAL GRID 728 Stock LSE
NG-.VX NATIONAL GRID 7.21 Stock VTX
NG.TO NOVAGOLD RESOURCES COM NPV 7.84 Stock TOR
NG-WTA.TO NOVAGOLD RESOURCES INC WRT 0.03 Stock TOR
NG NovaGold Resources Inc. 7.8 Stock Gold ASE
[/tt]


Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[mikee63]

bob135,
I was just reading this entire thread since I have the exact 'random' error. Did you ever get your problem resolved?
Mike