Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    Web Query syntax error

    When recording a macro for specific web query,
    it works fine when it looks like this after recording:

    Code:
    With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;http://webpage2.com/gardening/2013/3/29/workday", Destination:=Range("A3")) 'works OK
    But specified dates are required to be entered, so a Cell reference is used from a active work sheet

    Code:
     With ActiveSheet.QueryTables.Add(Connection:= _
           "URL;http://webpage2.com/gardening/" & Range("A1") & , Destination:=Range("A3"))
    But it errors in red.
    Even though the cell Value in Sheet1 A1 is exactly the same
    2013/3/29/workday
    Why does it red syntax error ?

    Thanks
    Last edited by XPDiHard; 2013-03-29 at 06:25.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,830
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi

    What about trying..


    zDateString = [a1]
    zConnection = "URL;http://webpage2.com/gardening/" & zDateString
    With ActiveSheet.QueryTables.Add(Connection:=zConnectio n, Destination:=Range("A3"))

    zeddy

  3. #3
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Thanks zeddy.

    Maybe I am not reading your code right, but I put in;
    Code:
    zConnection = "URL;http://webpage2.com/gardening/" & zDateString
    With ActiveSheet.QueryTables.Add(Connection:=zConnection, Destination:=Range("A3"))
    It tried to query, but errored 1004

    Some history.
    Originaly, the old web ( webpage1.com) page, the code was changed to;

    Code:
    .(Connection = "URL;http://webpage1.com.au/" & Range("A1") & ".html"
    This worked oerfectly.
    It referenced the date from Cell A1
    Since webpage1.com.au is no longer,
    when recording a new web query macro from
    webpage2.com/gardening/2013/3/29/workday
    is what is shown in VBA after recording.
    The data does get imported, that web address does exist.

    However,
    To get another new date, one has to go into VBA editor and change that date.
    But because the macro is on AutoStart at a specific time of day, it needs to referenced from the date
    in Cell A1. Not the DateString from the machine.

    What I tried with your zDateString is;
    Code:
    zConnection = "URL;http://webpage2.com/gardening/" & zDateString
    With ActiveSheet.QueryTables.Add(Connection:=zConnection, Destination:=Range("A3"))
    What I don't get is, how zdateString syntax "knows" it's required date is in Sheet1 A1
    or am I missing something here ?

    I also tried,
    Code:
    zConnection = "URL;http://webpage2.com/gardening/" & zRange("A1") _
    With ActiveSheet.QueryTables.Add(Connection:=zConnection, Destination:=Range("A3"))

    Thanks.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Try this complete code. Replace with an actual website or an error will occur because no connection can be made. I have tested with a different website by concatenating the contents of Cell A1 and it worked fine. I had also verified that the connection string was correct. Data Menu> Connections> Properties> Definition Tab> Connection String.

    HTH,
    Maud

    Code:
    Public Sub Horticulture()
        With ActiveSheet.QueryTables.Add(Connection:= _
            "URL;http://webpage2.com/gardening/" & Range("A1"), Destination:=Range("$A$3"))
            .Name = "Garden"
            .Refresh BackgroundQuery:=False
        End With
    End Sub
    Last edited by Maudibe; 2013-03-30 at 12:17.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    Solved

    Thanks,
    worked perfectly.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    Thanks all who helped with this phase of my project.

    I have uploaded a copy of the workbook, it may need some
    code-cleaning-uppers and tweaks for "correctness", more stuff to learn from.
    Attached Files Attached Files

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Well, how cool is that? Very clever. Your Cells.Replace code is very interesting. That could be very handy to master. I see how the components have come together. My only suggestion would be to add some error handling in case of unforseen web site or connection issues. Nicely done and thanks for posting.

    Maud
    Last edited by Maudibe; 2013-03-31 at 03:57.

Posting Permissions

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