Results 1 to 2 of 2

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Acolyte gsnidow's Avatar
    Join Date
    Aug 2011
    Location
    Virginia
    Posts
    38
    Articles
    0
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •