Save Excel worksheet as html

sandramac
sandramac used Ask the Experts™
on
Hello, trying to build a vba code to save sheet 14 range A1:P51 as a .htm file, using the value of sheet5.z71 as the filename.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
'sheet5.z71' won't be recognized as an 'htm' file,  maybe 'sheet5.z71.htm'.
SANTABABYSoftware Professional

Commented:
Please try the following subroutine.
(Note that : Exception conditions such as sheet 5 or 14 do not exist, are not handled. Addition code can be added to handle those conditions.)
Sub SaveAsHtml()

SaveDir = "" 'Target directory, end with a \
SaveFileName = SaveDir & Worksheets(5).Range("$Z$71")
WSname = Worksheets(14).Name
Src = "$A$1:$P$51"

    With ActiveWorkbook.PublishObjects.Add(xlSourceRange, SaveFileName, WSname, Src, xlHtmlStatic, WSname & "_DIV", "")
        .Publish (True)
        .AutoRepublish = False
    End With
End Sub

Open in new window

SANTABABYSoftware Professional

Commented:
Assumed that you wanted to use the value in Cell Z71 of sheet5 as the name of the file to be generated. If that's not what you meant, please let me know.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Author

Commented:
yes sheet 5 cell Z71 the value will be a text like KTUY  so it would save as KTUY.htm
SANTABABYSoftware Professional

Commented:
Please try to run the code above and please confirm whether it works for you or if you need any assistance to make it work for you.

Author

Commented:
Hello, it is working, just a quick question it saves it as a .mht, can it be save as .htm
Software Professional
Commented:
First try this:
In the VBA subroutine, replace the line
SaveFileName = SaveDir & Worksheets(5).Range("$Z$71")

Open in new window

with
SaveFileName = SaveDir & Worksheets(5).Range("$Z$71") & ".htm"

Open in new window



(The above worked for me in EXCEL 2007) If it does not work for you, please try the following
In Line #8, please replace
xlHtmlStatic

Open in new window

with
xlHtml

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial