Noob question for a save as html script

  • Hi there,

    As stated, I'm a noob! I have this code below to save the current Excel sheet as an html file, which works fine for selected sheet or workbook. My question is, and I'm sure it's a simple one for those more knowledgeable: how do I get rid of the two full path references, and tell it to save the html page in the same, current location, with the same file name as the open workbook please? Also, need to lose the reference to DB10 further down the code, as this was specific to the one document I had tried it on so far. It works great when I save this as a macro to run on other workbooks, but of course always calls the output html file DB10.

    Here it is:

    Sub SaveAsHtmlAndClose()
    ' SaveAsHtmlAndClose Macro
    ' Save as an html file, and close, with option to always update html file upon saving main xlsx file.

    With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
    "S:\RED\Facilities\Power Layout and Distribution Boards\Redhill Boards\Computer Room 1\DB10.htm" _
    , "Sheet1", "", xlHtmlStatic, "DB10_19990", "")
    .Publish (True)
    .AutoRepublish = True
    End With
    ChDir _
    "S:\RED\Facilities\Power Layout and Distribution Boards\Redhill Boards\Computer Room 1"
    End Sub

    Many thanks in advance guys. :)

  • Try this:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!