CODEWITHSUNDEEP
excelvba
bluewayrafi
bluewayrafi

Reputation: 13

Why is VBA not able to locate an Excel file in a particular path, unless the file is re-saved with same name?

I have a VBA macro to open three workbooks (call them "file0.xlsm", "file1.xlsm", and "file2.xlsm") for reading and editing.

The problem is with opening the first of these files. Whenever I start Excel and attempt to run the macro, VBA shows the error:

"Runtime Error 1004:
Sorry we couldn't find 'file0.xlsm'. Is it possible it was moved, renamed, or deleted?"

The file is in the directory and does have the correct name. (All three files are in the same directory as the workbook where the code lives.) Moreover: if I open the workbook "file0.xlsm", hit "Save As", save it with the same name, close it, and then run the VBA code, the error no longer appears.

The error appears regardless of (1) Whether the file is saved as .xlsx or .xlsm format, (2) Whether the file name is hard-coded or user-specified in a spreadsheet, and (3) the order in which I open the files.

I tried moving the data from file0 to a new workbook (by copying worksheets from file0 to the new book one at a time), and then referencing opening the new book instead of file0 in my code. The error persists.

I also checked for leading/trailing spaces in my file names, and followed the procedure on this page: https://github.com/OfficeDev/VBA-content/issues/637.

Code for opening the books:

If Not IsWorkBookOpen(book1) Then
    Workbooks.Open Filename:="file0.xlsm", UpdateLinks:=0, ReadOnly:=False, ignorereadonlyrecommended:=0, notify:=False, addtomru:=True
End If
If Not IsWorkBookOpen(book2) Then
    Workbooks.Open Filename:="file1.xlsm", UpdateLinks:=0, ReadOnly:=False, ignorereadonlyrecommended:=0, notify:=False, addtomru:=True
End If
If Not IsWorkBookOpen(book3) Then
    Workbooks.Open Filename:="file2.xlsm", UpdateLinks:=0, ReadOnly:=False, ignorereadonlyrecommended:=0, notify:=False, addtomru:=True
End If

Note: IsWorkBookOpen() is a function that checks if a book is already open.

Code for IsWorkBookOpen():

Function IsWorkBookOpen(Name As String) As Boolean
    Dim xWb As Workbook
    On Error Resume Next
    Set xWb = Application.Workbooks.Item(Name)
    IsWorkBookOpen = (Not xWb Is Nothing)
End Function

Upvotes: 1

Views: 3388

Answers (1)

chris neilsen
chris neilsen

Reputation: 53136

Your issue is that because you only specify the file name and not the path, you are attempting to open a file from the Current Directory, whatever that may be. If that directory contains a file with the specified name then that file opens (which may or may not be the file you want to open!). When an Open succeeds the Current Dirctory is updated to the directory just used.

So, you either need to verify/update the Current Directory (not reccomended) or fully qualify your path and file name.

On a side note, you've already written a helper function IsWorkBookOpen. I'd extend that to also open the file if required, and check for correct formatting of the path and filename.

Something like this:

Function ReturnWorkBook(Name As String, Optional Path As String = vbNullString) As Workbook
    Dim xWb As Workbook
    On Error GoTo EH

    Set ReturnWorkBook = Application.Workbooks(Name)
Exit Function
EH:
    Resume EHOpen
EHOpen:
    On Error GoTo EHFail
    If Path = vbNullString Then Path = ThisWorkbook.Path
    If Right$(Path, 1) <> "\" Then Path = Path & "\"
    If Not (Right$(Name, 4) Like ".xls[xm]") Then
        Name = Name & ".xlsm"  'Assumes default file type, update as needed
    End If
    Set ReturnWorkBook = Application.Workbooks.Open( _
        Filename:=Path & "\" & Name, _
        UpdateLinks:=0, _
        ReadOnly:=False, _
        IgnoreReadOnlyRecommended:=False, _
        Notify:=False, _
        AddToMru:=True)
EHFail:
    ' Exit with ReturnWorkBook = Nothing
End Function

You can call it in a number of ways

Dim wb0 as Workbook
Dim wb1 as Workbook
Dim wb2 as Workbook

Set wb0 = ReturnWorkBook("file0.xlsm", ActiveWorkbook.Path)
Set wb1 = ReturnWorkBook("file1.xlsm") ' uses Thisworkbook.Path
Set wb2 = ReturnWorkBook("file2")      ' uses Thisworkbook.Path and .xlsm extension

Your following code can then use wb0 , wb1 , wb2 to check for failure (eg If wb0 = Nothing Then), and refer to the opend books (eg wb1.Worksheets("SomeSheet") etc)

Upvotes: 1

Related Questions