Exporting multiple sheets to multiple htm files?

[rehsurq]

I have a workbook that has 32 sheets in it. I would like to export each
sheet into its own htm file. Is there a way to do this automatically so
I don't have to go through and export each sheet individually? I do NOT
want to export it as a multi-sheet htm page.

Thanks...
J

 

[Dave Peterson]

How about this:

Option Explicit
Sub testme()
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
wks.Copy 'to a new workbook
With ActiveSheet
.Parent.SaveAs Filename:="C:\temp\" & .Name & ".htm", _
FileFormat:=xlHtml
.Parent.Close savechanges:=False
End With
Next wks
End Sub

I used the worksheet name and save into a C:\temp folder. Change these to match
your situation.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[rehsurq]

Hey that works great. Many many thanks to you...

BTW, is there something I can add to this macro to prevent Excel from
including the document properties (name/company, etc) in the files it
outputs? I really don't want that info in there.

Thank you very much again.
J

 

[rehsurq]

Hey that works great. Many many thanks to you...

BTW, is there something I can add to this macro to prevent Excel from
including the document properties (name/company, etc) in the files it
outputs? I really don't want that info in there.

Thank you very much again.
J

 

[Dave Peterson]

Etc is a big category.

You can look at builtindocumentproperties in VBA's help to see all the ones that
can be accessed (not all are used).

Option Explicit
Sub testme()
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
wks.Copy 'to a new workbook
With ActiveSheet
.Parent.BuiltinDocumentProperties("Author").Value = ""
.Parent.BuiltinDocumentProperties("Company").Value = ""
.Parent.SaveAs Filename:="C:\temp\" & .Name & ".htm", _
FileFormat:=xlHtml
.Parent.Close savechanges:=False
End With
Next wks
End Sub

If you look at File|Properties, you can see if you want/need to include more,
too.