Use a variable based on a cell in a WebQuery

[Mr. Z]

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.

Please Login or Register  to view this content.

The parts I want to replace with references to cells are:

Please Login or Register  to view this content.

And...

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

...but I keep getting errors in the webquery. So, I then created three new cells for each date, using the following code:

Please Login or Register  to view this content.

I named each cell, such as WeekStartMM, WeekStartDD, WeekStartYY and PeriodEndMM, PeriodEndDD, PeriodEndYY. Then updated my webquery as follows:

Please Login or Register  to view this content.

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.

[Mr. Z]

With many google searches and trial and error, I was able to find a solution.

Using this formula was step 1:

Please Login or Register  to view this content.

I named two cells WeekStart and PeriodEnd in my workbook. But the values of those cells needed to be in a formula of

Please Login or Register  to view this content.

and the CELLREF was another cell where I actually enter the date. This allowed me to enter a date in one cell, which was converted to text in another cell that was used in the webquery.

Just thought I'd share my solution in case anyone else ever ran into this problem. I'm not sure if my solutions is the most elegant, but it does work!

Mr Z.