Type Mismatch Error

[CoconutP]

I am trying to perform a process for 2 files I am naming specifically. Since there are 2 files, I am declaring parts of the folder names and file names as an array. But I think the mixing of arrays and strings are giving me the Type Mismatch error message. Or my construction below is just completely wrong for looping through multiple files? How should I correct the below? Thanks in advance!

Sub Mgmtaccts()

Dim FullName As Object
Dim FileNames As Object
CurrentFolderYear = Sheet1.Range("B4").Value
CurrentFolderMonth = Sheet1.Range("B5").Value
CurrentFileMonth = Sheet1.Range("B6").Value

Dim EntityName As String, FolderName As String, MgmtAcctsName As String, PathName As String
EntityName = Array("ABC LP", "ABC (GP)")
FolderName = Array("Folder ABC LP", "Folder ABC (GP)")
PathName = "\\Server\Monthly " & CurrentFolderYear & "\" & CurrentFolderMonth & "\Other\" & FolderName
MgmtAcctsName = EntityName & " Management Accounts " & CurrentFileMonth

FullName = PathName & "\" & MgmtAcctsName & ".xlsx"

For Each FullName In FileNames
Workbooks.Open FileName:=FullName

Insert code here

Next FullName

End Sub

 

[Joe4]

You are declaring "FullName" and "FileNames" as Objects, but then you seem to be setting "FullName" equal to a string here:

FullName = PathName & "\" & MgmtAcctsName & ".xlsx"

And you have this line here:

For Each FullName In FileNames

though you have not set "FileNames" equal to anything yet!

 

[rlv01]

Perhaps if you restructured a bit (not tested).

Sub Mgmtaccts()
    Dim FullName As String, MgmtAcctsName As String, PathName As String
    Dim CurrentFolderYear, CurrentFolderMonth, CurrentFileMonth 'previously undefined variables
    Dim I As Long, J As Long
    Dim EntityName As Variant, FolderName As Variant
    
    CurrentFolderYear = Sheet1.Range("B4").Value
    CurrentFolderMonth = Sheet1.Range("B5").Value
    CurrentFileMonth = Sheet1.Range("B6").Value
    
    EntityName = Array("ABC LP", "ABC (GP)")
    FolderName = Array("Folder ABC LP", "Folder ABC (GP)")
    
    With CreateObject("Scripting.FileSystemObject")
        For I = LBound(FolderName) To UBound(FolderName)
            For J = LBound(EntityName) To UBound(EntityName)
                PathName = "\\Server\Monthly " & CurrentFolderYear & "\" & CurrentFolderMonth & "\Other\" & FolderName(I)
                MgmtAcctsName = EntityName(J) & " Management Accounts " & CurrentFileMonth
                FullName = PathName & "\" & MgmtAcctsName & ".xlsx"
                
                If .FileExists(FullName) Then
                    Debug.Print "Open file '" & FullName
                    Workbooks.Open Filename:=FullName
                Else
                    Debug.Print "File '" & FullName & "' does not exist"
                End If
            Next J
        Next I
    End With
End Sub