return multiple values function-CodePudding

I need to get some information about some files so im trying to build a function that returns 3 information like this:

sub main()
    File = getFile(path) 'future code to list all file information on range

end sub


Function getFile(ByVal path as String)

Dim result() as String
Dim Arquivo as File

Set Folder = FSO.getFolder(Path)

For Each File in Folder.Files
    result(n) = File.Name
    result2(n) = File.DateLastModified
    result3(n) = File.ParentFolder
Next    

getFile = Result(), Result2(),Result3()???

End function

Is it possible to return the 3 file information in a single funcion?

CodePudding user response：

Please, try the next adapted function:

Function getFile(ByVal path As String) As Variant
 Dim fso As Object, folder As Object, file As Object
 Dim arrName, arrDat, arrPar, n As Long

 Set fso = CreateObject("Scripting.FileSystemObject")
 Set folder = fso.GetFolder(path)
 ReDim arrName(folder.files.count - 1)
 ReDim arrDat(folder.files.count - 1)
 ReDim arrPar(folder.files.count - 1)

 For Each file In folder.files
    arrName(n) = file.name
    arrDat(n) = file.DateLastModified
    arrPar(n) = file.parentfolder
    n = n   1
 Next
 getFile = Array(arrName, arrDat, arrPar)
End Function

It can be tested in the next way:

Sub main()
   Dim file As Variant, path As String
   
   path = "the folder path" 'please use here a real path...
   file = getFile(path) 'future code to list all file information on range
    Debug.Print file(0)(0), file(1)(0), file(2)(0)
End Sub

CodePudding user response：

Get File Info

Option Explicit

Sub GetFileInfoTEST()
    
    Const FolderPath As String = "C:\Test"
    Const wsName As String = "Sheet1"
    Const FirstCellAddress As String = "A2"
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim ws As Worksheet: Set ws = wb.Worksheets(wsName)
    
    Dim FileInfo As Variant: FileInfo = GetFileInfo(FolderPath)
    Dim rCount As Long: rCount = UBound(FileInfo, 1)
    If IsEmpty(FileInfo) Then Exit Sub
    
    With ws.Range(FirstCellAddress).Resize(, UBound(FileInfo, 2))
        .Resize(rCount).Value = FileInfo
        .Resize(ws.Rows.Count - .Row - rCount   1).Offset(rCount).ClearContents
        .EntireColumn.AutoFit
    End With
    
End Sub

Function GetFileInfo( _
    ByVal FolderPath As String) _
As Variant

    Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(FolderPath) Then Exit Function
    
    Dim fsoFolder As Object: Set fsoFolder = fso.GetFolder(FolderPath)
    Dim fCount As Long: fCount = fsoFolder.Files.Count
    If fCount = 0 Then Exit Function
    
    Dim Data As Variant: ReDim Data(1 To fCount, 1 To 3)
    
    Dim fsoFile As Object
    Dim r As Long
    
    For Each fsoFile In fsoFolder.Files
        r = r   1
        Data(r, 1) = fsoFile.Name
        Data(r, 2) = fsoFile.DateLastModified
        Data(r, 3) = fsoFile.ParentFolder
    Next fsoFile
    
    GetFileInfo = Data

End Function