Excel to Word, "FileLocked" with 2 open Word documents - error

[eugene81]

Hi all,

I've been looking for a solution to my problem for way too long so I hope someone here can help.

From an Excel workbook, I run a macro that will open/activate an existing Word doc, copy some info from the spreadsheet onto this word doc, then, if the user requires it, another existing word doc should open/activate so some more info can be copy-pasted into that 2nd word doc. When neither word doc is open or when only 1 is open, everything works fine, but once they're both open, it doesn't work (see below for exact error message and when it appears).

I am using 2x "filelocked" type of function I found in an excel forum, 1 for each of the word documents that need to be opened or activated, to avoid having to deal with the read-only issue:

Function filelocked(path As String) As Boolean
On Error Resume Next
filenum = FreeFile
Open path For Binary Access Read Write Lock Read Write As #filenum
Close #filenum
If Err.Number <> 0 Then
    filelocked = True
    Err.Clear
End If
End Function

Function filelocked2(path2 As String) As Boolean
On Error Resume Next
filnumber = FreeFile
Open path2 For Binary Access Read Write Lock Read Write As #filnumber
Close #filnumber
If Err.Number <> 0 Then
filelocked2 = True
Err.Clear
End If
End Function

I started out using #1 instead of #filenum/#filnumber, but then I thought maybe, seeing as I had 2 documents, using FreeFile would fix the problem. I also tried using #1 for the 1st function and #2 for the 2nd function, but didn't change anything.

The filelocked functions are called during these 2 portions of my code:

Calling filelocked

If filelocked(path) Then
    Set wordapp = GetObject(, "Word.Application")
    wordapp.Documents(path).Activate
    Set doc1 = wordapp.ActiveDocument
Else
    Set wordapp = CreateObject("word.application")
    wordapp.Visible = True
    Set doc1 = wordapp.Documents.Open(path)
End If

Calling filelocked2

If filelocked2(path2) Then
        Set wordapp = GetObject(, "word.application")
        wordapp.Documents(path2).Activate 'error message appears here
        Set doc2 = wordapp.ActiveDocument
Else
        Set wordapp = CreateObject("word.application")
        wordapp.Visible = True
        Set doc2 = wordapp.Documents.Open(path2)
End If

• If neither doc is open, the entire macro works fine,
• if doc1 is already open but not doc2, the entire macro works fine,
• if both docs are open, then everything works fine with doc1 (it activates, then whatever needs to be copy-pasted from Excel, is and the user is prompted about whether doc2 is required), but when it gets to "wordapp.Documents(path2).Activate" (see code above), I get the following error:
  Run-time error '4160': Bad file name.

It seems that this part of the code is the only real problem given that under certain circumstances, the macro runs all the way.

If anyone can shed some light on this, that'd be great!

Thank you in advance.

 

[frabulator]

Are you running the second code before the first finishes/closes? Also, have you put a break in the code to verify that the path2 is still the correct path and the string hasn't been somehow altered?

 

[eugene81]

This is all in the one macro. First, everything to do with doc1 happens: the macro calls the 1st function "filelocked", opens or activates the 1st word doc, then copies and pastes the relevant info from Excel onto the 1st word doc; then the user is asked if the 2nd word doc is needed. If it isn't, the Sub ends, if it is needed, then the macro calls the 2nd function "filelocked2", etc. So the "2nd code" can't run before the 1st one, if that's what you meant.

