Error On Opening File

[HiSan]

Hi all, I got Run-time error "Sorry, we couldn't find Inventory_SWK - 080319.xlsx. Is it possible it was moved, renamed or deleted?"

Below is my code:

Dim sFileName As String
Dim lRow As Long
Dim wb As Workbook


sFileName = Dir(ThisWorkbook.Path & "\Inventory_SWK*")
Workbooks.Open (sFileName)

This macro is saved together with the file I want to open which is Inventory_SWK - 080319.xlsx. Not sure why macro found this file but says couldn't find in the error message and refuse to open it.

 

[tyija1995]

Hello!

Not sure if this will work but try ThisWorkbook.FullName instead of ThisWorkbook.Path - then remove the & "\Inventory_SWK*" part as .FullName provides that bit as well.

Let me know if that works!

 

[tyija1995]

Just looked into it more, ignore my previous answer as it's incorrect.

Try this:

Sub path()
    Dim sFileName As String
    
    sFileName = ThisWorkbook.path & "\Inventory_SWK*"
    Workbooks.Open (sFileName)
End Sub

 

[RoryA]

Welcome to the forum.

You need to include the path when opening the workbook:

Workbooks.Open ThisWorkbook.Path & "\" & sFileName

 

[HiSan]

Hi tyjia1995, it doesn't work... Method 'Open' of object 'Workbooks' failed

 

[HiSan]

Welcome to the forum.

You need to include the path when opening the workbook:

Workbooks.Open ThisWorkbook.Path & "\" & sFileName

Hi RoryA, It works! Wonder if I have included the path in sFileName, why have to include again in opening workbook?

 

[RoryA]

You didn't include the path. You assigned the result of Dir, which is just the file name, without any path.