[RESOLVED] find sheets in html from excel workbook

[Resource Dragon]

I am trying to put together something that will take a couple of manky spread sheets and combine them into one neat summary. The manky spread sheets are the result of a data export from a web-based program. As far as Excel is concerned, these spread sheets are in html.

I need a sub routine that selects the correct sheet so that it can be cleaned up. If I manually move the sheet into the workbook where I’ve got the subs a simple

For each spreadsheet in Sheets
If...

routine selects the right sheet, but I need a way of searching outside the work book, so that the user doesn’t need to move the spread sheets manually.

When the spread sheets are down-loaded their names are essentially random.

The sub routine needs to work in Excel 2003.

[Siddharth Rout]

Three things...

1) What code do you have so far?

2) Show us a sample of the "manky spread sheets"

3) Also show us how do you expect the summary to look?

[westconn1]

for each w in workbooks
    for each s in w.sheets

[Resource Dragon]

Once I've selected the sheet, I've got the code that cleans it up working nicely. The clean-up involves unmerging all cells, deleting unwanted columns, deleting blank rows and a bit of formatting - and naming the sheet!

The second sheet gets a similar clean-up and then I've got a third lot of code that populates the first sheet with data from the second.

I'll try your code at work tomorrow - this is a work project but I can't log in to VB Forums at work.

My only other unsolved problem with this project is including today's date in the file-name when I save the nice, clean summary sheet.

Thank you

[Siddharth Rout]

My only other unsolved problem with this project is including today's date in the file-name when I save the nice, clean summary sheet.

Strname = Format(Now,"DD-MM-YYYY")

You can then use Strname in the name of the sheet.

[Resource Dragon]

Thanks, the
for each w in workbooks
for each s in w.sheets
code does the trick. I'll try koolsid's date solution & see if I can get it to work tomorrow.