CODEWITHSUNDEEP
htmlvbaexcelexport
Rootinshell
Rootinshell

Reputation: 1

Save all sheets in workbook as HTML files

I've been using the following script to convert an .xlsx file to HTML web page. Now I need to save all the sheets in this workbook as HTML and this function doesn't work.

Const xlHtml = 44
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open "C:\Workbook\newitems.xlsx"
objExcel.ActiveWorkbook.SaveAs "C:\Workbook\newitems.html", xlHtml
objExcel.Quit

Thanks for your help

Upvotes: 0

Views: 489

Answers (1)

laylarenee
laylarenee

Reputation: 3284

You will need to iterate each sheet in the file and save an output file for it:

Const xlHtml = 44

Sub SaveAllTabsAsHtmlFiles()

    Set objExcel = CreateObject("Excel.Application")
    ' open that source file
    objExcel.Workbooks.Open "C:\Workbook\newitems.xlsx"
    ' iterate all sheets in source file
    For i = 1 To objExcel.ActiveWorkbook.Worksheets.Count
        ' select each sheet
        objExcel.ActiveWorkbook.Worksheets(i).Select
        ' get name of current sheet
        sheetname = objExcel.ActiveWorkbook.Worksheets(i).Name
        ' name of output file (I added underscore + sheetname)
        outputfile = "C:\Workbook\newitems_" & sheetname & ".html"
        ' save current sheet as html
        objExcel.ActiveWorkbook.SaveAs Filename:=outputfile, FileFormat:=xlHtml
    Next i
    'done
    objExcel.Quit
End Sub

Upvotes: 1

Related Questions