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
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
yo_bee
Doesn't the application prompt you when you open a file that is already one?
ASKER
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
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
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.
ASKER
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
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.
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.
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 ?
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 ?
ASKER
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
try using wmi to pull information
https://blogs.technet.microsoft.com/heyscriptingguy/2005/02/16/how-can-i-list-open-sessions-and-open-files-on-a-computer/
WHy not incorporate into the VBA a check to validate it has sole access to the workbook.
Installing a sysinternals
https://social.technet.microsoft.com/Forums/windowsserver/en-US/67e94a6a-47e8-420a-b28a-759d876fb15e/how-to-list-open-files-using-powershell?forum=winserverpowershell
https://blogs.technet.microsoft.com/heyscriptingguy/2005/02/16/how-can-i-list-open-sessions-and-open-files-on-a-computer/
WHy not incorporate into the VBA a check to validate it has sole access to the workbook.
Installing a sysinternals
https://social.technet.microsoft.com/Forums/windowsserver/en-US/67e94a6a-47e8-420a-b28a-759d876fb15e/how-to-list-open-files-using-powershell?forum=winserverpowershell
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Very good. Thank you!
Extra credit question: does standard VB have anything remotely equivalent to processes?
Extra credit question: does standard VB have anything remotely equivalent to processes?
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