Code runs in break mode, but not otherwise!

[ExTeacher]

Hi all,

I have a code that is replacing hyperlinks to open word documents on a networked document as everyone has a unique identifier for the initial drive on the network. For example, I am on the H drive, but the same document for a colleague is on the N drive. I've created a range in excel called filepath, using the =cell("filename") code so each person will have their drive registered to the document.

It runs in break mode, but I receive the 4198 error when I try and run it as normal. I have about 80 documents I need to hyperlink to a master - if anyone has any alternative methods please let me know!

The code is:

 Sub label1_Click()
    Dim objWord As Object
    Dim objDoc 

    
    Set objWord = CreateObject("Word.Application")
    
    x = Range("filepath") & "\Stage 3.7 Coordinator Item Check.docx"
    
    objWord.Documents.Open (x)
    
    objWord.Visible = True
    
End Sub

Thanks!

 

[RoryA]

Welcome to the forum.

It doesn't look like you need to control Word after opening the file, so perhaps try this. Add the following to the top of the module:

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
                                      (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
                                       ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Const SW_SHOW            As Long = 5&
Sub OpenFile(strFilePath As String)
   ShellExecute Application.hWnd, "Open", strFilePath, 0&, 0&, SW_SHOW
End Sub

then change your click code to this:

Sub label1_Click()

    OpenFile Range("filepath") & "\Stage 3.7 Coordinator Item Check.docx"
    
End Sub

 

[ExTeacher]

Works perfectly. Thank you. Would it be possible to explain what the code is doing? I like knowing these things so next time I can try and figure it out for myself

Thanks again!

 

[RoryA]

The ShellExecute API is a hook into the operating system functions. This is essentially the code equivalent of you finding the docx file in Explorer and double-clicking it.

 

[ExTeacher]

Brilliant, thank you.

 

[GTO]

Rory has already provided an alternative, so just FWIW...

objWord.Documents.Open (x)

IMO, there should not be parenthesis around the x, you are not returning anything.

Mark

 

[ExTeacher]

So just use objWord.Documents.Open x ?

Thanks!

 

[GTO]

Yes

 

[ExTeacher]

Brilliant. Thank you very much!