How to publish as web page with relative path

[Wednesday 13]

Hello. I'm publishing some reports as .htm files, and I faced issue with relative/absolute path of output .htm files.

Let's say, workbook is located in folder "Publish" at desktop. I want to publish .htm files in that same folder where workbook is located. Attached is layout of dialog for that action. Afer I complete it, I get .htm file and respective folder in desired location, and it works fine.

Now, if I move the workbook to another location and try to republish files, Excel republishes them in this same, absolute location, Desktop/Publish, not to a new location, where workbook is now located. If I want to get published files in that same folder, I have to go through publishing dialogs again and edit locations.

I want Excel to republish files in relative destination, in the same folder where workbook is located, whenever workbook is moved to different location, and not in absolute destination chosen during initial publishing.

I refer to republishing when workbook is updated and saved (option "AutoRepublish every time this workbook is saved"), not to new publishing - new publishing works fine when I specify desired location; I just want that location to be relative to workbook, not absolute.

Is it possible?

Thanks in advance!

 

[John_w]

See if this code works for you. The code must be placed in the ThisWorkbook module so that it runs when the workbook is opened. The code simply changes the folder path of the workbook's first Publish settings to the folder that the workbook resides in. Therefore if you move the workbook to another folder, the .htm file will be saved in the same folder.

Private Sub Workbook_Open()

    Dim po As PublishObject
    Dim p As Long
   
    Set po = ThisWorkbook.PublishObjects(1)
    p = InStrRev(po.Filename, "\")
    po.Filename = ThisWorkbook.Path & Mid(po.Filename, p)
    MsgBox "Current Publish file name is " & po.Filename

End Sub

The workbook must be saved as a .xlsm or .xlsb file because it contains VBA code. The MsgBox line confirms the new setting and can be deleted if not needed.

 

[Wednesday 13]

John, thank you for fast reply! I have tried this solution, and it works, but not completely.

I did not mention that my workbook has about 20 sheets/ranges for publishing. When I apply your code, it works properly only for first published sheet, it gets exported to .htm in desired location, whereas all remaining sheets for publishing generate the same error as before, "Excel cannot access", and old path to workbook.

The message box you emphasized gives message "Current Publish file name is C:\new-path-here\publish1.htm", where publish1.htm is the name of that first file which gets exported properly. I tried to play with code to exclude "po.Filename", so it applies to entire folder, not to specific file, but it did not work.

I tried saving workbook both as .xlsm and .xlsb, it's the same in both cases.

Can this code be altered so to update publishing path of all files, please?

 

[John_w]

As you have multiple publish objects we need to loop through them and change the filename property to the current folder.

Private Sub Workbook_Open()

    Dim po As PublishObject
    Dim p As Long, n As Long
   
    n = 0
    For Each po In ThisWorkbook.PublishObjects
        With po
            p = InStrRev(.Filename, "\")
            .Filename = ThisWorkbook.Path & Mid(.Filename, p)
            n = n + 1
            MsgBox "PublishObject index = " & n & vbCrLf & _
                   "SourceType = " & CvtSourceType(.SourceType) & vbCrLf & _
                   IIf(.SourceType = xlSourceRange, "Range = '" & .Sheet & "'!" & .Source, IIf(.SourceType = xlSourceSheet, "Sheet = '" & .Sheet & "'", IIf(.SourceType = xlSourceWorkbook, "", "Other - NOT CODED"))) & vbCrLf & _
                   "Filename = " & .Filename
        End With
    Next

End Sub


Private Function CvtSourceType(st As XlSourceType) As String
    Select Case st
        Case XlSourceType.xlSourceRange: CvtSourceType = "Range"
        Case XlSourceType.xlSourceSheet: CvtSourceType = "Sheet"
        Case XlSourceType.xlSourceWorkbook:  CvtSourceType = "Workbook"
        Case Else: CvtSourceType = "Other - NOT CODED"
    End Select
End Function

Again, the MsgBox and supporting function is informational and not needed.

 

[Wednesday 13]

Now, that works like a charm! Thank you very much for help!