Modifying Existing VBA to open file for yesterday not today

[ItalianPlatinum]

Hello - I have current VBA that a user helped me in a prior thread here. It will open a file based off partial parameters then open the most recent file. I have one curve ball now. I need to open the file for the prior business day as well (factoring holidays). The complexity of doing that has me little worried of how to approach. Would someone kindly assist?

Function OpenCopyST() As Workbook
    Dim sPath       As String
    Dim sPartial    As String
    Dim sFName      As String
  
    sPath = "\\XXXXXXXXXXXXXXXXXXXXXXXd\"      ' <<<<< change accordingly
  
    sPartial = "v_j_dist_periodic_" & Year(Now) & IIf(Len(Month(Now)) = 1, "0" & Month(Now), Month(Now)) & IIf(Len(Day(Now)) = 1, "0" & Day(Now), Day(Now)) & "*.txt"
  
    sFName = Dir(sPath & sPartial)
    Dim arr() As Variant, FullName As String, i As Long
    Do While Len(sFName) > 0
        ReDim Preserve arr(i)
        arr(i) = sPath & sFName
        i = i + 1
        sFName = Dir
    Loop
    If i > 0 Then
        FullName = GetMostRecentFileFromArray(arr)

        Dim ErrNum As Long
        On Error Resume Next
        Workbooks.OpenText FullName, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 2)), TrailingMinusNumbers:=True

        ErrNum = VBA.Err.Number
        On Error GoTo 0
        If ErrNum = 0 Then
            Set OpenCopyST = ActiveWorkbook
        End If
    End If
End Function

Public Function GetMostRecentFileFromArray(ByRef argArr() As Variant) As String
    Dim fso As Object, i As Long, arrEntry As Long, oFile As Object, MostRecentFileDate As Double
    Set fso = VBA.CreateObject("Scripting.FileSystemObject")
    For i = LBound(argArr) To UBound(argArr)
        Set oFile = fso.GetFile(argArr(i))
        If oFile.DateLastModified > MostRecentFileDate Then
            MostRecentFileDate = oFile.DateLastModified
            arrEntry = i
        End If
    Next i
    GetMostRecentFileFromArray = argArr(arrEntry)
End Function

 

[GWteB]

so far i am falling down here with a compile error argument not optional

By throwing this error the VBA compiler is telling you two things:
- the code is attempting to invoke a procedure, which procedure accepts an argument;
- that argument is not optional, so at the caller's side a parameter needs to be given on invocation.

The OpenCopyST procedure needs an argument of type Long. When you study my code, you'll see there's a parameter (which you've left out) passed on to that procedure.

 

[ItalianPlatinum]

Apologies in advance I am not quite following I copied the VBA exactly and that public function is there exactly as you show and has (some date). here is the vba maybe it is something I am missing or i now need to do differently? i didnt even try to incorporate the opening of yesterday's file yet was taking it one step at a time. Once opening current day cleared i was going to start with opening yesterday's file

Sub ImportSTFILE()
    Dim UsdRws As Long
    Dim rws As Long
    Dim lr As Long, lr1 As Long, lr2 As Long
    Dim vCols As Variant, vRows As Variant
    Dim i As Long, k As Long
    Dim ErrMsg As String
    Dim ST As Workbook
    Dim WbSPCT As Workbook
    Dim WsDIST As Worksheet, WsFT1 As Worksheet, WsTR1 As Worksheet, WsSEC1 As Worksheet, WsTRF1 As Worksheet, WsDIST1 As Worksheet, WsSPCT As Worksheet, WsSPCT1 As Worksheet, WsRUN As Worksheet, wsRUN1 As Worksheet, wsFOFL As Worksheet, WsOLE As Worksheet, WsDISTR As Worksheet, WsFOFR As Worksheet
    Dim chk
    Dim FOF$
    Dim s As String

'setting ranges etc

        ' the OpenCopyST function does TWO things:
        ' 1. it tries to open a certain workbook of which there could be multiple versions;
        '    get by use of a wildcard the most recent version;
        ' 2. - if the function succeeds a valid reference is returned;
        '    - if the function does NOT succeed the reference would be "Nothing"
   
Set ST = OpenCopyST
   
        ' check whether the OpenCopyST function succeeded
        If SPCT Is Nothing Then
        ErrMsg = ErrMsg & vbCrLf & "ST File NOT FOUND"
    End If
   
        ' opening of workbook succeeded
        'If ST found check if ST file is NOT empty give msg end of VBA if Empty
        If Range("A" & rows.count).End(xlUp).row <= 3 Then
                ST.Close SaveChanges:=False
                ErrMsg = ErrMsg & vbCrLf & "ST File is Empty"
             
    End If
   
    If ErrMsg <> "" Then
        MsgBox ErrMsg
Else

Public Function OpenCopyST(ByVal argSomeDate As Long) As Workbook

    Dim sPath As String, sPartial As String, sFullName As String, SomeDate As Long

    If argSomeDate > 0 Then
        
        sPath = "\\XXXXXXXXXXXXXXXXXXXXXXXd\"      ' <<<<< change accordingly
        sPartial = "v_j_dist_periodic_" & Year(argSomeDate) & IIf(Len(Month(argSomeDate)) = 1, "0" & Month(argSomeDate), Month(argSomeDate)) & IIf(Len(Day(argSomeDate)) = 1, "0" & Day(argSomeDate), Day(argSomeDate)) & "*.txt"

        sFullName = GetMostRecentFileFromWildCardSpec(sPath, sPartial)
        
        If VBA.Len(sFullName) > 0 Then
            Set OpenCopyST = OpenSpecificST(sFullName)
        End If
    End If
End Function

Public Function GetMostRecentFileFromWildCardSpec(ByVal argPath As String, ByVal argFileSpec As String) As String
    Dim FName As String, arr() As Variant, i As Long
    FName = Dir(argPath & argFileSpec)
    Do While Len(FName) > 0
        ReDim Preserve arr(i)
        arr(i) = argPath & FName
        i = i + 1
        FName = Dir
    Loop
    If i > 0 Then
        GetMostRecentFileFromWildCardSpec = GetMostRecentFileFromArray(arr)
    End If
End Function

Public Function OpenSpecificST(ByVal argFullName As String) As Workbook
    Dim ErrNum As Long
    On Error Resume Next
    Workbooks.OpenText argFullName, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
    Array(7, 1), Array(8, 2)), TrailingMinusNumbers:=True
    
    ErrNum = VBA.Err.Number
    On Error GoTo 0
    If ErrNum = 0 Then
        Set OpenSpecificST = ActiveWorkbook
    End If
