+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    moxy_fruvus@yahoo.com
    Guest

    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


  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    Moxy

    It looks like the URL is not correct

    The 1st part of the Url http://online.bridgebase.com/myhands/ opens up a page that reuires a user name and an option for other fields.

    You Url does not appear to list these required fileds and there for the query can not retrieve any data

  3. #3
    Robin Hammond
    Guest

    Re: 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

    <moxy_fruvus@yahoo.com> wrote in message
    news:1112832781.419253.216530@o13g2000cwo.googlegroups.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
    >




  4. #4
    Moxy
    Guest

    Re: 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
    >
    > <moxy_fruvus@yahoo.com> wrote in message
    > news:1112832781.419253.216530@o13g2000cwo.googlegroups.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
    > >



  5. #5
    keepITcool
    Guest

    Re: 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


    moxy_fruvus@yahoo.com wrote :


  6. #6
    Don Guillett
    Guest

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

    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
    donaldb@281.com
    <moxy_fruvus@yahoo.com> wrote in message
    news:1112832781.419253.216530@o13g2000cwo.googlegroups.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
    >




  7. #7
    Moxy
    Guest

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

    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
    > donaldb@281.com
    > <moxy_fruvus@yahoo.com> wrote in message
    > news:1112832781.419253.216530@o13g2000cwo.googlegroups.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
    > >



  8. #8
    Don Guillett
    Guest

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


    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
    donaldb@281.com
    "Moxy" <moxy_fruvus@yahoo.com> wrote in message
    news:1112881978.356318.61300@z14g2000cwz.googlegroups.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
    > > donaldb@281.com
    > > <moxy_fruvus@yahoo.com> wrote in message
    > > news:1112832781.419253.216530@o13g2000cwo.googlegroups.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
    > > >

    >




  9. #9
    Moxy
    Guest

    Re: 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
    > donaldb@281.com
    > "Moxy" <moxy_fruvus@yahoo.com> wrote in message
    > news:1112881978.356318.61300@z14g2000cwz.googlegroups.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
    > > > donaldb@281.com
    > > > <moxy_fruvus@yahoo.com> wrote in message
    > > > news:1112832781.419253.216530@o13g2000cwo.googlegroups.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
    > > > >

    > >



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1