VBA code to open last file in folder not working

[Kra]

Hi all!

I have a macro that opens last modified file with partial name from specific folder. It returns mismatch error on

 Set filToOpen = fil

line and I have no clue why, can somebody explain it please?

Private Sub OpenMaster_Click()

Const sPartialName As String = "Master File"
Dim sStartPath As String
Dim sFileExt As String
Dim fso, fldr, fil, S, myDate, filToOpen As Workbook

sStartPath = "C:\Local\Raports\"
sFileExt = "*.xlsx"
Set fso = CreateObject("Scripting.Filesystemobject")
Set fldr = fso.getfolder(sStartPath).Files
For Each fil In fldr
    If fil.Name Like sPartialName & "*" & sFileExt Then
       If fil.datelastmodified > myDate Then
              myDate = fil.datelastmodified
             Set filToOpen = fil   'This line returns mismatch
       End If
End If
Next fil
If Not filToOpen Is Nothing Then
       Workbooks.Open Filename:=filToOpen.Name
Else
       MsgBox "No Master File found."
End If
End Sub

 

[smozgur]

Following line requires filToOpen is declared as File, or as Object since you are using late binding.

Set filToOpen = fil

However, it is declared as Workbook in the code:

Dim fso, fldr, fil, S, myDate, filToOpen As Workbook

Replace it with Object.

Dim fso, fldr, fil, S, myDate, filToOpen As Object

Not related with your question. However, ideally, also declare fso, fldr, fil variables as Object as well instead of Variant.

 

[Joe4]

Note that this line:

Dim fso, fldr, fil, S, myDate, filToOpen As Workbook

only declare "filToOpen" as Workbook. The rest will be set to the default "Variant".

Each variable must be explicitly declared if you want to ensure they are a certain data type, i.e.

Dim fil as Workbook, filToOpen As Workbook

etc

 

[Kra]

Following line requires filToOpen is declared as File, or as Object since you are using late binding.

Set filToOpen = fil

However, it is declared as Workbook in the code:

Dim fso, fldr, fil, S, myDate, filToOpen As Workbook

Replace it with Object.

Dim fso, fldr, fil, S, myDate, filToOpen As Object

Not related with your question. However, ideally, also declare fso, fldr, fil variables as Object as well instead of Variant.

Thank you, macro runs now without issues

 

[smozgur]

Thank you, macro runs now without issues

Glad to hear it helps.