VB Code to do this....
 

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!

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

On Fri, 1 Apr 2005 14:51:02 -0800, "Jim in Montana"
wrote:

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,
thanks for the help, however, it didn't work completely.

First I got a debug error on the line w.Copy.
So, I commented that line out.
After that, it only created 1 file (report.htm) with all the worksheets,
kind of a dynamic Excel file.

Any other suggestions... I really need this to work! Thanks again for your
efforts.

Jim

"Gord Dibben" wrote:

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

On Fri, 1 Apr 2005 14:51:02 -0800, "Jim in Montana"
wrote:

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'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?


Jim in Montana wrote:

Gord,
thanks for the help, however, it didn't work completely.

First I got a debug error on the line w.Copy.
So, I commented that line out.
After that, it only created 1 file (report.htm) with all the worksheets,
kind of a dynamic Excel file.

Any other suggestions... I really need this to work! Thanks again for your
efforts.

Jim

"Gord Dibben" wrote:

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

On Fri, 1 Apr 2005 14:51:02 -0800, "Jim in Montana"
wrote:

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!



--

Dave Peterson

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 !!!

I thought you wanted HTML files?

Jim in Montana wrote:

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