Loop Import HTML Tables, Extract 3 Cells from Table, Do Calculation on Cells

Hi Everyone,

Bit of a challenge I have come up against to import tables stored in HTML across a number of subfolders.

Every day an HTML file is dumped on a server subfolder in the format of \\server\archive\YYYYMM\DD\daily_rates.htm (the server does not have http accessibility, and I get file not found if I try existing scripts I have seen). I have attached the daily rates table (has extension txt, change to htm), with some modifications for privacy (there are actually a larger number of tables listed, but we only use the ones in the attached as provided by a 3rd party).

What I want to do is:

1. Open the subdirectory YYYMM\DD\daily_rates.htm
2. Copy the data from particular cells in the tables into another sheet (the headings should be DATE, TERM, RATE1, RATE2):
- Rate 1 and Rate 2 for 1M - 3M from Base Yield Curve table
- BBSWMID (as Rate1) for 4M-6M from AFMA Data, and Rate2 for 4M-6M from Base Yield Curve Table
3. Do some additional calculations to generate 7M-12M numbers off the 1-6M numbers, and some additional calculations for each rate in columns to the right of the data (for script purposes, say 7M = (1.02 * 1M), 8M = (1.02 * 2M) etc, additional column is Datevalue&Month of the row)
4. Loop onto the next day/month/year, with the results as new rows on the end of the previous day (there will be no data for any weekend date, or public holiday)

I've seen a few scripts that do parts of what I'm after, but I'm not competent enough in VBA to put it all together.

Any ideas where I should start?

Thanks!

Hello jhowland,

Welcome to the Forum!

I am assuming the date you are referring to is the effective date. Is that correct?

Hello jhowland,

I am putting together some code to open the file and copy the tables to a worksheet. This will provide us with a starting point.

Hello jhowland,

Paste these three macros into a new VBA Module. The macro TestA will copy the tables from the file name you specify in the TestA to the "Sheet3" starting at cell A1.
The macro CopyTables sets the starting cell for the table output.

Please Login or Register  to view this content.

Hi Leith,

Thanks for your code. I may have been a little vague in my requirements before - what I've got is about 1000+ individual HTML files in the folder format described above (\YYYMM\DD\daily_rates.htm) above that I'm trying to import.

The closest I can get to my solution is described here:

HTML Code: 

, but my issue is that the files aren't neatly in a form where I can use a 'For' statement (because they are in multiple subdirectories they aren't neatly numbered 1-1000).

I think what may be necessary is to list all the files in a new sheet (Column A, rows 1->n) and then run a loop off that. I have reworked jindon's code available from here:

HTML Code: 

to build a list of actual directory and filename (reworked so that the entire path is in the A column and not split). I then applied the code from here:

HTML Code: 

and I was able to build a list vertically down the page. I suspect I am closer than I think, in that I should need to copy the relevant data and then clear the page, rather than going down the next line.

Hello jhowland,

The macro I wrote was to show you how to pull the data from the three tables into a worksheet, nothing more. As I stated before, this is starting point in a more complex chain of events.
I understand you have many of these URLs in a column and require a loop to extract the data from each site. What I need from you at this point is a workbook with a short list of these URLs, comments, before and after examples of the output.

If you choose to seek help from other forums, please update the status of your solutions. No one wants to spend time writing code for a problem that already has been solved. Too many cooks in the kitchen can hider getting the orders out on time.

If you can post a copy of the workbook for review, it will help us find a solution faster.

How To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

File Manger Picture