Listing Seeing if a Workbook Is Open

' This function checks to see if a given workbook ' is open or not. This function can be used ' using a short name such as MyWorkbook.xls ' or a full name such as C:\Testing\MyWorkbook.xls Function IsWorkbookOpen(sWorkbook As String) As Boolean Dim sName As String Dim sPath As String Dim sFullName As String

On Error Resume Next IsWorkbookOpen = True

' See if we were given a short name or a long name If InStr(1, sWorkbook, vbTextCompare) > 0 Then

' We have a long name ' Need to break it down sFullName = sWorkbook BreakdownName sFullName, sName, sPath

If StrComp(Workbooks(sName).FullName,

IsWorkbookOpen = False End If

Else

' We have a short name If StrComp(Workbooks(sWorkbook).Name,

IsWorkbookOpen = False End If End If

End Function

This function requires the BreakdownName and FileNamePosition procedures from Chapter 5. The BreakdownName procedure is handy here because it enables you to create a function that doesn't care if it receives a simple workbook name or a full workbook name that includes the path where the file is stored.

This function is sort of tricky in a number of ways, especially because you haven't covered some of the functionality that makes this function work. One of the first things that clues you in to a critical technique that allows this function to work correctly is the On Error Resume Next statement. This function flat out wouldn't work without it. I'll tell you why in a few minutes. After the On Error Resume Next statement, you can see that I set the default return value to true.

Next, you need to check whether the parameter that was provided is in the form of a filename only, or if it contains that storage path and filename. You do that by seeing if it contains a "\" using the InStr function. Because slashes are prohibited within a filename, the only way you can have one is if you received a storage path in addition to the filename. If the sWorkbook parameter does contain the path in addition to the filename, you use the BreakdownName procedure from Listing 5.8 to isolate the actual name of the file from the location in which it is stored.

The trickiest parts of the IsWorkbookOpen function are the two If.. .Then statements that use the StrComp function. You might be wondering why you need two If.. .Then statements, one for the case in which we have a full name and one for the case in which we only have a filename. Why not use one If.. .Then statement to do this for both cases?

The reason you need two statements is that a significant difference exists between using this function with a short name versus a full name. When you use this function with a short name, you aren't considering the possibility that a workbook with the same name exists in multiple folders. This is fine in situations in which you want to check whether it is safe to open a file (you can't have two files open with the same name even if they are stored in separate folders). However, if you need to be sure that a specific file is open, the only way to do this is to provide this function with a full name.

You can see that the two StrComp function calls use different properties of the Workbook object. One uses the FullName property, which consists of a path and a filename, and the other uses the Name property, which only consists of a filename.

Also, notice how the Workbook object is used here—by going through the Workbooks object. The next section dives into this a little deeper. For now, just understand that you can refer to an individual item (in this case a Workbook object) within a collection by specifying its name within parentheses.

Remember the part about the On Error Resume Next statement being so critical to this function? Here is why. If you recall, the Workbooks object is the collection of all open workbooks. If you attempt sWorkbook, 1) <> 0 Then sWorkbook, 1) <> 0 Then

0 0

Post a comment