CODEWITHSUNDEEP
excelvba
Brett Havertz
Brett Havertz

Reputation: 1

Run Time Error 1004 Sorry, we couldn't find *****.xlsx. is it possible it was renamed, moved, or deleted?

I am pretty new to VBA and I am still trying to learn a lot. I keep receiving the following message,

"run time error 1004, sorry, we couldn't find C:\Users\documents.....etc."

I have triple checked my vba code and where my documents are saved but I cant seem to find any solution. THe code is shown below and its the second line that wont run.

Sub Get_Data()

Workbooks.Open "C:\Users\bhavertz\Documents\Call Center ADP\ALC Daily Dispatch Totals.xlsx"

I know this is a simple solution but I cant seem to find any solution. Please help.

Upvotes: 0

Views: 1281

Answers (2)

VBasic2008
VBasic2008

Reputation: 54767

File Not Found

  • For the code not to 'break' when this happens, you can modify your code using one of the following.
  • The first solution is the way to go.
  • The other solutions just show what you can do when there is no 'elegant' way.

The Code

Option Explicit

Sub Get_Data_Dir()
    Const FilePath As String = "C:\Users\bhavertz\Documents\Call Center ADP\" _
        & "ALC Daily Dispatch Totals.xlsx"
    Dim FileName As String: FileName = Dir(FilePath)
    If Len(FileName) = 0 Then
        MsgBox "File not found."
        Exit Sub
    End If
    Dim wb As Workbook: Set wb = Workbooks.Open(FilePath)
    ' Continue...

End Sub

Sub Get_Data_OnError()
    Const FilePath As String = "C:\Users\bhavertz\Documents\Call Center ADP\" _
        & "ALC Daily Dispatch Totals.xlsx"
    On Error GoTo clearError
    Dim wb As Workbook: Set wb = Workbooks.Open(FilePath)
    ' Continue...
    
ProcExit:
    Exit Sub
clearError:
    MsgBox "Run-time error '" & Err.Number & "': " & Err.Description
    Resume ProcExit
End Sub

Sub Get_Data_OnErrorResumeNext()
    Const FilePath As String = "C:\Users\bhavertz\Documents\Call Center ADP\" _
        & "ALC Daily Dispatch Totals.xlsx"
    On Error Resume Next
    Dim wb As Workbook: Set wb = Workbooks.Open(FilePath)
    On Error GoTo 0
    If wb Is Nothing Then
        MsgBox "File not found."
        Exit Sub
    End If
    ' Continue...

End Sub

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

If the file really exists, open it manually in Excel, then press Alt+F11 to bring up the VBE, press Ctrl+G to bring up the immediate pane, and then type this:

?ActiveWorkbook.FullName

Select & copy the resulting output and go back to your code, replace the hard-coded string with the copied file path, expectation being that the hard-coded path/name is somehow different than the actual path/name of the actual file.

Upvotes: 1

Related Questions