Hello,
I'm using a webquery to pull data down to a table in Excel. The query has two hard coded dates in it, but I want to replace them with values stored in cell on another tab in the same workbook. The idea is I can change the dates in the cells, then refresh the query to get new data from the new dates. Here is the webquery I'm using with the hard coded dates.
= Xml.Tables(Web.Contents("https://host.domain.tld/v1234/webreport/?c=abc&u=DBWebQuery&p=REDACTED&r=Time&q=(member_id+%3d%27User1%27+OR+member_id+%3d%27User2%27+OR+member_id+%3d%27User3%27+OR+member_id+%3d%27User4%27)+AND+Location+%3d%27Location1%27+AND+Date_Start+%3e%3d%2704%2f16%2f2023%27+AND+Date_Start+%3c%3d%2704%2f30%2f23%27&f=Date_Start&f=company_name&f=member_id&f=hours_actual&f=SR_Service_RecID&f=Location&f=BusGroup&f=work_role&f=work_type"))
The parts I want to replace with references to cells are:
Date_Start+%3e%3d%2704%2f16%2f2023%27
And...
Date_Start+%3c%3d%2704%2f30%2f23%27
I'd like the first one to reference cell A1 (which contains a date, such as 04/16/2023) and the second one to reference cell A2 (which contains a date, such as 04/30/2023).
I've tried to name the cells, and use this instead of the hard coded date:
"&Excel.CurrentWorkbook(){[Name="WeekStart"]}[Content]{0}[Column1]&"
"&Excel.CurrentWorkbook(){[Name="PeriodEnd"]}[Content]{0}[Column1]&"
...but I keep getting errors in the webquery. So, I then created three new cells for each date, using the following code:
B1 = TEXT(WeekStart,"mm")
B2 = TEXT(WeekStart,"dd")
B3 = TEXT(WeekStart,"yyyy")
B4 = TEXT(PeriodEnd,"mm")
B5 = TEXT(PeriodEnd,"dd")
B6 = TEXT(PeriodEnd,"yyyy")
I named each cell, such as WeekStartMM, WeekStartDD, WeekStartYY and PeriodEndMM, PeriodEndDD, PeriodEndYY. Then updated my webquery as follows:
= Xml.Tables(Web.Contents("https://host.domain.tld/v1234/webreport/?c=abc&u=DBWebQuery&p=REDACTED&r=Time&q=(member_id+%3d%27User1%27+OR+member_id+%3d%27User2%27+OR+member_id+%3d%27User3%27+OR+member_id+%3d%27User4%27)+AND+Location+%3d%27Location1%27+AND+Date_Start+%3e%3d%27"&Excel.CurrentWorkbook(){[Name="WeekStartMM"]}[Content]{0}[Column1]&"%2f"&Excel.CurrentWorkbook(){[Name="WeekStartDD"]}[Content]{0}[Column1]&"%2f"&Excel.CurrentWorkbook(){[Name="WeekStartYY"]}[Content]{0}[Column1]&"%27+AND+Date_Start+%3c%3d%27"&Excel.CurrentWorkbook(){[Name="PeriodEndMM"]}[Content]{0}[Column1]&"%2f"&Excel.CurrentWorkbook(){[Name="PeriodEndDD"]}[Content]{0}[Column1]&"%2f"&Excel.CurrentWorkbook(){[Name="PeriodEndYY"]}[Content]{0}[Column1]&"%27&f=Date_Start&f=company_name&f=member_id&f=hours_actual&f=SR_Service_RecID&f=Location&f=BusGroup&f=work_role&f=work_type"))
Now I get an error that states Conversion failed when converting date and/or time from character string.
Any help would be much appreciated!
Thanks,
Mr Z.
Bookmarks