Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) | |||
Error trapped only while stepping through the code - Not triggered when run | Excel Discussion (Misc queries) |