Run Time 1004 - Cant locate the file to open

[Razor_Rob]

Hi

Ive got a VBA code that opens a file where the user chooses a file

 Dim fileNameAndPath As Variant
    fileNameAndPath = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSX), *.XLSX", Title:="Select File To Be Opened")
    If fileNameAndPath = False Then Exit Sub
    'Workbooks.Open Filename:=fileNameAndPath
    Set wbCopy = Workbooks.Open(fileName:=fileNameAndPath, ReadOnly:=True)
    Set wsCopy = wbCopy.Worksheets(1)

and another code to open another workbook on a set path

Dim ACpath As String
    Dim ACname As String
    Dim fullACname As String
    Dim fExt As String
            
    ACpath = "\\foldername\different folder\Macro\"
    ACname = "Availability Codes"
    fExt = "xlsx"
    fullACname = Dir(ACpath & ACname & "*.*.*." & fExt)
    Set wbAvailabilityCodes = Workbooks.Open(fullACname)    
    Set wsAvailabilityCodes = wbAvailabilityCodes.Worksheets(1)

Now it gives an Error 1004 stating that the file is not there. If the user selects a file in the same folder as the set path the code works fine. It only errors if the user selects a different path for the 1st code.

The error highlights this code

Set wbAvailabilityCodes = Workbooks.Open(fullACname)

 

[Tetra201]

Try inserting a ChDir statement before calling the Dir function:

...
ChDir ACpath
...
fullACname = Dir(ACpath & ACname & "*.*.*." & fExt)
...

 

[Razor_Rob]

Thanks!

 

[Razor_Rob]

Can I add something to this to improve the logic better.

How do I change the code so it looks for the latest file in the folder?
ie
Availability Codes 26.10.22.xlsx
Availability Codes 26.09.22.xlsx
Availability Codes 02.08.22.xlsx