vba in excel to List all word files that are currently open on my PC

I am making an excel project to manage the creation and filing of a set of word documents created from the same .dotx template. I dont have experience with vba for MS Word. The requisit MS References are open to use the Word Object model.

Before moving or copying a word document using scripting.FileSystemObject, I wish to identify if the file in question is OPEN, and close it before move/copy.

I thought that one documents collection would cover all open Word application windows, and that this code would report all open docs:
Sub tstDocsOpen()
    Dim aDoc As Word.Document
    Dim aName As String
    aName = ""
    For Each aDoc In Word.Documents
        aName = aName & aDoc.Name & vbCr
    Next aDoc
    MsgBox aName
End Sub

However, when I open two docs in separate windows, sometimes one is reported (twice), and sometimes both reported. Changing which windows file has most recently been 'on top' made no difference.

Please can you help?
Kelvin4
Kelvin4Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
This sounds similar to a problem I was having finding all of the open instances of Excel for an application I wrote a while back.

After searching forever on the Internet, I finally found some code that uses  FindWindowEX to return the handle to the window that is being searched for.

I don't have access to that application at the moment, but you might want to search on that and Word to see if you can find anything that has previously been posted.

Posted from my iPad
tilsantCommented:
This is a piece I have been using for quite a long time for checking open files:
Function IsFileOpen(filename As String)

    Dim iFilenum As Long
    Dim iErr As Long

    On Error Resume Next
    iFilenum = FreeFile()
    Open filename For Input Lock Read As #iFilenum
    Close iFilenum
    iErr = Err
    On Error GoTo 0

    Select Case iErr
    Case 0:    IsFileOpen = False
    Case 70:   IsFileOpen = True
    Case Else: 'Error iErr
    End Select
    
End Function

Open in new window


You can use the above function provided you have the complete name of the file you want to check. This works irrespective of the file type, be it Word, pdf, Excel or any other.


- Tilsant

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProfessorJimJamMicrosoft Excel ExpertCommented:
the above captioned title refers to list all name and path of all open word documents in excel sheet,
but then again, your explanation to the question refers that you want to check if the specific file is open before move and copy.

so my question is,  do you need to list all of open word documents in excel?  

or do you want to simply to show in message boxes all of the files currently open?  

or do you want to check the files before delete if it is currently open?

can you please elaborate?
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Roy CoxGroup Finance ManagerCommented:
Try this

Option Explicit


Sub Word_List()
    Dim oApp As Object
    Dim intCount
    Set oApp = GetApplication("Word.Application")
    If oApp Is Nothing Then
        MsgBox "No documents open!", vbCritical, "Quitting"
        Exit Sub
    End If
    With oApp
        For intCount = 1 To .Documents.Count
            Cells(intCount, 1).Value = .Documents(intCount).Name
            Cells(intCount, 2).Value = .Documents(.Documents(intCount)).Path
        Next intCount
    End With
    Set oApp = Nothing
End Sub

Private Function GetApplication(ByVal AppClass As String) As Object
    Const vbErr_AppNotRun = 429
    On Error Resume Next
    Set GetApplication = GetObject(Class:=AppClass)
    If Err.Number = vbErr_AppNotRun _
        Then Set GetApplication = CreateObject(AppClass)
    On Error GoTo 0
End Function

Open in new window

Kelvin4Author Commented:
Hi Tilsant:
Many thanks for nice code.
Kelvin
excelismagicCommented:
Roy Cox,

thanks for posting the code, i prefer that option, is much better.

but it would be nice to mention the credit to the original author of the code , i believe you copied the code from http://www.mrexcel.com/forum/excel-questions/337402-visual-basic-applications-list-name-path-all-open-documents.html

don't tell me you wrote that code :-)
DrTribosCommented:
Also FWIW, WRT to this solution, if a document is showing as a preview (e.g. in Windows File Explorer Preview Pane) then the document count might be incorrect...  ('might' = 'will'  - if the file explorer somehow gets focus while code is running...)

Can't see this being an issue with the accepted solution, nice :-D
Kelvin4Author Commented:
thanks!
Roy CoxGroup Finance ManagerCommented:
It was code that I used in a project, at work, not sure where it came from.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Word

From novice to tech pro — start learning today.