Function to read data from a list of closed workbooks(dynamically)

Question

I am recently working with some data analysis in my project. In my case I need to run a VBA that can automatically read the data from a list of closed excel workbook named from 1-80 by ascending order, in which the data i would like to read is store at cell F7 .

That's how the data set looks like

I try to study the threads on internet and i come up with the following "function". It actually works but it doesn't loop according to ascending order.(1,2,3.....9,10,11.......80) Is the excel treat my file name as String instead of numeric value? If yes, how to troubleshoot sorting problems?

Private Sub test()
Dim fso As Object, FolDir As Object, FileNm As Object, Cnt As Integer
On Error GoTo erfix
Set fso = CreateObject("scripting.filesystemobject")
Set FolDir = fso.GetFolder("D:\Data\FYP")
Application.ScreenUpdating = False
For Each FileNm In FolDir.Files
If FileNm.Name Like "*" & ".xls" & "*" Then
Application.DisplayAlerts = False
UpdateLinks = True
Workbooks.Open Filename:=FileNm
Application.DisplayAlerts = True
Cnt = Cnt + 1

ThisWorkbook.Sheets("Sheet1").Range("A" & Cnt).Value = _
      Workbooks(FileNm.Name).Sheets("Sheet1").Range("F" & 7)

Workbooks(FileNm.Name).Close SaveChanges:=False
End If
Next FileNm
Application.ScreenUpdating = True
Set FolDir = Nothing
Set fso = Nothing
Exit Sub
erfix:
On Error GoTo 0
MsgBox "Error"
Application.ScreenUpdating = True
Set FolDir = Nothing
Set fso = Nothing
End Sub

Thank you

The `fileSystemObject` cannot sort the files in any order, see https://stackoverflow.com/questions/16895525/order-of-files-collection-in-filesystemobject — FunThomas, Mar 05 '18 at 17:06

score 0 · Answer 1 · answered Mar 05 '18 at 19:07

I took the code from here and adjusted it

Option Explicit

    Function kc_fsoFiles(theFolder, pattern) As Object

    Dim rsFSO, objFSO, objFolder, File
    Const adInteger = 3
    Const adVarChar = 200

        'create an ADODB.Recordset and call it rsFSO
        Set rsFSO = CreateObject("ADODB.Recordset")

        'Open the FSO object
        Set objFSO = CreateObject("Scripting.FileSystemObject")

        'go get the folder to output it's contents
        Set objFolder = objFSO.GetFolder(theFolder)

        'create the various rows of the recordset
        With rsFSO.Fields
            .append "Name", adVarChar, 200
            ' Field for the "number" part of the file name
            .append "DecName", adInteger
        End With
        rsFSO.Open

        'Now let's find all the files in the folder
        For Each File In objFolder.Files

            'hide any file that begins with the character to exclude
            If File.Name Like pattern Then
                rsFSO.AddNew
                rsFSO("Name") = File.Name
                ' if the basename is not an integer this will pobably crahs
                rsFSO("DecName") = objFSO.getbasename(File.Name)
                rsFSO.Update
            End If

        Next

        'Now get rid of the objFSO since we're done with it.
        Set objFSO = Nothing

        'And finally, let's declare how we want the files
        'sorted on the page. In this example, we are sorting
        'by DecName
        rsFSO.Sort = "DecName ASC "

        'Now get out of the objFolder since we're done with it.
        Set objFolder = Nothing

        'now make sure we are at the beginning of the recordset
        'not necessarily needed, but let's do it just to be sure.
        rsFSO.MoveFirst
        Set kc_fsoFiles = rsFSO

    End Function

If you use this function you will get a list of filenames sorted to your needs

Sub TestIt()
'Now let's call the function and open the recordset
'the folder we will be displaying
Dim strFolder:
strFolder = "...your folder here .."

'the actual recordset we will be creating with the kc_fsoFiles function
Dim rsFSO 'now let's call the function and open the recordset

Set rsFSO = kc_fsoFiles(strFolder, "*xlsx*")

'now we'll create a loop and start displaying the folder
'contents with our recordset. Of course, this is just a
'simple example and not very well formatted, i.e., not in
'a table, but it gets the point across on how you can
'ouput the recordset
  While Not rsFSO.EOF
    Debug.Print rsFSO.Fields("Name").Value
     rsFSO.MoveNext
   Wend

  'finally, close out the recordset
  rsFSO.Close
  Set rsFSO = Nothing
End Sub

Function to read data from a list of closed workbooks(dynamically)

1 Answers1