Add worksheets with Web Query

  • Thread starter Thread starter herbwarri0r
  • Start date Start date
H

herbwarri0r

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

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

Really appreciate any help on this one. Oh I have been looking at th
code at the bottom of this page http://www.ozgrid.com/News/jan-2006.ht
but couldn't get it to work for me
 
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
sheet>copy template sheet>name it with date.
 
Don said:
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

sheet>copy template sheet>name it with date.
Click to expand...

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.
 
Back
Top