Before opening a excel file using VBA, you need to test particular file is open or not in excel. This is required as it can result into a error if particular file is already open.
You need to go step by step to open the file
1. Check if excel file is open
2. Open that file using VBA , This should be done only in case you know the full path for the particular file
3. In case you do not know the full path , it is advisable to give excel file open dialog and let user open the file.
You will need to
copy this code to regular VBA module of your workbook
Sub Open_File_after_IsOpen_or_Not()
'*************************************************************************************************************
'* Macro recorded by Yogesh Gupta, smiley123z@gmail.com, Ygblogs.blogspot.com on September 16, 2009 *
'* Macro modified on September 19, 2009 based on inputs from Mr. Augusto as per comment left by him *
'* Mr. Augusto added FileExist function though his comment and included in this Updated Macro by Yogesh Gupta*
'*************************************************************************************************************
Dim Myworkbook As String
Myworkbook = "FileName.xls" ' Replace the workbook name here
If Isopen(Myworkbook) = "Not Open" Then ' In case workbook is not open
If FileExist(Myworkbook) = True Then ' In case workbook exist
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & Myworkbook
Else
Call Open_new_file ' let user choose and open the file
End If
Else
Application.Workbooks(Myworkbook).Activate
End If
End Sub
Sub Open_new_file()
NewWorkbook = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
If NewWorkbook = False Then
Exit Sub
Else
Workbooks.Open Filename:=NewWorkbook
End If
End Sub
Public Function Isopen(Myworkbook As String)
On Error Resume Next
Set wBook = Workbooks(Myworkbook)
If wBook Is Nothing Then
Isopen = "Not Open"
Exit Function
End If
End Function
Public Function FileExist(Myworkbook As String) As Boolean
Dim nfile
On Error GoTo FileExist_err
nfile = FreeFile
Open Myworkbook For Input As nfile
Close nfile
FileExist = True
Exit Function
FileExist_err:
FileExist = False
End Function |
Download file having File open Macro with test if particular workbook is open or not
File open VBA , VBA open files , File Open Macro , Excel File open VBA , VBA Excel Workbook Open , VBA Excel Open Workbook , VBA open Workbook , Test particular file is open or not in excel , Check if file is open - Excel
Correct, this is a best practice for sure. I'd only add a file existence control before opening it, even directly from the code or after user's choice. From a general standpoint, something may always remove or lock the file between choice and actual opening.
ReplyDeleteIn Viney@rd (http://www.straysoft.com)I use this small boolean function.
Public Function FileExist(fname As String) As Boolean
Dim nfile
On Error GoTo FileExist_err
nfile = FreeFile
Open fname For Input As nfile
Close nfile
FileExist = True
Exit Function
FileExist_err:
FileExist = False
End Function
It works with every file, not just Excel.
Thanks Augusto - This is very valid input, we need to build this check before opening the file
ReplyDeleteRegards//Yogesh
This only tests if you have opened the file yourself, not if anyone else has opened the file.
ReplyDelete