Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
web query in VBA (format dates in url)
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=volfor...n&BeginDat e= & 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 -- slimswo ----------------------------------------------------------------------- slimswol's Profile: http://www.excelforum.com/member.php...fo&userid=3201 View this thread: http://www.excelforum.com/showthread.php?threadid=51756 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
web query in VBA (format dates in url)
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¶ms=srgEd&status=Open&BeginDat e=" 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" wrote in message ... 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¶ms=srgEd&status=Open&BeginDat e=" & 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
web query in VBA (format dates in url)
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¶ms=srgEd&status=Open&BeginDat e=" & 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 -- slimswol ------------------------------------------------------------------------ slimswol's Profile: http://www.excelforum.com/member.php...o&userid=32014 View this thread: http://www.excelforum.com/showthread...hreadid=517569 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
web query in VBA (format dates in url)
Is it as simple as fixing:
& "EndDate=" & to be: & "&EndDate=" & |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
web query in VBA (format dates in url)
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 ------------------------------------------------------------------------ slimswol's Profile: http://www.excelforum.com/member.php...o&userid=32014 View this thread: http://www.excelforum.com/showthread...hreadid=517569 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
web query in VBA (format dates in url)
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! -- slimswol ------------------------------------------------------------------------ slimswol's Profile: http://www.excelforum.com/member.php...o&userid=32014 View this thread: http://www.excelforum.com/showthread...hreadid=517569 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
web query in VBA (format dates in url)
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query equasion for dates | Excel Worksheet Functions | |||
Group Dates from SQL Query | Excel Discussion (Misc queries) | |||
Format text 'dates' to real dates | Excel Worksheet Functions | |||
the dates on cell format make different dates. | New Users to Excel | |||
dates in query | Excel Worksheet Functions |