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

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Web query in macro doesn't work until you manually edit it (https://www.excelbanter.com/excel-programming/326957-web-query-macro-doesnt-work-until-you-manually-edit.html)

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

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

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

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

wrote in message
oups.com...
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

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

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

Robin Hammond wrote:
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

wrote in message
oups.com...
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

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

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

wrote :

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

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

Don Guillett wrote:
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...150-1112822060
-753119"
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

--
Don Guillett
SalesAid Software

"Moxy" wrote in message
ups.com...
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...50-1112822060-

Notice the trailing hyphen.

Which version of Excel are you using?

Moxy

Don Guillett wrote:
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...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.

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
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