Web Query syntax error

Topic

New Reply

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

Reply | Quote

Replies

Hi

What about trying..

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

zeddy

Reply | Quote

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.

Reply | Quote

Try:

Code:

With ActiveSheet.QueryTables.Add(Connection:= _
       “URL;http://webpage2.com/gardening/” & Range(“A1”) & Chr(34), Destination:=Range(“A3”))

Reply | Quote

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

Reply | Quote

Thanks,
worked perfectly.

Reply | Quote

The workbook,
It may need some cleaning up or more efficient coding…
But it gets the list in the correct order.

Thanks Maud,Zeddy and everyone who helped.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote