Opening Word document via Excel VBA not working

[richh]

I wrote a piece a code last week that was working fine this morning but now doesn't work. No idea why. upon selecting an item from a listbox, the onClick function of that control sends the value selected to this founction. It should then find the topic selected in the list of topics, then create a wird application, open the document, pull a small bit of data out, close the doc, then quit the app. Now it gets hung up when it attempts to open the file.

Public Function PopulateSelectedPreview(topicSelected As String)
    Dim ws      As Worksheet
    Dim lRow    As Long
    Dim pathStr As String
    Dim wordapp As Object, objNewDoc As Object ''Word.Document
        
    Set ws = Application.ThisWorkbook.Worksheets("ResponseData")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    
    For i = 2 To lRow
        If ws.Cells(i, 4) = topicSelected Then
            pathStr = ws.Cells(i, 7)
            Set wordapp = CreateObject("Word.Application")
            wordapp.Documents.Open (pathStr) '<--------------error here
            'wordapp.Visible = False
                        
            UserForm1.selectedPreview.Text = wordapp.activedocument.selectcontentcontrolsbytitle("Response Body").Item(1).Range.Text
            wordapp.Documents.Close
            wordapp.Quit
                        
            Exit For
        End If
    Next i
    
    Set wordapp = Nothing
    Set ws = Nothing
End Function

 

[6StringJazzer]

You said it gets hung up, and you said there is an error. Can you be specific about what happens on that line of code? Do you get an error message? What does it say?

What is the value of pathStr at the point where the error occurs? Have you verified that it is a valid path, and the file exists?

 

[richh]

I think I figured it out, but not sure how to address it. I had ran the code several times and the document was not closed. Icouldn't figure out out - I guess opening a word app instance happens in the background..? I couldn't see the file so I assumed it wasn't opening.

I had to force close Excel to get it to stop trying to open the document and in turn, had started up several instances of Word in the background. After forcing them all closed, I tried to open the doc directly and Word (Office? Windows?) had flagged it as having encountered an error the last time it was opened, which had prevented it from being opened normally. I then had to re-save the file to remove that flag.

Now I have to figure out how to check to see if the document is open before attempting to open it. And thus, another 50 lines of code need to be developed to address one error.... wa wa waaaa

 

[6StringJazzer]

Try to replace this code

            Set wordapp = CreateObject("Word.Application")

with this code

On Error Resume Next
Set wordapp = GetObject(, "Word.Application")
If wordapp Is Nothing Then Set wordapp = CreateObject("Word.Application")
On Error GoTo 0

This will use an existing instance of Word if one is already open, which I think is probably the issue. Also, if you do this, consider if you really want to Quit an instance that was already open when you started.