CODEWITHSUNDEEP
vbaexcel
Rafael Osipov
Rafael Osipov

Reputation: 740

Excel VBA Can't Find a xlsx File in The Folder

Hi I have a code and it pops up a massage box with

run time error 1004

that it can't find the file that I want to open. How can I handle it? I got stuck in it already for hours and I can't find what was going wrong. I want to open an XLSX file that is saved in the same folder with the macro file and they are the only files in the folder, can someone help?

Private Sub cmdStartMonth_Click()
'Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
'Analyze month by selecting
Dim myPath As String
Dim FileName As String
Dim UnionWB As Workbook
Dim MonthName As String
MonthName = ListMonth.Value
myExtension = "*.xlsx*"

    Dim sWrkbkPath As String
    sWrkbkPath = Dir$(ThisWorkbook.Path & "\*.xlsx")

    'Only expecting a single file so no need to loop.
    If sWrkbkPath <> "" Then
        Set UnionWB = Workbooks.Open(sWrkbkPath)
    End If

UnionWB.Worksheets("Union").Range("A1:Y1").AutoFilter
With UnionWB.Worksheets("Union")
        .Activate
        .Range("L1:W1").Find(MonthName, , xlValues, xlWhole).Activate
End With

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

that is the problematic row in the code:

  Set UnionWB = Workbooks.Open(sWrkbkPath)

Upvotes: 0

Views: 3100

Answers (1)

braX
braX

Reputation: 11735

Change this

Set UnionWB = Workbooks.Open(sWrkbkPath)

to this

Set UnionWB = Workbooks.Open(Thisworkbook.Path & "\" & sWrkbkPath)

Upvotes: 2

Related Questions