Web query in macro doesn't work until you manually edit it

[moxy_fruvus]

Hi all:

I've assigned the following macro containing a web query to a
workbook (not a particular sheet in a workbook):

Sub GetListOfTravellersForATournament()
'
' X Macro
' Macro recorded 3/23/2005 by AdministratorHDT
'
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range("A1:Z200").Delete shift:=xlShiftUp
Range("A2").Select
'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://online.bridgebase.com/myhands/" & _
"hands.php?tourney=150-1112822060-", _
Destination:=Range("A1"))
.Name = _
"Tournament #" & Worksheets("Sheet1").Range("A1").Value
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "3"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

When I run the macro from the Tools / Macro / Macros dialog box nothing
happens. I receive no error messages and the query does not populate
Sheet1.

When I use the Data / Import External Data / Edit Query Data menu to
bring up the query, immediately press the Cancel button, and then
execute the Macro it runs successfully.

I tried using the Macro Recorder to see what changes Excel makes to the
query when I execute the Data / Import External Data / Edit Query Data
command sequence. The resulting macro is empty! Go figure.

What gives? I admit I am a newbie regarding web queries. Other than the
debugging I described above I don't know how else to debug them. Any
debugging suggestions welcome.

There's probably something simple going on here that I don't
understand. Can anyone help me?

Moxy

 

[Robin Hammond]

Moxy,

This is not very helpful, but I can't see anything wrong with it. Your macro
works for me in Excel XP.

Robin Hammond
www.enhanceddatasystems.com

 

[Moxy]

I forgot to mention that I'm using Excel 2002 (10.5815.4219) SP-2.

 

[keepITcool]

Moxy:
also note following:

By adding queries instead of modyfying an exiting one,
you should be aware that you build up a lot of dirt
in the names collection.

When you add a querytable an equivalent name object is created.
when you subsequently delete the rows the name object remains.
(but points to non existing rows..

I've seen books with thousands of names...
all created be similar procedures... but there comes a point
the book is going to crash.

So first check and possibly (likely?) clean up the names.
Then clean up your code to something like..

Sub hmm()
Dim qt As QueryTable
Set qt = Worksheets("sheet1").QueryTables(1)
qt.Connection = "URL;http://www.microsoft.com"
qt.Refresh
End Sub

HTH..


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


(e-mail address removed) wrote :

 

[Don Guillett]

I tried your macro and it did not produce a query so I went to the url given
and got this.
http://online.bridgebase.com/myhands/hands.php?tourney=150-1112822060
Invalid input. Please use this page

Also, if you are to re-create the query each time, I suggest you delete rows
2:?? and have your query goto a2 instead of a1. Also, delete the query name
in defined names or the list will GROW. Maybe a refresh method with the
macro changing the parameter instead.

 

[Moxy]

Hi Don:

The query you used in your test contains an invalid URL. I suspect
that when you copied it from the source code you omitted the trailing
hyphen. The URL as it appears in the macro is valid:

http://online.bridgebase.com/myhands/hands.php?tourney=150-1112822060-

Notice the trailing hyphen.

Which version of Excel are you using?

Moxy

 

[Don Guillett]

I am using xl2002.sp2(xp)
When I use the hyphen this is all I get
Tourney 150-1112822060-
Board 1 Traveller
Board 2 Traveller
Board 3 Traveller
Board 4 Traveller
Board 5 Traveller
Board 6 Traveller
Board 7 Traveller
Board 8 Traveller
Board 9 Traveller
Board 10 Traveller
Board 11 Traveller
Board 12 Traveller

When I goto the web site and pull up board 3 and right click to import to
excel and record while editing, I get this macro. The macro can be made to
pull in each board in a loop if you know the -????? for each board.


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/7/2005 by Don Guillett
'

'
With Selection.QueryTable
.Connection = _

"URL;http://online.bridgebase.com/myhands/hands.php?traveller=150-1112822060
-753119"
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Moxy]

Don:

Adding the hyphen returns the correct results.

I see that you are using Excel XP version 12 and it works fine. Robin
Hammond reported earlier in this thread that my macro also worked for
him under Excel XP - although he didn't mention the specific version
number.

When I run the macro under Excel 2002 (exact version number and
service pack level mentioned previously in thread) it returns nothing -
not even an error message.

Is there something obviously wrong with the macro code in my initial
post? I thought the info I posted there was complete and the example
was simple and self-contained.

I appreciate the suggestions recommending that I modify an existing
query as opposed to creating a new one each time I run the query. I
plan to implement that code in the next version of my program. However
that doesn't address my original issue.

Moxy