Simple enough Excel macro baffling me...

jimi_t

I'm sure this should be really simple for all the hardcore office people out there but I'm really stuck...

Basically I've a lot of big Excel Workbooks with about 200 or so individual sheets in each. What I want to do within a workbook is:

Save each sheet as a separate webpage with each filename designated by cell 2b on the relevant sheet

I'm sure there's some really obvious solution I'm missing and I'd appreciate any comments.

Capt'n Midnight

Slightly OT

openoffice saves all sheets into ONE page with hyperlinks to each sheet

The html it produces is SO much cleaner than excel that it's worth looking at using it to convert excel files to html for that reason alone.

Seth Brundle

http://support.microsoft.com/kb/q168561/

ActiveWorkbook.SaveAs FileFormat:=xlHtml
ActiveWorkbook.Close

saves it as one web page with tabs to each sheet.

ActiveWorkbook.PublishObjects.Add(SourceType:=xlSourceSheet, _
Filename:="Interactive Sample", _
Sheet:="Sheet1", HtmlType:=xlHtmlCalc, _
Title:="Sample Sheet").Publish

seemingly will create a sheet - you just need to loop this for each shet and replace Sheet:="Sheet1" with the name of the current sheet.

EL_Loco

here's the code for actually naming the htm as the cell B2. still looking for how to loop through each sheet, but this gets you closer to your initial request:

Sub Macro1()

' assigns the cell B2 value and adds .htm for the file name

Dim strname As String
strname = Range("B2").Value + ".htm"

' notice where strname gets used here for the filename
' this is still manual and will affect sheet3 in this example

   ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
         strname, "Sheet3", "" _
        , xlHtmlStatic, "Book1_30703", "").Publish (True)
    ChDir "C:\"
End Sub

Seth Brundle

This loops through each sheet

Dim ws As Worksheet
    Dim strname As String
    For Each ws In ActiveWorkbook.Worksheets
        strname = ws.Name & ".htm"
        ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
         strname, "Sheet3", "" _
        , xlHtmlStatic, "Book1_30703", "").Publish (True)
        ChDir "C:\"
    Next ws

(not tested!)

EL_Loco

kbannon wrote:

This loops through each sheet

         strname, "Sheet3", "" _

I think there where it says sheet3 will mess ya up. I'm very rusty. soz.

just had a thought (below) renaming the hardcoded source with the worksheet name (ws.name)? so the source changes as you cycle through each worksheet?

         strname, ws.Name , "" _

Seth Brundle

Yeah - missed that - replace "sheet3" with ws.name
Shows that I should be in my bed!

jimi_t

Some great advice there thanks, the finish line is in sight

Just in relation to looping it for each sheet, is there a way you could do a global command to save each sheet named "worksheet*" as a webpage where * = wildcard? Maybe that would save code/time?
The sheets are named "sheet1" to about "sheet 194" or so and I think that might work... Maybe :cool:

Seth Brundle

did the code supplied save each sheet as a separate page?
Then create an add in which will be available under the tools menu - see http://www.fontstuff.com/vba/vbatut03.htm