Reading excel files name and one cell of information in every excel files

Harshinee · Jan 4, 2014

Dear All,
Nice meeting you all, i am quite new to VB.So hope you all give me some guidance to solve this problem.
I am creating an excel file to read all the excel files name in the format of (YYYYMMDD = date format) for eg 20131112 and displaying them in the first column.The second task would be displaying an information from cell U50 from each of the files and display them in the 2th column according to their file name. I have attached 2 files for reference purpose but I have 29 files all together.Thank you.

gsnidow · Jan 6, 2014

Harshinee, here is a basic example of one way to loop through files in a folder to get some data out of them. Keep in mind, if you are going to be looping through files other people can affect (mess up through user error), you will have to put in a lot more robust error checking. I did not do that here, because there are countless ways something can go wrong, so you will have to figure out what those ways are. Anyhow, this ought to get you started. Also, when you say you are "new to VB", I am assuming you know how to open the VBE and paste in some code?

Greg

Code:

Sub LoopThroughFiles()
    Dim colFiles As Collection
    Dim strDir As String, strFile As String
    Dim i As Long
    Dim WBFrom As Workbook
    Dim WS As Worksheet
    
    Application.ScreenUpdating = False
    
    Set WS = ActiveWorkbook.ActiveSheet
    
    Set colFiles = New Collection
    
    strDir = "c:\test\" ' Change to suit your needs
    ' Here we are going to loop through all the files in your folder, and look for ones
    ' fitting your criteria.  When one if found, we will add the file name to a collection.
    ' You could just as well forget the collection, and do all your processing in one step,
    ' but I like to keep things separate for troubleshooting.
    strFile = Dir$(strDir, vbDirectory)
    Do While Len("" & strFile) > 0
    strFile = Dir$
        If strFile <> "." And strFile <> ".." And Len(strFile) <> 0 Then
            ' Here you can put any kind of logic you like to ensure you only open the files you want.
            ' Of course, you can save yourself a lot of hastle if you just ensure you only have the
            ' files you want in your folder.
            If strFile Like "*.xlsx" Then   ' If its an Excel file
                If IsNumeric(Mid(strFile, 1, 8)) Then   ' If the file name is numeric
                    If IsDate(Mid(strFile, 5, 2) & "/" & Mid(strFile, 7, 2) & "/" & Mid(strFile, 1, 4)) Then ' If the file name is a date
                        colFiles.Add strFile
                    End If
               End If
            End If
        End If
    Loop
    
    ' Now we will open each file, get cell U50, and write it to the current
    ' worksheet.  Again, you could put all this in the above loop if you
    ' wanted to, but I would keep it separate if I were you.
    For i = 1 To colFiles.Count Step 1
        Set WBFrom = Workbooks.Open(strDir & "\" & colFiles.Item(i))
        WS.Cells(i, 1).Value = colFiles.Item(i)
        WS.Cells(i, 2).Value = WBFrom.Sheets("Report").Range("U50")
        WBFrom.Close
    Next i
   
    Application.ScreenUpdating = True
    
End Sub

Reading excel files name and one cell of information in every excel files

Harshinee

New member

Attachments

gsnidow

New member