Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Jim in Montana
 
Posts: n/a
Default 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!

  #2   Report Post  
Gord Dibben
 
Posts: n/a
Default

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!


  #3   Report Post  
Jim in Montana
 
Posts: n/a
Default

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!



  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #5   Report Post  
Jim in Montana
 
Posts: n/a
Default

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




  #6   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Make Change Case in Excel a format rather than formula Kevin Excel Worksheet Functions 1 March 18th 05 09:53 PM
Error trapped only while stepping through the code - Not triggered when run Jeff Excel Discussion (Misc queries) 7 March 7th 05 07:29 PM
Opening a file with code without a set file name jenkinspat Excel Discussion (Misc queries) 1 March 4th 05 11:50 AM
Opening a file with code without a set file name jenkinspat Excel Discussion (Misc queries) 1 March 3rd 05 04:40 PM
Error trapped only while stepping through the code - Not triggered when run Jeff Excel Discussion (Misc queries) 0 February 28th 05 07:26 PM


All times are GMT +1. The time now is 02:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"