Smart Web Queries in Excel

[lukman4068]

Hi,

I wanted to know if this is possible.

I have a scenario where I have reports generated on a website from a different department.

The issue is that because they're daily updates, the link for the most up to date information changes daily to that days date, for example,

For the reports from yesterdays work i'll have a link which ends 27jul.htm, but the next day I'll have a link ending 28jul.htm - and the reports are only generated Monday - Saturday.

Is there any way of getting excel to know to change the website that it tries to draw data from automatically by amending the link to match, say for example today()-1

or something like that which also excludes Sunday and Bank Holiday reporting?

Thanks for your advice,

 

[Norie]

Should be pretty straightforward to generate the that part of the link based on the current date.

The harder part would be dealing with the dates you don't want to include.

What date(s) would you want to use for those?

By the way have you ever considered not naming the reports like this?

Perhaps give them a generic name and when you create a new report archive the previous one with the relevant date.

 

[lukman4068]

Should be pretty straightforward to generate the that part of the link based on the current date.

The harder part would be dealing with the dates you don't want to include.

What date(s) would you want to use for those?

By the way have you ever considered not naming the reports like this?

Perhaps give them a generic name and when you create a new report archive the previous one with the relevant date.

They're not date's rather days - Sunday's and Bank Holiday dates.

I have thought about getting the reports generated on something more generic - for example to always be today.html or yesterday.html but I'm not sure how long it will take to get that sorted so wanted to see if there are any other ways round it for now.

How would you do it for the current date? it's not the whole date - it is normally in the format as above of www.website.com/28jun.html

 

[Norie]

For today.

strLink = "www.website.com" & Format(Date(), LCase("ddmmm")) & ".html"

 

[lukman4068]

i take it that you post that into the actual web query address line where the browser menu appears?

 

[Norie]

Oops. my bad I assumed you were using VBA.

If your not then you could still create the link in a cell but I don't know how, if you even can, you could use that for a web query outside code.

 

[lukman4068]

I don't know anything about VBA What would I have to do to use VBA to make that work? :-/

I'm suspecting then the easiest option is to get the report pages to be generated on something more generic as suggested earlier...