VBA Word : Error 5174 when Documents.Open with filename including pound sign #

Question

In Microsoft Word 2010 VBA

I am getting a runtime error 5174, when trying to open a document which file name includes a pound sign "#", with a relative file path.

Sub openPoundedFilename()
    Dim doc As Object
    ' Both files "C:\Temp\foo_bar.docx" and "C:\Temp\foo#bar.docx" exist

    ' With absolute file paths
    Set doc = Documents.Open(fileName:="C:\Temp\foo_bar.docx") ' Works
    doc.Close
    Set doc = Documents.Open(fileName:="C:\Temp\foo#bar.docx") ' Works
    doc.Close

    ' With relative file paths
    ChDir "C:\Temp"
    Set doc = Documents.Open(fileName:="foo_bar.docx") ' Works
    doc.Close
    Set doc = Documents.Open(fileName:="foo#bar.docx") ' Does not work !!!!
    'Gives runtime error 5174 file not found (C:\Temp\foo)
    doc.Close
End Sub

I did not find any explanation for why the last Documents.Open fails.
It probably has to do with some mismatch regarding the "#" sign used for URL.
(see https://support.microsoft.com/en-us/kb/202261)

Thanks in advance for answers

Edit 17/10/2016 13:37:17
The macro recording generates the following:

Sub Macro1()
'
' Macro1 Macro
'
'
    ChangeFileOpenDirectory "C:\Temp\"
    Documents.Open fileName:="foo#bar.docx", ConfirmConversions:=False, _
        ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
        PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
        WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""
End Sub

This macro doesn't work (gives the same error 5174).

Anything after # is considered as sub-address, so you can try Record Macro to see the generated code. — Slai, Oct 17 '16 at 09:39
seems to work only with the full path `Documents.Open CurDir & "\foo#bar.docx"` — Slai, Oct 17 '16 at 13:34

score 0 · Accepted Answer · edited May 23 '17 at 10:32

To open the file using a relative path you need to URLEncode the filename. There is no built-in support in VBA for doing so (besides in newer Excel versions), but you can use @Tomalak's URLEncode function, which should encode foo#bar.docx as foo%23bar.docx:

ChangeFileOpenDirectory "C:\Temp\"

Dim urlEncodedFilename as String
urlEncodedFilename = URLEncode("foo#bar.docx")
Set doc = Documents.Open(fileName:=urlEncodedFilename)

score 0 · Answer 2 · answered Oct 17 '16 at 14:48

As the issue only occurred with relative path names, a work-around could be used: convert paths to absolute.

Set fs = CreateObject("Scripting.FileSystemObject")
Set doc = Documents.Open(fileName:=fs.GetAbsolutePathName("foo#bar.docx"))

Maybe this work-around doesn't work in all cases, as Documents.Open performs unclear processing with the file name.

VBA Word : Error 5174 when Documents.Open with filename including pound sign #

2 Answers2