Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#3
|
|||
|
|||
Quote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query worksheets in same workbook | Excel Worksheet Functions | |||
Save data retreived from query without saving query | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook? | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |