CODEWITHSUNDEEP
vbaexcel
pineappleorangebanana
pineappleorangebanana

Reputation: 31

Excel 2016 Macro Unable to Find File : Runtime Error 1004

I am running into issues while attempting to open an additional Excel file using VBA in Excel 2016. It matters not whether the file is in the same directory. I am thinking it has something to do with a default setting in Excel 2016 that is blocking the search? The Macro functioned in Excel 2010.

Private Sub CommmandButton_Click()
Dim source As String
Dim temp As Workbook

source = InputBox("Enter source")

Set temp = Workbooks.Open(source)

end sub

Upvotes: 3

Views: 522

Answers (2)

Taylor Raine
Taylor Raine

Reputation: 578

Here is an example solution using the FileDialog object

Private Sub CommmandButton_Click()
    Dim fDialog As FileDialog, _
        wb      As Excel.Workbook
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    With fDialog
        .AllowMultiSelect = False
        .Title = "Select a file"
        .InitialFileName = "C:\"
        .Filters.Clear
        ' Prevent Error by specifying that the user must use an excel file
        .Filters.Add "Excel files", "*.xlsx,*.xls,*.xlsm"
    End With
    If fDialog.Show = -1 Then
       Set wb = Excel.Workbooks.Open(fDialog.SelectedItems(1))
    Else 
        End  ' Cleanly exit the Macro if the user cancels
    End If

End Sub

Upvotes: 3

Gary's Student
Gary's Student

Reputation: 96753

You are allowing users to do more than point and click. This is asking for trouble. Instead:

Private Sub CommmandButton_Click()
Dim source As String
Dim temp As Workbook

source = Application.GetOpenFilename()

Set temp = Workbooks.Open(source)

end sub

This code can be further enhanced to:

1. pre-select the initial path
2. set the file-type(s)
3. give guidance
4. gracefully handle cancellations

Upvotes: 1

Related Questions