web query in VBA (format dates in url)

Hello,
I have looked everywhere and I cannot seem to get an example to find out what I am doing wrong. I need to create a web query within a macro for Excel. The web query will return results from a form which has 3 fields:
Field 1: Status
Field 2: BeginDate
Field 3: EndDate

I will be entering the status parameter manually into the url, but the dates need to be in the format (yyyy-mm-dd) and both fields need to have today's date. It sounds simple, but I can't seem to get it to work. Here is some code which is very simular to what I have:


Sub URL_Query()

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://mywebsite.com/abc?name=volform&params=srgEd&status=Open&BeginDate=" & Format(Now(), "yyyy-mm-dd") & EndDate=" & Format(Now(), "yyyy-mm-dd")", _
Destination:=Range("a1"))

.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub


Any help on this would be greatly appreciated!
Thanks

I am onl loudly thinking and have not tested thoroughly

type a formula
=YEAR(TODAY())&"-"&MONTH(TODAY())&"-"&DAY(TODAY())
it will 2006-3-1 and it wil be text
this can be used to fill formula
instead of introducing the dates every date
you separae the common part of the url and enter it in a cell e.g. A1 (if
necessary in two or three cells)
http://mywebsite.com/abc?name=volform&params=srgEd&status=Open&BeginDate="

then enter the formula given above in A2
in macro it will be something like this
range("a1") & range("a2") & "&" & range("a2")

I have given only a vaue indication. you can work on this suggstion and
improve.

if you want 2006-03-01 use an if fuction
if month(cell address)>9 the above formula otherwise concatenate 0(zeroes)
at proper place in the formula.





"slimswol" <slimswol.23yvnm_1141176301.1759@excelforum-nospam.com> wrote in
message news:slimswol.23yvnm_1141176301.1759@excelforum-nospam.com...
>
> Hello,
> I have looked everywhere and I cannot seem to get an example to find
> out what I am doing wrong. I need to create a web query within a macro
> for Excel. The web query will return results from a form which has 3
> fields:
> Field 1: Status
> Field 2: BeginDate
> Field 3: EndDate
>
> I will be entering the status parameter manually into the url, but the
> dates need to be in the format (yyyy-mm-dd) and both fields need to
> have today's date. It sounds simple, but I can't seem to get it to
> work. Here is some code which is very simular to what I have:
>
>
> Sub URL_Query()
>
> With ActiveSheet.QueryTables.Add(Connection:= _
> "URL;http://mywebsite.com/abc?name=volform&params=srgEd&status=Open&BeginDate="
> & Format(Now(), "yyyy-mm-dd") & EndDate=" & Format(Now(),
> "yyyy-mm-dd")", _
> Destination:=Range("a1"))
>
> BackgroundQuery = True
> TablesOnlyFromHTML = True
> Refresh BackgroundQuery:=False
> SaveData = True
> End With
> End Sub
>
>
> Any help on this would be greatly appreciated!
> Thanks
>
>
> --
> slimswol
> ------------------------------------------------------------------------
> slimswol's Profile:
> http://www.excelforum.com/member.php...o&userid=32014
> View this thread: http://www.excelforum.com/showthread...hreadid=517569
>

Any other ideas? I am usinmg the query below but I am getting an error "Application-defined or object-defined error"

Please help



Sub URL_Query()

With ActiveSheet.QueryTables.Add(Connection:="URL;http://mywebsite.com/abc?name=volform&params=srgEd&status=Open&BeginDate=" & Format(Now(), "yyyy-mm-dd") & "EndDate=" & Format(Now(), "yyyy-mm-dd"), Destination:=Range("a1"))

.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub

Is it as simple as fixing:

> & "EndDate=" &

to be:

> & "&EndDate=" &

That was the problem! I can't believe I spent that much time on something so simple.

Thanks for the help. You are the best!