web query in VBA (format dates in url)

[slimswol]

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

I will be entering the status parameter manually into the url, but th
dates need to be in the format (yyyy-mm-dd) and both fields need t
have today's date. It sounds simple, but I can't seem to get it t
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!
Thank

 

[R..VENKATARAMAN]

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]

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

 

[Randy Harmelink]

Is it as simple as fixing:

& "EndDate=" &

to be:

 

[slimswol]

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!

 

[slimswol]

Hey,

Since the macro now works, I thought this would automatically update
the data every day. I found out the next day that the sheet in which
the raw data is on, and the macro is run, does not automatically
update.


From my understanding, I though I would be able to refresh the data /
and macros from another macro I created. The macro below, from my
understanding, refreshes the data and the query on a particular
spreadsheet specified.

A button has the "Refresh_Info" macro on the graph shreadsheet.

Anybody have any ideas on what else is needed to get the data to be
refreshed and the macro to re-run so that the current date data is on
the speardsheets every day?

Sub Refresh_Info()
'
' Refresh_Info Macro
'


Sheets("Graph").Range("B2") = "Updating Sheet1..."
Sheets("Sheet1").Range("A1").QueryTable.Refresh
BackgroundQuery:=False

Sheets("Graph").Range("B2") = "Updating Sheet2..."
Sheets("Sheet2").Range("A2").QueryTable.Refresh
BackgroundQuery:=False


Thanks for any assisstance in advance!

 

[Randy Harmelink]

I thought this would automatically update the data every day. <<

The QueryTable method does have a "RefreshPeriod" property. For
example, if you had created the table with this in the "With" group:

.RefreshPeriod = 60

....then it would automatically refresh the query every 60 minutes. I
would think you'd run into timing issues if you made the 1440 minutes
though, since the start time might be when you open the workbook.

You could go into each query table and edit the "Data Range Properties"
and change it.