QueryDef; Error 1004
 

Hello,

I often import stock quotes using the QueryDef method without any problems.
But for some reasons I'm not able to import certain stocks quotes. Sometimes
the macro works, sometimes not (Error 1004) and I don't know why. I created
the following example:

Sub ImportData()
Dim ws As Worksheet
Dim qry As QueryTable
Dim n As Name
Dim sURL As String

sURL = "http://www.swissquote.ch/fcgi-bin/stockfquote?symbols=ALFGR"

Set ws = ThisWorkbook.Worksheets(1)

'delete old data
ws.Cells.Delete
For Each qry In ws.QueryTables
qry.Delete
Next qry
For Each n In ThisWorkbook.Names
n.Delete
Next n

'import
With ws.QueryTables.Add(Connection:="URL;" & sURL,
Destination:=ws.Range("$A$1"))
.Name = "stockfquote?symbols=ALFGR"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub


Has anybody an idea why this script generates the error 1004?

Tom

QueryDef; Error 1004
 

I just ran your code in xl2003, as written, and except for the fact that
there are unnecessary lines, it worked many times without any problem. I do
this sort of thing for clients all the time. You would need to make sure
that the url recognizes the symbol as you have it. Do they have a symbol
lookup?

Surely you are not creating a separate query for each symbol in a portfolio.
You should be able to have a list do with a loop or even all with ONE fetch.
Send an email to my private address and I'll send a file that uses Yahoo but
did not know this symbol.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tom" wrote in message
...
Hello,

I often import stock quotes using the QueryDef method without any
problems. But for some reasons I'm not able to import certain stocks
quotes. Sometimes the macro works, sometimes not (Error 1004) and I don't
know why. I created the following example:

Sub ImportData()
Dim ws As Worksheet
Dim qry As QueryTable
Dim n As Name
Dim sURL As String

sURL = "http://www.swissquote.ch/fcgi-bin/stockfquote?symbols=ALFGR"

Set ws = ThisWorkbook.Worksheets(1)

'delete old data
ws.Cells.Delete
For Each qry In ws.QueryTables
qry.Delete
Next qry
For Each n In ThisWorkbook.Names
n.Delete
Next n

'import
With ws.QueryTables.Add(Connection:="URL;" & sURL,
Destination:=ws.Range("$A$1"))
.Name = "stockfquote?symbols=ALFGR"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub


Has anybody an idea why this script generates the error 1004?

Tom