Excel Macro Help

The Muppet

Hi

I want to convert all the worksheets in a workbook to single html files. The macro below converts them to xls files, could anyone advise me on how to alter it to save as html.

Sub ExportToWorkbooks()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim NewBook As Workbook, OldBook As Workbook, sh As Worksheet
Set OldBook = ActiveWorkbook

For Each sh In OldBook.Worksheets
If sh.Visible = xlSheetVisible Then
sh.Copy
Set NewBook = Workbooks.Add

'NewBook.Name = sh.Name & " VALUES.xls"
NewBook.Sheets(1).Paste
NewBook.SaveAs Filename:=OldBook.Path & "\" & sh.Name & ".VALUES.xls"
NewBook.Close
End If
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


Thanks

Evil Phil

ActiveWorkbook.SaveAs Filename:="C:\Users\Book1.htm",  FileFormat:=xlHtml

Something like that should do it, you may have a different version of excel though.

The Muppet

I'm using excel 2003

I'll try that

Thanks for your help,

The Muppet

I modified the filename part of the original macro with the code above but it did not have the desired effect. It did create the html files but there was no data in them , just the macro code.

Is it possible to combine both macros to create a single macro that will create a html file for each worksheet in my workbook? I am using excel 2003.

Here is the modified code I used.

Sub ExportToWorkbooks()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim NewBook As Workbook, OldBook As Workbook, sh As Worksheet
Set OldBook = ActiveWorkbook

For Each sh In OldBook.Worksheets
If sh.Visible = xlSheetVisible Then
sh.Copy
Set NewBook = Workbooks.Add

'NewBook.Name = sh.Name & " VALUES.xls"
NewBook.Sheets(1).Paste
NewBook.SaveAs Filename:=OldBook.Path & "\" & sh.Name & ".VALUES.html", FileFormat:=xlHtml
NewBook.Close
End If
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub