Error 5 (Invalid procedure call or argument)

[Stefan Braem]

Hi

I'm trying to open another workbook based on the day of the week extracted from the date in the file name of the current workbook. This is my code:

Function OpenWerkboek()
    
    Dim Bestand As String
    Dim DatumDag As Long
    Dim DatumMaand As Long
    Dim DatumJaar As Long
    Dim Datum As Date
    Dim Dag As String
    Dim Pad As String
    Dim Werkboek As Workbook
    
    Bestand = ThisWorkbook.FullName

    DatumDag = CLng(Mid(Bestand, Len(Bestand) - 6, -2))
    DatumMaand = CLng(Mid(Bestand, Len(Bestand) - 8, -2))
    DatumJaar = CLng(Mid(Bestand, Len(Bestand) - 12, -4))
    
    Datum = DateSerial(DatumJaar, DatumMaand, DatumDag)
    Dag = Weekday(Datum)
    Pad = "C:\Users\stefa\Downloads\LOGBOEK 2021 (vanaf Juli)\03 WERKROOSTERS\" & Dag & ".xlsm"
    Werkboek = Workbooks.Open(Pad)

End Function

When I let it run, I get an Error 5 (Invalid procedure call of argument) but I can't seem to find out why. The error appears at the line indicated in red. Could someone help me on this?

Thanks in advance!

 

[Fluff]

Hi & welcome to MrExcel.
You cannot use a negative number as the last argument in a Mid function.

You also need to use

Set Werkboek = Workbooks.Open(Pad)

 

[Stefan Braem]

Thanks, it worked!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Fluff]

For future reference.

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Error 5 (Invalid procedure call or argument)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Stefan Braem]

Will do!