Path2 is declared once the 1st part of the macro has been executed, meaning when the user has been prompted about needing doc2 (and they've clicked yes). Don't know if that changes anything.
So it looks like this:

'User is prompted about needing doc2
result2 = MsgBox("Do you need Doc2?", vbYesNo, "Document 2")
If result2 = vbNo Then
Exit Sub
    ElseIf result2 = vbYes Then

    Dim doc2 As Word.Document
    Dim path2 As String
    path2 = fso.GetDriveName(filepath) & "\Macro Testing\doc2.docm"

'the 2nd function is called
If filelocked2(path2) Then
        Set wordapp = GetObject(, "word.application")
        wordapp.Documents(path2).Activate 'error message appears here
        Set doc2 = wordapp.ActiveDocument
Else
        Set wordapp = CreateObject("word.application")
        wordapp.Visible = True
        Set doc2 = wordapp.Documents.Open(path2)
End If

I did use debug.print in a few different spots throughout the code, when I was trying to understand what was wrong, to see if the various paths and file names were correct, they were. Obviously, I can't check this her "wordapp.Documents(path2).Activate" given that I get the error message. But, if doc2 is not already open, then in this line "Set doc2 = wordapp.Documents.Open(path2)", path2 is correct.

 

[frabulator]

I think I found your issue by testing it out on my own docs.

Please see the following codes:

Function filelocked(path As String) As Boolean
    'On Error Resume Next
    On Error GoTo AlreadyOpened
    filenum = FreeFile
    Open path For Binary Access Read Write Lock Read Write As #filenum 'if this parts errors out then it is already open
    Close #filenum
    filelocked = False
    Exit Function
AlreadyOpened:
    On Error GoTo 0
    filelocked = True
End Function



Sub LockyLocky()

    Dim path As String
    Dim p As String
   
    p = "C:\Users\GRAY\Desktop\New folder"
   
    path = p & "\Doc.docx"

    CheckAndDoStuff path
   
    path = p & "\Doc - Copy.docx"

    CheckAndDoStuff path

End Sub


Sub CheckAndDoStuff(path As String)
   
    Set wordapp = GetObject(, "Word.Application")
   
    If filelocked(path) = True Then
        'this means that the file is already opened
        wordapp.Documents(path).Activate
        Set doc1 = wordapp.ActiveDocument
       
        'do stuff here
       
        Set doc1 = Nothing
       
    Else
        'this means the file is not opened, so it needs to be
        Set wordapp = CreateObject("word.application")
        wordapp.Visible = True
        Set doc1 = wordapp.Documents.Open(path)
       
        'do stuff here
       
        doc1.Close 'close it when done
        Set doc1 = Nothing
        wordapp.Quit
       
    End If
   
    Set wordapp = Nothing

End Sub

The first was the error handling in your original function. Using 'on error resume next' is fine, but where you were using it you were skipping over the check to see if the file was already opened. Basically, every time I ran it, it was saying that the file was always opened.

Second, I think the 'If filelocked(path) Then' was reversed. Your original code had the 'already opened' code running when the 'not yet opened' code should have been.

Third, I fixed the double repeat codes. That is just a personal preference of mine, but I have found that repeats just lead to more headache than its worth.

The fourth thing I noticed was that you were not closing out of your documents when you were done with them, or nulling out your references. Coupling this with the double codes can lead to the issue of double opening books, which is why you were getting the error of 'bad name'. Basically, you were opening a book that was already opened in a new instance of word, and you can't do that.

I hope this has helped. The above code works for me and I have tested it out several times. Run 'LockyLocky' and change the paths as needed.

~Frab

 

[eugene81]

Thank you very much for your help.

I don't want the word documents to close, which is why I had a "check if already open" function, with either the macro opening the documents or activating them. I might run the macro several times in a row, on the same documents (they're templates), and even if I don't, I need to copy-paste their content somewhere else again, so I really don't want the documents to close, and I don't need copies of the documents either. Would I just need to not write the following parts:

 doc1.Close 'close it when done
 Set doc1 = Nothing
 wordapp.Quit
       
End If
   
Set wordapp = Nothing

Also, I'm not sure what you mean by double repeat codes. I do need both word documents open at the same time and I can't have the same code for both, seeing as the paths are different. Am I missing something here? Looking at your code, I think I need to write it all twice to account for the different paths.

I don't understand this either: "Using 'on error resume next' is fine, but where you were using it you were skipping over the check to see if the file was already opened. Basically, every time I ran it, it was saying that the file was always opened". Seeing as my "if then else" was accounting for the doc being opened or not (and it always works for doc1), I don't understand the problem.

 

[frabulator]

I don't want the word documents to close

If you dont need it, then dont include it in. I was just trying to cover all bases with the document. Though, when you are done with the document in question (as in referencing it) you should set the doc to nothing, and the app to nothing. Otherwise you are leaving over values that could result in memory overflow and give you an error.

I'm not sure what you mean by double repeat codes.

The following two are exactly the same thing.

VBA Code:

Function filelocked(path As String) As Boolean
On Error Resume Next
filenum = FreeFile
Open path For Binary Access Read Write Lock Read Write As #filenum
Close #filenum
If Err.Number <> 0 Then
    filelocked = True
    Err.Clear
End If
End Function


VBA Code:

Function filelocked2(path2 As String) As Boolean
On Error Resume Next
filnumber = FreeFile
Open path2 For Binary Access Read Write Lock Read Write As #filnumber
Close #filnumber
If Err.Number <> 0 Then
filelocked2 = True
Err.Clear
End If
End Function

Because they are the same, if you were to edit one, you would need to edit the other. That is a repeat code. If they are exactly the same, then make them into one function that you can call at any time and only edit once. There is no need to have it twice.

The 'double' part of that is your 'filelocked' and 'filelocked2'. Both are identical, so, have them run in their own routine.

I don't understand this either: "Using 'on error resume next' is fine, but where you were using it you were skipping over the check to see if the file was already opened. Basically, every time I ran it, it was saying that the file was always opened". Seeing as my "if then else" was accounting for the doc being opened or not (and it always works for doc1), I don't understand the problem.

I see now. I misread the 'Err.Number' as being something else.

Yes, you are correct, that code would work, but I caution you about using the 'on error resume next'.

'on error resume next' should only be used when you are trying to throw an error and you know exactly what it is going to do.

When you use 'on error resume next', the error throwing doesnt really stop until everything is complete. It is suppose to stop at the end of the function/subroutine that it is called, but I have seen several instances where it continues over due to a bug or overlaping code sequences. Whenever you use 'on error resume next' it is always good practice to throw in 'on error goto 0' after the event in question, which will revert back to its default error handling protocol.