Open file if not already open

[abc_xyz]

Hi,

The below code first checks if the required file is open, if it is open then use that file; if not, then open file from the path provided in the cell and read/write with that file. After completing the task, It further checks if the file path & name provided in the below cell is same or not, if same, then do nothing; if not, close the opened file without saving.

It works fine until the file path and name are same in the below cell. Throws an error when the file path and name is different in the below cell. It does not opens the file.

Not sure where am I going wrong. Can someone please help?

Sub RunQuery1()


Dim Lastrow As Long
Dim OpenBook_path, Available_File As String
Dim FileToOpen As Workbook
Dim wb As Workbook


Application.ScreenUpdating = False


Lastrow = ThisWorkbook.Sheets("Dashboard").Range("F" & Rows.Count).End(xlUp).Row

For i = 9 To Lastrow

    OpenBook_path = ThisWorkbook.Sheets("Dashboard").Cells(i, 6)  'Path includes file name with extension
    OpenBook_Sheet = ThisWorkbook.Sheets("Dashboard").Cells(i, 7)
    OpenBook_Range = ThisWorkbook.Sheets("Dashboard").Cells(i, 8)



'Check if file is open,if open, then use open file; if not, open file from the path in the cell
Available_File = Dir(OpenBook_path) 'extracts the file name from the path

  If Not wbOpen(Available_File, wb) Then Set FileToOpen = Workbooks.Open(OpenBook_path)
  


'open workbook from the path in the cell
With FileToOpen

    'Copy range from the sheet
    With Sheets(OpenBook_Sheet)
       .Range(OpenBook_Range).Select 'Do something
    End With

    
End With


'Check if Below File Path & Name are same
If ThisWorkbook.Sheets("Dashboard").Cells(i, 6) = ThisWorkbook.Sheets("Dashboard").Cells(i + 1, 6) Then

Else
FileToOpen.Close False
End If


Next i


Application.ScreenUpdating = True





End Sub



Function wbOpen(wbName As String, wbO As Workbook) As Boolean
    On Error Resume Next
    Set wbO = Workbooks(wbName)
    wbOpen = Not wbO Is Nothing
End Function

 

[Joe4]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Open file if not already open
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[abc_xyz]

Asked here - Open file if not already open

 

[rlv01]

Not tested.

Sub RunQuery1()
    
    Dim OpenBook_path As String, OpenBook_Sheet, OpenBook_Range, Available_File As String, Next_File As String
    Dim FileToOpen As Workbook
    Dim i As Long, Lastrow As Long
    Dim FSO As Object
    
    Application.ScreenUpdating = False
    
    Lastrow = ThisWorkbook.Sheets("Dashboard").Range("F" & Rows.Count).End(xlUp).Row
    
    For i = 9 To Lastrow
        OpenBook_path = ThisWorkbook.Sheets("Dashboard").Cells(i, 6)  'Path includes file name with extension
        OpenBook_Sheet = ThisWorkbook.Sheets("Dashboard").Cells(i, 7)
        OpenBook_Range = ThisWorkbook.Sheets("Dashboard").Cells(i, 8)
        
        'Check if file is open,if open, then use open file; if not, open file from the path in the cell
        Available_File = Dir(OpenBook_path) 'extracts the file name from the path
        
        Set FileToOpen = Nothing
        If Not wbOpen(Available_File, FileToOpen) Then
            Set FileToOpen = Workbooks.Open(OpenBook_path)
        End If
        
        'open workbook from the path in the cell
        With FileToOpen
            'Copy range from the sheet
            With Sheets(OpenBook_Sheet)
                .Range(OpenBook_Range).Select 'Do something
            End With
        End With
        
        'Check if Below File Path & Name are same
        Next_File = ThisWorkbook.Sheets("Dashboard").Cells(i + 1, 6) 'extracts the file name from the path
        If Trim(Next_File) = "" Then
            Exit For
        Else
            If OpenBook_path = Next_File Then
                
            Else
                On Error Resume Next
                FileToOpen.Activate
                FileToOpen.Close False
                Set FileToOpen = Nothing
                On Error GoTo 0
            End If
        End If
    Next i
    On Error Resume Next
    FileToOpen.Close False
    Application.ScreenUpdating = True
End Sub

 

[abc_xyz]

	Column F	Column G	Column H
Header Row	File Path/Name	Sheet Name	Data Range
Row 9	C:\Users\1234\Desktop\ABCD.xlsx	ABCD	C10:Q40
Row 10	C:\Users\1234\Desktop\ABCD.xlsx	ABCD	V10:Y40
Row 11	C:\Users\1234\Desktop\XYZ.xlsx	XYZ	C10:Q40

Ref above table
For Row 9 - the code should first check if the file path provided in column F is already open or not. If open, use that file; if not then open it. Do something and then check if the below file path in Row 10 is same or not, if same, then do nothing; if not, close it. [Here, the file path is same in Row 10. So, it will not close it]

For Row 10 - the code should first check if the file path provided in column F is already open or not. If open, use that file; if not then open it. [Here, logically, the file will be already open as the file path is same in Row 9 and Row 10. So, use the same file]. Do something and then check if the below file path in Row 10 is same or not, if same, then do nothing; if not, close it.

For Row 11 - the code should first check if the file path provided in column F is already open or not. If open, use that file; if not then open it. [Here, logically, no file will be open. so, open the file provided in the Column F].

The idea is to cut down the time taken to close the opened file and reopen the same file.
I have 50+ such lines. The provided code takes similar time to process when compared to individually closing and reopening the same files. However, does not give any error.
Is there a way we could optimize the code?

 

[rlv01]

Using the code I posted, and 50 rows of workbook file paths, my results are

1) Case using 50 files (50 rows) where the next row is always the same as the previous row (same file, no need to close): Elapsed Time: 0.36 sec
2) Case using 50 files (50 rows) where the next row is always different from the previous row (need to close & open): Elapsed Time: 19 sec.

I think that if you are not seeing a lot of difference, that may indicate that you don't have enough "same" instances to warrant the extra complexity of checking to see if the next file is the same and keeping that workbook open.

 

[abc_xyz]

Got it, will deep dive into the data set. Anyway, thank you so much for your help.