VB Code to do this....

[Guest]

I get periodic Excel files with worksheets.. one for each department for
instance.

What I'd like to do is write some code that will create a separate HTML file
for each worksheet. Best scenario would be to name each html file as the
worksheet name.

Example: file1.xls
worksheet1
worksheet2
worksheet3
Run Code which will produce:
worksheet1.html
worksheet2.html
worksheet3.html

Any ideas or suggestions? Would be greatly appreciated!

 

[Gord Dibben]

Jim

Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & w.Name, _
FileFormat:=xlHtml
ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP

 

[Dave Peterson]

Gord's code worked ok for me.

Any chance you have some, er, junk on that line (from copying and pasting from
the newsgroup post)?

try typing in
w.copy
in that same location to see if that works.

====
A couple of more thoughts...

Do you have any hidden worksheets?
Is the workbook protected?

 

[Guest]

There were some hidden worksheets...
A co worker of mine and me sat down and hammered this out... worked pretty
well.
We wrote this VB Macro in a new file..
=========
Sub Burst()

Dim w As Worksheet

Workbooks.Open Filename:="c:\temp\alldepts.xls" 'default filename
For Each w In Sheets()
If w.Visible Then
w.Copy
Workbooks(Workbooks.Count).SaveAs "c:\temp\" & w.Name
Workbooks(w.Name & ".xls").Close
Else
'do nothing
End If
Next w
Workbooks.Close

End Sub

=========

Thanks for your ideas and help !!!

 

[Dave Peterson]

I thought you wanted HTML files?

There were some hidden worksheets...
A co worker of mine and me sat down and hammered this out... worked pretty
well.
We wrote this VB Macro in a new file..
=========
Sub Burst()

Dim w As Worksheet

Workbooks.Open Filename:="c:\temp\alldepts.xls" 'default filename
For Each w In Sheets()
If w.Visible Then
w.Copy
Workbooks(Workbooks.Count).SaveAs "c:\temp\" & w.Name
Workbooks(w.Name & ".xls").Close
Else
'do nothing
End If
Next w
Workbooks.Close

End Sub

=========

Thanks for your ideas and help !!!