How to publish as web page with relative path

Wednesday 13

New Member
Joined
Sep 24, 2010
Messages
4
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,957
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.

Code:
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

New Member
Joined
Sep 24, 2010
Messages
4
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?
 
Last edited:

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,957
As you have multiple publish objects we need to loop through them and change the filename property to the current folder.
Code:
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.
 

Forum statistics

Threads
1,078,442
Messages
5,340,308
Members
399,366
Latest member
ahmed elsaid

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top