Wednesday, September 16, 2009

Excel Macro : File open VBA

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

4 comments:

  1. 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.

    In 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.

    ReplyDelete
  2. Thanks Augusto - This is very valid input, we need to build this check before opening the file

    Regards//Yogesh

    ReplyDelete
  3. This only tests if you have opened the file yourself, not if anyone else has opened the file.

    ReplyDelete
  4. Thanks for sharing your info. I really appreciate your efforts and I will be waiting for your further write ups thanks once again.
    youtube html5 player

    ReplyDelete