Sometime we have to call another Worksheet to get some data. Thanks to this code we could check if data source file is opened or closed.
In my case file couldn’t be open because a copy of file had to be created but under other name. When file was open during file copying an error occurred. Unfortunately I didn’t remember code source site.
Sub CheckOpen() Dim Ret Dim FolderPath As String FolderPath = "Folder path where source files is located" Ret = IsWorkBookOpen(FolderPath & "\FileName.xlsx") If Ret = True Then ' Worksheet is opened - close without save changes Windows("FileName.xlsx").Close SaveChanges:=False Debug.Print "File has been closed" ' to be removed Else ' Nothing happened - FileName.xlsx is closed Debug.Print "File is closed." ' to be removed End If ' FileName.xlsx copying and create FileName2.xlsx FileCopy FolderPath & "\FileName.xlsx", FolderPath & "\FileName2.xlsx" ' FileName2.xlsx opening Workbooks.Open FileName:=FolderPath & "\FileName2.xlsx" End Sub