0

Just after initial pointers on using VBA for the following:

Create a time keeping app running in the background that detects when any File within the 'Projects' folder is opened, and ask user if they want to start recording time to this job. Timer would stop when file from a different Project is opened, or when the original File is closed. I guess i'd need to filter out certain file types (temp files etc.)

Alternative approach could be to detect if active window is a project file. Perhaps this better than the above, as it would allow for scenarios where files from different projects are open, as well as being able to build in a delay - i.e. no need to log time if only active for 2 minutes.

Possible?

Smartbuild
  • 119
  • 3
  • 15
  • 1
    Yes, it's possible! You can play around with [FSO](https://stackoverflow.com/documentation/vba/990/scripting-filesystemobject#t=201707131037445699604) and [WMI query](https://stackoverflow.com/questions/15323676/check-if-file-is-in-use-with-vbs). – CommonSense Jul 13 '17 at 10:38
  • Thanks CommonSense, I was starting to go down the wrong track, you bought me back in line! I've tried using [this code](https://blogs.technet.microsoft.com/heyscriptingguy/2005/02/16/how-can-i-list-open-sessions-and-open-files-on-a-computer/#comment-996675) (3rd example down) to to show file open on my local computer (replacing "atl-ws-01/LanmanServer" with my PC name, but no joy. Any advice? – Smartbuild Jul 13 '17 at 12:10
  • First of all, why you're trying to achieve this (event-type routine) with VBA? Which one aplication from VBA family it is? – CommonSense Jul 13 '17 at 12:25
  • Because VBA is all i know! I mainly use it with Excel, but I have the idea in the back of my head that it will eventually be linked with Access to form part of a bigger project management solution. – Smartbuild Jul 13 '17 at 12:39
  • It's reasonable statement, @user2048265. Did you tried to combine my two suggestions? It's works well, but to be honest - your idea can't be implemented via plain VBA, since it's single-threaded while your demand is about checking folder *AND* opened file *AND* sort of GUI. I assume that it can be partially done in single thread (checking folder and file's open time), but your Excel application will be unresponsive because of single thread, hence you can't use Excel as GUI. If you want some sort of GUI from Excel, than you can do all checkings/timings with VBScript and control it from Excel. – CommonSense Jul 18 '17 at 14:38
  • Thanks CommonSense, I did a lot of research and trying various code with your useful FSO and WMI links. I've got a partial solution, using process.commandline and parsing the filep path, but my stumbling point is not being able to get the files currently open by the drawing software AutoCAD LT, which is one of the main criteria. I think it's because AutoCAD is 64bit. Also, I don't fully understand the significance/limitations of single thread - I'll start researching that! – Smartbuild Jul 24 '17 at 08:53
  • Good idea, @user2048265! In general, a common GUI application has the event-loop, which constantly redraws GUI and responds to user interactions. If your code flow catches a heavy routine (fo e.g. - keep checking opened file over and over), he's unable to return to event-loop. When this occures - you could see "Not Responding" window's title. Same applies to a vba applications as well. My explanation can be a little bit crude, but I think that you can imagine some sort of multitasking on code level..(: – CommonSense Jul 24 '17 at 09:07

0 Answers0