Populate a listbox with the names of open files in a folder 2

[JasonEnsor]

Hi Guys,

I am currently re-coding an old project that runs VBA code in Word and Excel 2010 (originally it was 2003). I am considering setting up an AddIn that adds a custom taskpane in Excel to track when documents in a particular folder are open/have been updated since the last check. The check would be done when the AddIn loads and subsequent checks would be done by running a macro.

The purpose of this is I am looking at mimicking some of the concepts of a Document Management system without leaving Excel. The documents would be all created from a template file, my reasoning is that in the small team that I work in we have 2 offices in the same building and we all tend to use the same documents. it becomes a pain to constantly have to ring someone up and say "can you tell me when you are finished with document x" and I know I could just keep trying to open the document periodically but what's the point of automation if you don't use it I have considered trying to do this in C# as a stand alone application but I quite like the thought of going down the vba route if possible then maybe updating it to Visual Studio Tools For Office at a later date if I find it works how I want. Once I have the code sorted I will then look at setting up backstage view and a custom ribbon to run the addIn.

So in summary what I need to know is:
How do I check if an Excel document in a folder is open?
How do I populate a listbox with open files

Do you guys think this is plausible?

Any Comments/Ideas or questions about what I am doing are very welcome as I know many of you are a lot more experienced than I am in VBA.

Many Thanks

J.

 

[PHV]

How do I check if an Excel document in a folder is open?

http://vbaadventures.blogspot.fr/2008/08/isfileopen.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

If you use PHV's suggestion, then...
"How do I populate a listbox with open files"

I created a UserForm in Excel, placed 2 list boxes (lstAvailable and lstInUse) and a command (cmdCheck)

Option Explicit
Private Const FILE_PATH As String = [red]"C:\SomeFolder\AnotherFolder\"[/red]

Private Sub cmdCheck_Click()
Dim strFile As String

Me.MousePointer = fmMousePointerHourGlass

lstInUse.Clear
lstAvailable.Clear
Me.Repaint

strFile = Dir(FILE_PATH)

Do While strFile <> ""
    If IsFileOpen(FILE_PATH & strFile) Then
        lstInUse.AddItem strFile
    Else
        lstAvailable.AddItem strFile
    End If
    strFile = Dir
Loop

Me.MousePointer = fmMousePointerDefault

End Sub

Have fun.

---- Andy

 

[JasonEnsor]

Hi Guys,

Thank you both so much, it's nice to know my ideas are actually feasible. I managed to get a quick demo running, so my next task is to build upon that and see where i can take my ideas.

J.