Closing already open excel file using vba

[praveenpatel421983]

Hi all,
About my excel knowledge, I am using excel vba for last 2 years. I am not an expert but I know the basics of the vba.

I am working on excel vba to extract data from other files in a particular folder and subfolders which is executing perfectly. I have used userform to select the template file as well as to select the source folder where other files are located and the program is executed using a command button called “ProceedButton”

I am using a excel template where all the extracted data will be pasted. If this file is open I get an error. To avoid that, I inserted the below code in my code. I have used workbooks.open() to set the workbook for variable but this opens the file as read only if it is already open and when I use .close, it closes the read only file not the actual file. I tried using workbooks() to set it as variable but it gives ‘runtime error 9’. I have tried various options by going through different posts by user but I was not able to find the right solution. Could someone help?

Private Sub ProceedButton_Click()
Dim FSO As Object
Dim wbk As Workbook
Dim TempPath As String
'Dim TestingForClose As Excel.Workbook
'Set TestingForClose = Workbooks(TemplateFileNameTextBox.Text) '<======= Used this text box to assisgn the file name

Application.DisplayAlerts = False
TempPath = TemplateTextBox.Text '<======= This textbox has the path of the file

If IsFileOpen(TempPath) Then

Set wbk = Workbooks.Open(TempPath) '<======= Opens read only file if the file is already open
' Set wbk = Workbooks(TempPath) '<======= Gives Runtime error 9 "Subscript out of range"
' Set wbk = Workbooks(TemplateFileNameTextBox.Text) '<======= Gives Runtime error 9 "Subscript out of range"

If MsgBox("Template file already in use!" & vbNewLine & "Do you want to close it?", vbYesNo + vbQuestion, "File in use") = vbYes Then
' wbk.Activate
' ActiveWorkbook.Close False
' wbk.Save
wbk.Close True '<======= Closes the read only file not the actual file
' If Application.ActiveWorkbook.Name = TestingForClose Then
' TestingForClose.Close SaveChanges:=True
' End If
' Workbooks(TempPath).Close savechanges:=False
Else
Exit Sub
End If
End If

End Sub

Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer

On Error Resume Next
filenum = FreeFile()
Open filename For Input Lock Read As #filenum
Close filenum
errnum = Err
On Error GoTo 0

Select Case errnum
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function

 

[dmt32]

Hi,
not tested but maybe something like following will do what you want

Private Sub ProceedButton_Click()
    
    Dim wbk As Workbook
    Dim TempPath As String, TempFileName As String
    
'This textbox has the path of the file
    TempPath = TemplateTextBox.Text
    
'This textbox has the name of the file
    TempFileName = TemplateFileNameTextBox.Text
    
    On Error Resume Next
'check if TemplateFile Already Open
    Set wbk = Workbooks(TempFileName)
    If Err.Number = 9 Then
'if the file is not already open - Open it read only.
        Set wbk = Workbooks.Open(TempPath & "\" & TempFileName, ReadOnly:=True)
        Err.Clear
    End If
    
    On Error GoTo myerror
    
'do stuff with wbk object variable
    
    
'Close the read only file
    wbk.Close False
    
myerror:
'report errors
    If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Dave

 

[praveenpatel421983]

Thanks for your reply dmt32. It didnt work. It is still not letting me to move from Set wbk = Workbooks(TempFileName) . I am getting same error "Runtine error 9". I tried with GetObject(), it worked but not sure whether it is the right one to use.

 

[dmt32]

It should work but I wonder if problem is with your error handling settings

From VBA Editor Tools > Options Select General Tab. In Error Trapping Section Select "Break On Unhandled Errors" > Press OK.

Then try and run code as published again & see if works ok.

Dave

 

[praveenpatel421983]

Thanks again for your reply dmt32. I am sorry for replying late.

I tried to find "Break On Unhandled Errors" but I didn't find it. Are you talking about 2013 version of excel? I am using 2010 version, does 2010 have this option?

Praveen

 

[codeliftsleep]

This works, I use it every day in a production file:

Private Sub CloseWB()
    Dim WB as Workbook
    
    For each WB in Workbooks
         'if you want to close all of them
          WB.Close
          'if you only want to close certain ones
          if WB.Name = "MyWorkbookName" then WB.Close
    next WB
End Sub

 

[dmt32]

Hi,
I use 2013 so cannot comment about 2010 maybe another here can comment

As a guide: https://msdn.microsoft.com/en-us/vb...articles/set-visual-basic-environment-options

Dave

 

[MARK858]

Hi,
I use 2013 so cannot comment about 2010 maybe another here can comment

As a guide: https://msdn.microsoft.com/en-us/vb...articles/set-visual-basic-environment-options

Dave

dmt32/codeliftsleep, it is in exactly the same place in Excel 2010 as in the link.

 

[praveenpatel421983]

I got it. I apologize I was looking in excel options (wrong place....my bad).

"Break On Unhandled Errors" is already selected by default for me. I have tested few times using GetObject() , it is working fine so far. So I am planning to go ahead with it until I face a new issue .

Thanks a lot to every reply, that's a good learning for me.This forum has always helped me learn new things and improve my knowledge on Excel Vba. You guys are doing great job by guiding beginners like us. Keep up the good job!

Thanks,
Praveen

 

[solidENM]

this code worked great for me. Thanks Codeliftsleep
I had trouble finding code that would close a workbook that I knew was open.

This works, I use it every day in a production file:

Private Sub CloseWB()
    Dim WB as Workbook
    
    For each WB in Workbooks
         'if you want to close all of them
          WB.Close
          'if you only want to close certain ones
          if WB.Name = "MyWorkbookName" then WB.Close
    next WB
End Sub