I have two custom add-ins loaded that use application-level events. I only want those events to run when a workbook associated with my application is active. Otherwise, the events in my purchase order application will try and do stuff to my invoices and vice versa. Not good.
For every application, the first thing I do is test to make sure I’m dealing with an appropriate object; be it a sheet, workbook, or whatever. I use two utilities to do the testing. The first utility verifies an open workbook and the second verifies a closed one.
As described in Custom Document Properties, I use custom document properties in my templates to identify the workbook as being part of the application. The utilities check the property and return True if it’s there.
Optional ByVal sProperty As String = gsCDPINVAPP) As Boolean
Dim bTemp As Boolean
On Error Resume Next
bTemp = Wb.CustomDocumentProperties(sProperty).Value
On Error GoTo 0
IsOpenInvoice = bTemp
End Function
Function IsInvoice(ByVal sName As String, _
Optional ByVal sProperty As String = gsCDPINVAPP) As Boolean
With Application.FileSearch
.NewSearch
.FileType = msoFileTypeAllFiles
.Filename = Dir(sName)
If Not sName = Dir(sName) Then
.LookIn = Replace(sName, Dir(sName), “”)
End If
.PropertyTests.Add sProperty, msoConditionIsYes
.Execute
IsInvoice = .FoundFiles.Count > 0
End With
End Function
In IsOpenInvoice, the property value from the supplied workbook is set to a Boolean variable. If the property value is False or if the property doesn’t exist, the variable is False. Of course the property value will never be false. I created the properties in the template and set the value to True. It will either exist or not.
For closed workbooks, IsInvoice uses FileSearch to find a file with the proper name and with the correct property. The argument sName is the full path and name of the file. If the file exists in the directory and has the property, True is returned. This is typically used before a file is opened, such as after GetOpenFilename is used but before the file is actually opened.
Both functions have an optional second argument, sProperty, that defaults to the property name that identifies the application in general. I also use these utilities to identify particular templates within my app. I may need to know, for instance, if the open workbook is an invoice, a sales order, or a report in particular, rather than just part of my app in general. I have constants set up for each template type that I want to test.
Here’s an example of an application level event in a custom class module that uses IsOpenInvoice:
If IsOpenInvoice(Sh.Parent, gsINVOICE) Then
‘Do stuff
End If
End Sub
Is this going to work in Excel 2007? I heard that Application.FileSearch has been discontinued.
Good catch Charles. I guess I’ll have to find something that can search custom document properties to replace FileSearch. Maybe Scripting Runtime can do it – I don’t know.
Does anyone know why MS removed that feature? I’d used it quite a lot in the past but once I heard that it was going away I migrated most of my stuff to the dir function.
I discovered some time ago that Windows Indexing Service had to be selected for FileSearch to work correctly. I believe that MS recognized this problem and it might be the reason for its removal.
Hi Dick:
(1) I did not use FileSearch until recently because some folks (I don’t recall who) had reported that it did not find all files that met the specified criteria.
(2) I suspect there is some Windows API (or combination of APIs) that will give you what you want. For example, with FileSystemObject I cannot find any information about an image but Windows Explorer reports on things like size, resolution, bit depth, etc. So, there must be a way to get that information
(3) For reasons now lost in the haze of history, I had considered and rejected the use of custom properties to identify files. One reason might be that I wanted a way to check not just the active workbook but also the activesheet. Currently, I rely on one of two techniques: either the name of the worksheet has to be so-and-so or a predefined cell must have a specific ‘tag’ value.
David and Tushar – I used to use FileSearch all the time, and never noticed its shortcomings, but after reading about them, I learned to Dir() my way around. I gather that MS decided it would be easier to scrap FileSearch and its problems (including the indexing service dependency) rather than to try to fix it.
Tushar – I don’t like relying too heavily on sheet names or specific cell values. Users change things like this. I use the sheet code name if I can predefine it (i.e., the sheet isn’t added on the fly), or hidden names at the workbook and worksheet level. I use custom properties to store the version number or date of files, so I can read them in Windows Explorer, and I have dsofile.dll on my dev machine so I can set them on closed files (and on non-Office files, like PDFs).
Kind of OT
Just bumped in to a problem in a project…
If a ado connection is not closed(due to say an error)…all worksheet events stop working…presumably due to some mem leak problems…
Dick
As Jon mentioned dsofile.dll (from MS) will get your properties on closed wbs. I did have a version that didn’t need registering but can’t currently find it.
cheers
Simon
When I first wrote ScanXLS, I used FileSearch and rapidly discovered that it also returned shortcuts to nonexistent files. Words like buggy and flaky occur in my notes at that time. So I switched to DIR and have had no problems since.
FileSearch is now Hidden in Excel 2007.
Patrick