End Function

Public Function GetMostRecentFileFromArray(ByRef argArr() As Variant) As String
    Dim fso As Object, i As Long, arrEntry As Long, oFile As Object, MostRecentFileDate As Double
    Set fso = VBA.CreateObject("Scripting.FileSystemObject")
    For i = LBound(argArr) To UBound(argArr)
        Set oFile = fso.GetFile(argArr(i))
        If oFile.DateLastModified > MostRecentFileDate Then
            MostRecentFileDate = oFile.DateLastModified
            arrEntry = i
        End If
    Next i
    GetMostRecentFileFromArray = argArr(arrEntry)
End Function

Public Sub Scenario2()

    Dim wb As Workbook, SomeDate As Long
    
    ' == SCENARIO 2 ==

    '- look for the file of the previous day anyway, regardless of its weekday;
    '- if that file does not exist, look for the file of the day before;
    '- repeat this until a file is found;

    ' today's date minus 1 targets yesterday
    SomeDate = VBA.Date - 1

    ' try opening the workbook of yesterday (or a day prior to yesterday)
    Do Until Not wb Is Nothing
        
        ' try opening required workbook and set a reference to it at the same time
        Set wb = OpenCopyST(SomeDate)
        If wb Is Nothing Then
            ' required workbook does not exist
            ' go one more day back
            SomeDate = SomeDate - 1
        Else
            ' process workbook
            ' after processing we're done
        End If
    Loop
End Sub

 

[GWteB]

No worries

What's going on here and what you probably have missed is that the OpenCopyST procedure has been changed several times over the course of several posts (starting with this thread).
At first it was a Sub but it became a Function (with a specific task) that may or may not returned a reference to a workbook object afterwards.
Later on, the task of that procedure was changed slightly and the procedure also expected a mandatory(!!) argument. A little later that changed again because of your additional wishes.

Bottom line is that the entire code has changed and so has its usage, i.e the way the procedures call each other.

 

[ItalianPlatinum]

I agree it has changed for different usages the last thread you shared was to add most recent file. this one is to do the exact same just while keeping existing and adding a new to open yesterday's (prior BD file). ill re-review this thread and see if there is something missing from how i copied over. Seeing from your response it appears that is the more likely case

 

[ItalianPlatinum]

Alright I went back to the basics and traveled to post #2. If I go to post #2 it still doesn't quite work. i get a run time 91 error at this. Still without adding yesterday into the mix.

OpenCopyST_Today = OpenCopyST(sFullName)

in the function OpenCopyST_Today()

 

[GWteB]

As you may have noticed, I'm happy to help you, but you will also have to do something yourself.
At the moment you're just consuming my code without having insight into the difference between a Variable, a Sub and a Function. For instance in your example, you are trying to assign a value to a function (because you are placing the name of the function to the left hand side of the equals sign) which function wasn't developed for that purpose, and also wasn't used that way in the code examples I provided, giving you a run-time error. A variable would be the only suitable way in this case.
Functions mostly do something and then return something, which then can be assigned to a variable. So in code: "Variable = Function()"
Functions (and Subs as well) mostly need some input to work with, so in code: "Variable = Function(input)"

As I'm aware that my educational skills are far below my coding skills I would like to recommend you to do yourself a favor, and use Google to study the above most basic VBA stuff. Forum members like me can code till Doomsday and tailor everything for you, but your response shows me that for now it won't work.

 

[ItalianPlatinum]

The prior code you provided was a function and worked fine opening the today file without issue (previous thread). I could understand if it was failing for the prior business day file but the today file is now inoperable. I am just going to delete everything and revert as now I cannot open today's files that was previously working. Maybe at a later time I'll restart revisit incorporating the prior business day file, for now I can manually support it. Thanks for the effort and apologies if it caused you frustration. I'll consider this thread closed and we can move on.