Add worksheets with Web Query
 

I need some VBA to add worksheets in date order. For example I will name the first sheet 01012007, I then need a macro to add sheets for one quarter up to 31032007 but only for weekdays.

On top of this once the sheets are added I need a web query to be performed on each tab. The url will be in the format of http://domain.com/01012007.html. The url will only exist from the day before it's name and I will need data to be refreshed upon opening the workbook for all urls less than 3 days old.

Really appreciate any help on this one. Oh I have been looking at the code at the bottom of this page http://www.ozgrid.com/News/jan-2006.htm but couldn't get it to work for me.

Add worksheets with Web Query
 

Why do you need a separate query for each day? Lots of resources used.
I would suggest ONE query (with the day variable)pull data to template
sheetcopy template sheetname it with date.
--
Don Guillett
SalesAid Software

"herbwarri0r" wrote in message
...

I need some VBA to add worksheets in date order. For example I will name
the first sheet 01012007, I then need a macro to add sheets for one
quarter up to 31032007 but only for weekdays.

On top of this once the sheets are added I need a web query to be
performed on each tab. The url will be in the format of
http://domain.com/01012007.html. The url will only exist from the day
before it's name and I will need data to be refreshed upon opening the
workbook for all urls less than 3 days old.

Really appreciate any help on this one. Oh I have been looking at the
code at the bottom of this page http://www.ozgrid.com/News/jan-2006.htm
but couldn't get it to work for me.




--
herbwarri0r

Yes one query would suffice, I assume this would be a module? Any clue on what VBA I need to use. I must admit I'm not terribly proficient at creating code but can do some jiggery pokery.

What I need to happen then is when the workbook is open is (assuming I have all the tabs created) to call this module which will do something like;

Web Query http://domain.com/<todaysdate+1.html to sheet <todaysdate+1 (format being 31032007 in both cases)

Web Query http://domain.com/<todaysdate.html to sheet <todaysdate

Web Query http://domain.com/<todaysdate-1.html to sheet <todaysdate-1

I suppose looking at the above there's no real need to have the tabs created in the first place, a bit more VB could accomplish this. But then I can't gauruntee that the workbook would be opened everyday, I could be on leave etc. The intranet site that hosts the pages has the last 22 days and tommorows urls. So If I hadn't opened the workbook in a week I'd need it to scrape all the dates it had missed.

This workbook will be looked up on from other workbooks, possibly, to run queries. Or I may create a front end and run the queries in the same workbook.

Basically the pages I'm scraping are payments received by my company. These are BACS (2 day transfer) and CHAPS (same day) and I can see payments clearing for tomorrow. This is why once a url is one day old the data on it will not change. Now my team have a list of customers but the customer name does not match the payee name on the statement. Could be A Company Ltd on our system but their payments show up as A Co. Holdings.

In the past I have been saving the urls in html, opening in Excel and saving as .xls. Then we have a monthly master workbook which has a list of the customer name and payee names on one tab and masses of vlookups for 1 month for each customer on each day. The headache with this is using find and replace to update the vlookups for the next month and saving the urls in the first place. Also I have the drawback of only displaying the first payment from a customer if they made more than one on a given day.

So once I'm automatically scrapping the data into one workbook I'd like to have a front end with options to 'Show all payments from Company A in last week' or 'Show all payments for My customers today'. I should be able to create these queries myself with a bit more research.