Sub SplitFile(FileName As String)
    Debug.Print (FileName)
End Sub


Sub AliorRead()
Dim PATH As String
PATH = ThisWorkbook.Sheets("SET").Range("A2")
Dim FileList As Variant
FileList = Worksheets("SET").ListObjects("Pliki").DataBodyRange
Dim i As Integer
Dim FileName As String
For i = LBound(FileList) To UBound(FileList)
    FileName.Value = FileList(i)
    SplitFile (FileName)
Next i

End Sub

I'm basically learning (trying to) VBA. But I'm still getting error for Sub AliorRead.

Updated code, now produced type mismatch errors:

Option Explicit
Sub Alior_Read_MT940()
Dim PATH As String
PATH = ThisWorkbook.Sheets("SET").Range("A2").Value
Dim FileList() As Variant
FileList = Worksheets("SET").ListObjects("Pliki").DataBodyRange
Dim i As Integer
For i = LBound(FileList, 1) To UBound(FileList, 1)
    Call SplitFile(FileList(i, 1), PATH)
Next i

End Sub

Sub SplitFile(FileName As String)
    Debug.Print (FileName)
End Sub


Sub AliorRead()
Dim PATH As String
PATH = ThisWorkbook.Sheets("SET").Range("A2")
Dim FileList As Variant
FileList = Worksheets("SET").ListObjects("Pliki").DataBodyRange
Dim i As Integer
Dim FileName As String
For i = LBound(FileList) To UBound(FileList)
    FileName.Value = FileList(i)
    SplitFile (FileName)
Next i

End Sub

I'm basically learning (trying to) VBA. But I'm still getting error for Sub AliorRead.

Updated code, now produced type mismatch errors:

Option Explicit
Sub Alior_Read_MT940()
Dim PATH As String
PATH = ThisWorkbook.Sheets("SET").Range("A2").Value
Dim FileList() As Variant
FileList = Worksheets("SET").ListObjects("Pliki").DataBodyRange
Dim i As Integer
For i = LBound(FileList, 1) To UBound(FileList, 1)
    Call SplitFile(FileList(i, 1), PATH)
Next i

End Sub

• excel
• vba

• You're trying to assign a value to FileName using .Value when FileName is declared as a String. Use FileName = FileList(i). – Lewis Commented yesterday
• Try FileName = FileList(i) – ValNik Commented yesterday
• Ok, I'm getting rid of qualifier error with FileName = FileList(i), but now I'm getting types mismatch errors. At least now I understand what happens. – Greg_em Commented yesterday
• Which column of the table Pliki do you want ? If first then use FileName = FileList(i,1) you could just use SplitFile FileList(i,1) – CDP1802 Commented yesterday
• Why added () to Dim FileList() ? Did you change Sub SplitFile for 2 parameters ? Which line has error – CDP1802 Commented 22 hours ago

2 Answers 2

Option Explicit

Sub Alior_Read_MT940()
    Dim FileList() As Variant, path As String, i As Long

    With ThisWorkbook.Sheets("SET")
        path = .Range("A2").Value
        FileList = .ListObjects("Pliki").DataBodyRange.Value
   
        For i = LBound(FileList, 1) To UBound(FileList, 1)
            Call SplitFile(CStr(FileList(i, 1)), path)
        Next i
    End With
    
End Sub

Sub SplitFile(FileName As String, FilePath As String)
    Debug.Print FileName, FilePath
End Sub

or alternatively

Sub Alior_Read_MT940()
    Dim path As String, r As ListRow

    With ThisWorkbook.Sheets("SET")
        path = .Range("A2").Value
        For Each r In .ListObjects("Pliki").ListRows
           Call SplitFile(r.Range(1), path)
        Next
    End With
End Sub

(1) Your original error (already solved in the comments) was caused by FileName.Value. In your code, FileName is of type String, and strings in VBA are skalar values (simple values), not objects (like in other languages). A skalar has no properties or methods. FileName.Value would refer to the property Value of an object (eg a Cell or a Range). For a skalar value, you use simply the variable name:

FileName = FileList(i)

(2) Next thing might be a little bit confusing, you need to understand the idea of data type Variant. A variant can hold anything: A number, a date, a String, an object reference... It can also contain an array of anything, including an array of Variants.

You now write

FileList = Worksheets("SET").ListObjects("Pliki").DataBodyRange

DataBodyRange is an Excel Range, and reading the range like this will always return a 2-dimensional array of data (except if the range contains only one cell, but let's ignore this for now). Using DataBodyRange like this is, by the way, a shortcut for DataBodyRange.Value, it will return a 2-dimensional array of the content of the Range. As a cell can contain different data types, the result is a 2-dimensional array of type Variant. You can assign this 2-dimensional array to a single variant variable, but you can't assign it to an array of variant - if you do, you get the type mismatch error. So use

Dim FileList As Variant
FileList = Worksheets("SET").ListObjects("Pliki").DataBodyRange.Value 

(3) Next problem is your call to the sub SplitFile. This sub expects a String as parameter, but you pass FileList(i, 1). As explained, FileList now holds an array of Variant. It might contain a string, but it might contain anything else - the compiler doesn't know and complains with an error ByRef type mismatch. Three possibilities to solve that:

• Explicitly convert the Variant to a String using the function CStr:

  SplitFile CStr(FileList(i, 1)

• Change the definition of your sub so that it accepts a Variant as Parameter:

  Sub SplitFile(FileName As Variant)

• Change the way the parameter is passed from ByRef to ByVal. Now the VBA runtime can convert the Variant implicitly into a String.

  Sub SplitFile(ByVal FileName As String)

(4) You have another issue: You pass 2 parameters to the Sub, but the Sub expects only 1 parameter. Depending on your needs, either change the definition of the sub so that it expects 2 parameters, or leave it as it is and remove the second parameter (PATH) from your call.

By the way, there are 2 ways to call a Sub in VBA. The meaning is identical, only the syntax is different. Assuming you changed your Sub to expect 2 parameters, it can be called with either of the 2 following statements:

SplitFile FileList(i, 1), PATH
Call SplitFile(FileList(i, 1), PATH)

It is sometimes stated that Call is deprecated, but it's perfect valid VBA. However, don't use the syntax you have used in your first example:

SplitFile (FileName)    ' Don't use this!

There, the parenthesis have a complete different meaning and have nothing to do with the call of a routine.

(5) Not an issue here, but forget about data type Integer in VBA and always use Long. An integer can contain only values from -32.768 to +32.767, sooner or later you will hit a case where you get an "Overflow" runtime error. Use Long to avoid that. And no, Integers neither saves memory nor are they faster.