Runtime error '1004'

[Pookiemeister]

I am not sure why this code does not worked because I used a similar variation in another project and it worked. I checked the file location of the file and path needing to be opened and its correct. Not sure why its not finding the workbook. The error occurs on the

Set wb = Workbooks.Open(sFile)

Here's the rest of the code. Thank you.

Option Explicit


'Declares variable names
Dim sPath As String, sFile As String
Dim wb As Workbook
Dim ws As Worksheet


Private Sub Workbook_Open()
    
    'Declares variable names
    Dim ans As String
    Dim ansSplit As String
    Dim i As Integer
    
    sPath = "C:\Users\Desktop\Bulk_Yield_Report\"
    
    sFile = sPath & "F-103-04 Exh E-Bulk MT-Pkg Prod Theoretical Yield Report-Rev004.xls"
    
    Set wb = Workbooks.Open(sFile)
    
    
    ans = MsgBox("Is this a split Lot?", vbQuestion + vbYesNo, "Split Lot")
    
    If ans = vbNo Then
        frmPackageYield.Show vbModeless
    Else
        ansSplit = InputBox("How many times does this lot split?", "Split Lot")
        For i = 1 To ansSplit
            frmPackageYield.Show 'vbModeless
        Next i
        'NEEDS CODING HERE
    End If
        
    Call ClearContents
End Sub

 

[mole999]

when you step through and before set wb. what is held in the sfile string

 

[Pookiemeister]

I set up a watch and and put sPath and sFile to see what the value would be and the code below came from the watch for the expression "sFile". Thank you for your quick response.

"C:\Users\Desktop\Bulk_Yield_Report\F-103-04 Exh E-Bulk MT-Pkg Prod Theoretical Yield Report-Rev004.xls"

 

[Pookiemeister]

Ok, I figured it out. When I was sent the file from my supervisor, he put the file location and name of the file in an email, along with the file attachment. So I copied and pasted the file name and location and created a location on my desktop to simulate a server location. Anyway, he accidentally forgot the last letter x in the file extension. So instead of name being "F-103-04 Exh E-Bulk MT-Pkg Prod Theoretical Yield Report-Rev004.xls", it is actually "F-103-04 Exh E-Bulk MT-Pkg Prod Theoretical Yield Report-Rev004.xlsx". One hour gone. Thank you.

 

[mole999]

lots still using 2003. so it didn't look wrong

 

[Pookiemeister]

I agree.