1

What I'm aiming at is getting a list of all currently running scripts in order to check if other users aren't running the same VBA macro at the same time (and if yes, then stop the code etc., similar to what the OP of the below question wanted). This would be for a shared workbook (I learned it's not designed for this type of work, but I need to try it).

https://stackoverflow.com/a/36116091/5947935

I've been trying to make the code in the above answer work in VBA, but it seems it's a vbs thing and I would like to avoid that.

I'm not an expert to say the least, so I'm having trouble understanding how to get this to work in Excel VBA. I don't even know if it's possible at all.

I've found this as well: VBA Getting program names and task ID of running processes and it works fine but it only lists the running processes.

I've no idea however how to merge the two... or even if the WMI is the correct way to go.

I'd appreciate any sort of help.

Community
  • 1
  • 1
cloudberry
  • 11
  • 2
  • I may misunderstand, but to me they both appear to check for scripts running on ONE workstation. If two different users open a shared workbook, the vba will run on THEIR machines. Would be simple if this was Access,but... I assume you are running in a client-server environment? If so, maybe you could do one of the following: (a) create a hidden file on the server and when the script starts, open it exclusively (i.e. lock out); have code check if file locked, and if so, wait. Or (b) try to manage by creating file with a list of workstations (and maybe user name) - may be too much trouble. – Wayne G. Dunn Jun 04 '17 at 21:15

2 Answers2

0

I used to create a "locking file" which was just an empty text file with the name of the workbook followed by the username and an extension of .LCK

First thing my code did on auto open was look for a locking file then report back to the user which user had it open then cancel the open.

If it didn't find a locking file then it created one and proceeded as normal. If it found one but it was the same username (ie that user had it crash on them) it proceeded with the open.

The last thing the code did was delete the file.

Dan Donoghue
  • 6,056
  • 2
  • 18
  • 36
  • Hey Dan, sorry for coming back to this only now, but I haven't had the chance to touch the subject before. I like this idea a lot and I hope it works. I'll let you know about the outcome. Thanks a million! – cloudberry Oct 16 '17 at 16:16
0

No codes here and theory not tested yet, but the idea of preventing a different user executing a Macro on a shared workbook requires some thinking.

I would create a hidden worksheet, and use one of the cells to store the Environ("USERNAME") when the macro is first started - to indicate who has it running, then clear it when complete, first-in-first-out.

  1. Lets say named range MUser (macro user) is range A1 in that hidden worksheet
  2. When the macro runs, it will first check if MUser is empty, if so then change it's value to Environ("USERNAME") and Save the file before next step (here I am not certain the value is updated on others session).
  3. If MUser is not empty, either abort or retry in a few second.
  4. When macro completes, MUser will be ClearContents, and save the File to free up the workbook for macro.

Idea is here but please test. Post your own code for us to troubleshoot. You may also use Workbook events to "lock" the macro execution this way. Or even use this hidden sheet to make a log record for debug. Also some fail-safe needs to be implemented (such as a time stamp at macro start and override the lock after some minutes).

PatricK
  • 6,375
  • 1
  • 21
  • 25