Programmatically seeing what files are open at any given time, via standard VBA routines

I have a VBA program in Windows that uses declared functions to retrieve which files are open, as shown in the taskbar at the bottom of my screen, next to the Start button.  I would like the VB equivalent of that task bar, so that code has knowledge of all open processes.  

Declared functions are a hassle to maintain; and, as I migrate my VBA code to VB.Net, some of these stop working.  I won't bother reproducing the code block here, because I want to get away from the use of declared functions and subroutines, in general, and replace them with industry-standard VB routines.  In general, a declared function looks as follows; and these are buggy, have little recourse for debugging, and don't migrate easily from VBA/VB6 to VB.Net:

Private Declare Function <FuncName> Lib "<Libname>" Alias "<AliasName>" ()

The point of the exercise is to determine whether an existing Word or Excel file is already open or not.  Based on the status of what is already open, I can take one of the three options:

1) Open a new Excel or Word application object and then open the desired file there.
2) Note that an Excel or Word application is open, and open the desired file from that application., or
3) Note that the Excel or Word file is already open, and simply return a pointer to that existing file.

The advantage of having a system such as this is that I would never run into the problem of opening the same document two or more times, using different instances of the same Excel or Word application.  If I do this, I run the risk of not knowing which version is the state of the art and which version to save and which to toss away.  Knowing what app;lications are open from the start would give me the knowledge base to choose the right course programmatically.  

Thanks, in advance, for your assistance.  ~ Peter Ferber
PeterFrbWeb development, Java scripting, Python TrainingAsked:
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.

yo_beeDirector of Information TechnologyCommented:
Doesn't the application prompt you when you open a file that is already one?
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
I don't think you understand the question, so I'll use an example.  Imagine you have an Excel application with three workbooks open in it.  Using the Workbooks object, within the Excel.Application object, you can validate all the file names associated with that Excel.Application object.  

I am asking for is that same type of functionality from the level of Windows.  Imagine that I have 2 Access applications, 3 Explorer windows, 3 Adobe Readers, iTunes, 5 Notepads, and Norton Antivirus running simultaneously.  Manually, I can look at the task bar and see the icons for each of those applications and derive a complete list of everything that is running on my machine at any given moment.  I would like to provide VB with an internal catalog that sees what I do when I manually look.  Hope that clarifies.  ~ Peter Ferber
yo_beeDirector of Information TechnologyCommented:
I see.  You want an open application count.  I misunderstood your question and I am sorry I do not have any insight on a solution.
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
No worries.  This is something of a tough nut, although my gut tells me it can be done.  It's not like the information is proprietary, since the Taskbar shows it publicly.  I have the strong belief that some industry-standard languaging is available to make VB aware of all the cast members currently on stage.  ~ Peter Ferber
arnoldCommented:
I think you have it as part of office, that creates a lock file ~filename format.
You will be alerted that the file is already open, the only distinction is when DFS is deployed in the environment where the lock file is precluded from replicating to the other dfs target servers while the document is open because the lock file is itself in use as a place holder.

You should look at whether your vba application checks on the alert of being in use, but ignores it.

Since this is your vba, look at using a flag/method that accounts for vba opening the file once, including using lock files.
Fabrice LambertConsultingCommented:
I wouldn't rely on ~filename as it isn't reliable enough (with file in a read-only directory, the lock is created somewhere else).

Beside using windows API, I don't think pure VBA can handle what you are looking for.

If there is a single instance of word / excel /powerpoint ect ... already open, no problem, you can get the instances (with the getObject function) and count how many documents are open, but what if there are multiple instances already of the same application ?
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
Exactly.  Fabrice is precisely on track and articulating the problems I encounter.  Solving for this is surprisingly difficult, and I am looking to vuew the same inventory I see in the taskbar.  Perhaps the question should be: can I tap into the same resource that Windows uses to fashion its taskbar?  ~ Peter
Chris WatsonSoftware DeveloperCommented:
If you're porting to VB.NET, have a look at the System.Diagnostics.Process class.

For example:

Dim processes As Process() = Process.GetProcesses()
For Each p As Process In processes
    Console.WriteLine(p.MainWindowTitle)
Next

Open in new window

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
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
Very good.  Thank you!  

Extra credit question: does standard VB have anything remotely equivalent to processes?
Randy BristowSenior Systems AnalystCommented:
https://stackoverflow.com/questions/26303173/how-can-i-kill-task-manager-processes-through-vba-code

Dim oServ As Object
Dim cProc As Variant
Dim oProc As Object

Set oServ = GetObject("winmgmts:")
Set cProc = oServ.ExecQuery("Select * from Win32_Process")

For Each oProc In cProc

    'Rename EXCEL.EXE in the line below with the process that you need to Terminate. 
    'NOTE: It is 'case sensitive

    If oProc.Name = "EXCEL.EXE" Then
      MsgBox "KILL"   ' used to display a message for testing pur
      errReturnCode = oProc.Terminate()
    End If

Next

Open in new window

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
Visual Basic.NET

From novice to tech pro — start learning today.