System Error

[David]

Hi Guys & Gals,

I am running a Querry:
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?s=" & (Symbol) & "",
Destination:=Range("A1"))

and I get an error:
"System error &H80070057(-2147024809). The parameter is incorrect."

This is inside existing code, where I have run several (at least 3) other
"QueryTables" with out a problem. The Query runs fine, if it is done alone,
in another Module, but the error occurs where I have already run the others.

It appears to be a memory problem, but I am not certain. Any insight would
be appreciated.

Thanks,

 

[Don Guillett]

Try this. However, subsequent queries will build up defined names which
should be deleted. Better to establish the query and just refresh with a
different symbol or list using a loop

Sub MakeQuery()
symbol = "MSFT"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?s=" & symbol & "", _
Destination:=Range("A4"))
.Refresh BackgroundQuery:=False
End With
End Sub
Feel free to send your workbook to my address below. This is the sort of
thing I do daily for clients.

 

[David]

Hi Don,

I tried this, but now I am getting "run-time error '5': Invalid agurment or
procedure"

This is the code, symbol is an input and has run previously on 3 other tables.
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?s=" & symbol & "", _
Destination:=Range("A4"))
.Refresh BackgroundQuery:=False
End With

This is a lot shorter code than I am using on the other tables, but be a lot
of "Extra" code that is default code?

Thank you for your time and efforts.

David,

 

[David]

Hi Again Don,

How would I go about deleting previous queries with defined names. I am
walking down a worksheet and adding several queries, Income Statement,
Balance Sheet, Cash Flow, stock price Hi and Low during the year, etc. I am
then doing various analyses on the data. Many variables and quite a few web
queries.

Some of the sites have changed, so the code had to be re-examined, in is in
this process that i have run into the problems and errors.

Thank you again for your time and efforts. The file that this is all in is a
temp file, not yet even saved. The orginal has many modules in it.

Thank again,
David

 

[David]

Hi Again,

Seems I am back to the same error as I started with now.

Thanks,
David

 

[Don Guillett]

I have been out of the office for awhile. Send your workbook to my address
below, if you like and I will take a look.

 

[Don Guillett]

sub deletenames
for each n in sheets("sheetname").names
if n <> "dontdeletethisone" then n.delete
next

 

[David]

Hi Don,

Thank you for your time and efforts. The if n <> "dontdeletethisone" then
n.delete
did solve many of the problems. The problem now is that the Excel sheet does
not have enough time to refresh when a web site is brought into it. The next
command executes and it all happens so fast the first table is not there.
Partly because the internet connection is not as fast as it could be.

I tried Wait, but it suspends all execution, so it fails to bring in the
table too.

Any ideas would be great.

Thanks,
David

 

[Don Guillett]

I have a fast computer and fast connection. Send to me if you like for
testing.

 

[David]

Hi Don,

I am trying to get it to work for me, on my computer, with the slower
connection. I am looking for a technique to allow the web site to populate
the work sheet. Stepping throught it already works, when I let the connection
populate it.

Thank again for your time and efforts.

David

 

[David]

Hi Again,

It looks like I have to create a Class Module tied to an event, Refreshing
the Query? In reading about this I am afraid I got lost. Looks like I need an
Object first in "a" or "the" class module, then trigger and event? It looks
like I would have to do this several times, since I am creating several
queries.

Looks like the other choice is to put in a stop after each query, let it
populate, then move on to the next query and stop it, etc.

Thanks for your time and efforts,
David

 

[David]

Again (people are starting to talk, just joking),
This is what I put in the Class Module:

Public WithEvents qtQueryTable As QueryTable
Sub InitQueryEvent(QT As Object)
Set qtQueryTable = QT
End Sub

Private Sub QueryTable_AfterRefresh(Success As Boolean)
If Success
Return
Else
Stop
End If
End Sub

And I think I have to put something like this is the regular module, but I
am not certain:
Sub RunInitQTEvent
clsQueryTable.InitQueryEvent _
QT:=ActiveSheet.QueryTables(1)
End Sub

The above is almost direct from the Help files. Thank you for your time and
efforts.

David