'********************************************************************************************************** ' Name: openWorkbook ' Author: mielk | 2014-02-26 ' ' Comment: Function to open the Excel file with the given filepath in the specified Excel ' application instance. ' ' Parameters: ' filePath The path of a file to be opened. ' readOnly Optional parameter of Boolean type. ' It determines if the file should be open in read-only mode. ' Default value for this parameter is False, so file is open in write-read mode. ' excelInstance Excel instance to be used when opening the specified file. ' createIfNotExists Optional parameter of Boolean type. ' It determines if the file should be created if it not exists yet. ' ' ' Returns: ' Excel.Workbook Reference to the Excel file with the specified filepath. ' ' ' Exceptions: ' PathNotFoundException Thrown if the given path has not been found. ' ' --- Changes log ----------------------------------------------------------------------------------------- ' 2014-02-26 mielk Function created. '********************************************************************************************************** Public Function openWorkbook(filepath As String, Optional readOnly As Boolean = False, _ Optional excelInstance As Excel.Application, _ Optional createIfNotExists As Boolean = False) As Excel.Workbook Const METHOD_NAME As String = "openWorkbook" '------------------------------------------------------------------------------------------------------ Dim xls As Excel.Application Dim bAlerts As Boolean '------------------------------------------------------------------------------------------------------ 'Find out what Excel application instance should be used for checking. ------------------------------| If excelInstance Is Nothing Then '| Set xls = Excel.ThisWorkbook.Application '| Else '| Set xls = excelInstance '| End If '| '----------------------------------------------------------------------------------------------------| 'Switch off the alerts when opening workbook. Alerts settings will be restored at the end of --------| 'this function. '| With xls '| bAlerts = .DisplayAlerts '| .DisplayAlerts = False '| End With '| '----------------------------------------------------------------------------------------------------| If isFileOpen(filepath, xls) Then Set openWorkbook = xls.Workbooks(getFileName(filepath)) Else If fileExists(filepath) Then Set openWorkbook = xls.Workbooks.Open(filepath, readOnly:=readOnly, UpdateLinks:=False) End If End If 'Restoring [DisplayAlerts] setting. -----------------------------------------------------------------| 'It is not critical, so if any error occurred just skip it and continue. '| On Error Resume Next '| xls.DisplayAlerts = bAlerts '| On Error GoTo 0 '| '----------------------------------------------------------------------------------------------------| End Function '********************************************************************************************************** ' Name: isFileOpen ' Author: mielk | 2014-02-26 ' ' Comment: Function checks if the given file is open in the specified Excel instance. ' ' Parameters: ' filePath The path of a file to be checked. ' excelInstance Excel instance to be searched through for the given file. ' If this parameter is ommitted, the current Excel instance is used. ' ' Returns: ' Boolean True - if the given Excel file is open in the specified Excel instance. ' False - otherwise. ' ' ' --- Changes log ----------------------------------------------------------------------------------------- ' 2014-02-26 mielk Function created. '********************************************************************************************************** Public Function isFileOpen(filepath As String, Optional excelInstance As Excel.Application) As Boolean Const METHOD_NAME As String = "isFileOpen" '------------------------------------------------------------------------------------------------------ Dim xls As Excel.Application Dim fileName As String: fileName = getFileName(filepath) Dim wkb As Excel.Workbook '------------------------------------------------------------------------------------------------------ 'Find out what Excel application instance should be used for checking. ------------------------------| If excelInstance Is Nothing Then '| Set xls = Excel.ThisWorkbook.Application '| Else '| Set xls = excelInstance '| End If '| '----------------------------------------------------------------------------------------------------| 'Try to get the reference to the specified file from the given Excel instance. If nothing -----------| 'is assigned, it means there is no such file open in this Excel instance. '| On Error Resume Next '| Set wkb = xls.Workbooks(fileName) '| On Error GoTo 0 '| '----------------------------------------------------------------------------------------------------| '----------------------------------------------------------------------------------------------------| If wkb Is Nothing Then '| isFileOpen = False '| Else '| isFileOpen = compareString(wkb.FullName, filepath) '| End If '| '----------------------------------------------------------------------------------------------------| End Function '********************************************************************************************************** ' Name: getFileName ' Author: mielk | 2014-02-26 ' ' Comment: Function returns the name and extension of the given full filepath. ' ' Parameters: ' filepath The path of a file which name is to be returned. ' ' Returns: ' String The name of the given file (including extension). ' If the given parameter is not a proper file path, an empty String is returned. ' ' ' --- Changes log ----------------------------------------------------------------------------------------- ' 2012-03-26 mielk Function created. '********************************************************************************************************** Public Function getFileName(filepath As String) As String Const METHOD_NAME As String = "getFileName" '------------------------------------------------------------------------------------------------------ Dim slashPosition As Integer '------------------------------------------------------------------------------------------------------ slashPosition = VBA.InStrRev(filepath, "\") getFileName = VBA.Mid(filepath, slashPosition + 1) End Function '********************************************************************************************************** ' Name: compareString ' Author: mielk | 2013-05-15 ' ' Comment: Function compares two strings and returns True if they are equal. ' The difference between this function and function compareStrings is that this ' function can compare only two strings but it is much faster, so it is recommended ' to use this function instead of [compareStrings] if there is no need to compare ' more than two strings to each other. ' ' ' Parameters: ' baseString Base string. ' comparedString String to be compared to the base string. ' isCaseSensitive Optional parameter of Boolean type. ' It defines if comparing is case-sensitive. ' Default value of this parameter is True, that means comparing is case-insensitive. ' trimmed Optional parameter of Boolean type. ' It defines if white spaces at the beginning and at the end of strings should be ' ignored. ' Default value of this parameter is True. ' ' ' Returns ' Boolean True - if both given strings are equal (excluding white spaces if [trimmed] ' parameter is set to True). ' False - if given strings differ from each other or if any of string is null. ' ' ' Exceptions: ' IllegalObjectException Thrown if parameter [baseString] or [comparedString] passed to this ' functions are objects, arrays or other values that cannot be converted ' to String. ' ' ' --- Changes log ----------------------------------------------------------------------------------------- ' 2013-05-15 mielk Method created. '********************************************************************************************************** Public Function compareString(baseString As Variant, comparedString As Variant, _ Optional isCaseSensitive As Boolean = False, Optional trimmed As Boolean = True) As Boolean Const METHOD_NAME As String = "compareString" '------------------------------------------------------------------------------------------------------ Dim compareMethod As VBA.VbCompareMethod Dim base_ As String Dim compared_ As String '------------------------------------------------------------------------------------------------------ 'Fast check if both strings are exactly the same. In such case True is returned without -------------| 'taking any further actions. '| If baseString = comparedString Then GoTo EqualStrings '| '----------------------------------------------------------------------------------------------------| 'Check if any given string is Null. In such case False is returned (even if both strings are null). -| If VBA.isNull(baseString) Or VBA.isNull(comparedString) Then GoTo NullStrings '| '----------------------------------------------------------------------------------------------------| 'Check if both given strings can be converted into Strings. If at least one of them cannot be -------| 'converted, IllegalObjectException is thrown. '| On Error GoTo IllegalObjectException '| base_ = VBA.CStr(baseString) '| compared_ = VBA.CStr(comparedString) '| On Error GoTo 0 '| '----------------------------------------------------------------------------------------------------| 'Convert parameter [isCaseSensitive] of Boolean type to VbCompareMethod type. -----------------------| compareMethod = VBA.IIf(isCaseSensitive, VBA.vbBinaryCompare, VBA.vbTextCompare) '| '----------------------------------------------------------------------------------------------------| 'If the [trimmed] parameter is set to True, remove all white spaces at the beginning and at the -----| 'end of the input strings. '| If trimmed Then '| base_ = VBA.Trim$(base_) '| compared_ = VBA.Trim$(compared_) '| End If '| '----------------------------------------------------------------------------------------------------| 'Check if strings are the same, by using VBA built-in function StrComp. compareString = (VBA.StrComp(base_, compared_, compareMethod) = 0) '========================================================================================================== ExitPoint: Exit Function '---------------------------------------------------------------------------------------------------------- EqualStrings: compareString = True GoTo ExitPoint NullStrings: compareString = False GoTo ExitPoint IllegalObjectException: 'Error handler for the case if objects, arrays or any other values that cannot be converted to 'String have been passed as an input parameters. GoTo ExitPoint End Function '********************************************************************************************************** ' Name: fileExists ' Author: mielk | 2012-12-02 ' ' Comment: Function to check if file with the given filepath exists in the file system. ' This is a wrapping function for method .fileExists of FileSystemObject class that ' lets a user avoid declaring a new instance of FileSystemObject class when invoking ' this function. ' ' Parameters: ' folderPath The path of a file to be checked. ' ' ' Returns: ' Boolean True - if file with the given filepath exists. ' False - otherwise. ' ' ' --- Changes log ----------------------------------------------------------------------------------------- ' 2012-12-02 mielk Function created. '********************************************************************************************************** Public Function fileExists(filepath As String) As Boolean Const METHOD_NAME As String = "fileExists" '------------------------------------------------------------------------------------------------------ Static objFSO As Object '(Late binding that allows to use the function, even if 'Microsoft Scripting Runtime library is not loaded) '------------------------------------------------------------------------------------------------------ 'Create FileSystemObject instance if it hasn't been created yet. ------------------------------------| If objFSO Is Nothing Then '| Set objFSO = VBA.CreateObject("Scripting.FileSystemObject") '| End If '| '----------------------------------------------------------------------------------------------------| fileExists = objFSO.fileExists(filepath) End Function