Workbooks.Open won't open any files

Question

I'm new to Excel VBA. In a new workbook (macro_tester.xlsm) I created a new Module1 and put this code into it:

Public Function fileopen(f)

    Dim wbk As Workbook
    
    file = f
    
    If Dir(file) = "" Then
        fileopen = "File '" & file & "' Not Found"
        Exit Function
    End If
    
    Set wbk = Workbooks.Open(file)
    If wbk Is Nothing Then
        fileopen = "File '" & file & "' Failed To Open"
        Exit Function
    End If

End Function

I created an empty Excel file called test.xlsx and it is on a USB drive D:. No matter what I do, I always get the message "File 'D:\test.xlsx' Failed To Open". The file is found but not opened.

I've used fully qualified pathnames to the local directory, I've used a USB drive with the file in the root. And I've made sure that the target file is not open (just the one with the macro in it). I've also tried to open other Excel files. I can't get any file to open.

Any suggestions would be appreciated.

`Workbooks.Open(file)` should give you a run-time error if it fails. — Tim Williams, Aug 31 '23 at 23:59

score 2 · Accepted Answer · answered Sep 01 '23 at 05:57

I assume you are trying to use the fileopen Function from a cell in a Worksheet ... you cannot open files that way.

If you add this code in the same Module as fileopen (adjusting the file path if necessary):

Sub TestFileOpen()
    Debug.Print fileopen("D:\test.xlsx")
End Sub

Then run this Sub, you will find that the file is opened perfectly fine.

If your end-outcome is to get values from another closed Workbook then this can be done and there are multiple SO questions and answers on this subject, an example is here

score 0 · Answer 2 · answered Sep 01 '23 at 18:06

@JohnM Thanks for your response. I assumed that you can't open files from functions called within cells (which is what I was doing for testing). But I wasn't able to find any documentation of that and, because I'm new, I had no idea how to test it. I used your code and did some more research and I have it working now. I had to learn about Workbook_Open, Debug.Print, and a few other things first. For other newbies like me, here is the working code with copious documentation:

Private Function fileopen(file)
    Dim wbk As Workbook
    Dim wsht As Worksheet
    Dim tosht As Worksheet
'   Verify file exists
    If Dir(file) = "" Then
        fileopen = "File '" & file & "' Not Found"
        Exit Function
    End If
'   Open remote file
    Set wbk = Workbooks.Open(file)
    If wbk Is Nothing Then
        fileopen = "File '" & file & "' Failed To Open"
        Exit Function
    End If
'   Open remote file worksheet
    Set wsht = wbk.Worksheets("Sheet1")
    If wsht Is Nothing Then
        fileopen = "Worksheet 'Sheet1' Not Found in '" & file & "'"
        Exit Function
    End If
'   Define where the data should be placed
    Set tosht = ThisWorkbook.Worksheets("Sheet1")
    If tosht Is Nothing Then
        fileopen = "Worksheet 'Sheet1' Not Found in this workbook"
        Exit Function
    End If
'   Copy data from the remote file to this workbook
    tosht.Range("A1:B2").Value = wsht.Range("A1:B2").Value
'   Close the remote workbook without saving it and return
    wbk.Close (False)
    fileopen = "File '" & file & "' Sucessfully Processed " & DateTime.Now
End Function

Private Sub Workbook_Open()
' Code must be located within a Module UNDER ThisWorkbook and not in Module1
' Double click ThisWorkbook to open the code module under it
    Dim msg As String
    ' Run this function without updating the screen so that it runs faster
    Application.ScreenUpdating = False
    ' Turn automatic calculation off while data is being transferred
    Application.Calculation = xlCalculationAutomatic
    ' Run the data transfer function
    msg = fileopen("D:\test.xlsx")
    ' Hit ^G to open the Immediate window which will contain the Debug.Print output
    Debug.Print msg
    ' Save the return message in this worksheet
    ThisWorkbook.Worksheets("Sheet1").Range("A4").Value = msg
    ' Turn automatic calculation back on
    Application.Calculation = xlCalculationAutomatic
    ' Recalculate the workbook
    Calculate
    ' Turn the screen updating back on
    Application.ScreenUpdating = True
End Sub

Workbooks.Open won't open any files

2 Answers2