Microsoft Office Tutorials and References
In Depth Information
Some Useful Functions for Use in Your Code
The SheetExists function
This function accepts one argument (a worksheet name) and returns True if the worksheet
exists in the active workbook:
Private Function SheetExists(sname) As Boolean
‘ Returns TRUE if sheet exists in the active workbook
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err.Number = 0 Then SheetExists = True _
Else SheetExists = False
End Function
The WorkbookIsOpen function
This function accepts one argument (a workbook name) and returns True if the workbook is open:
Private Function WorkbookIsOpen(wbname) As Boolean
‘ Returns TRUE if the workbook is open
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err.Number = 0 Then WorkbookIsOpen = True _
Else WorkbookIsOpen = False
End Function
Retrieving a value from a closed workbook
VBA doesn’t include a method to retrieve a value from a closed workbook file. You can, however,
take advantage of Excel’s ability to work with linked files. This section contains a custom VBA
function ( GetValue , which follows) that retrieves a value from a closed workbook. It does so by
calling an XLM macro, which is an old-style macro used in versions prior to Excel 5. Fortunately,
Excel still supports this old macro system.
Private Function GetValue(path, file, sheet, ref)
‘ Retrieves a value from a closed workbook
Dim arg As String
‘ Make sure the file exists
If Right(path, 1) <> “\” Then path = path & “\”
If Dir(path & file) = “” Then
GetValue = “File Not Found”
Exit Function
End If
 
Search JabSto ::




Custom Search