Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Search Files in Folder

    Hello everyone,

    My spreadsheet has names of files listed in column A and i want a macro to search for that files in folder and found search result in column B 'Yes' 'No'.

    Part 2 The search files are of text format ".txt" is it possible from macro to read file and state the number of records/lines found in each files are there. Any helps in this.

    Sheet1
    Col A -----------Col B-------Col C
    test.txt_______Yes_______10 records
    sample.txt____ Yes_______50 records
    code.txt_______Yes_______69 records
    snippet.txt_____No_______00 records

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Zmagic,

    Welcome to the lounge as a new poster!

    See if this does what you want.
    Code:
    Option Explicit
    
    Sub ProcessFiles()
    
       Dim lCntr       As Long
       Dim lRecCnt     As Long
       Dim zDir        As String
       Dim zFileToFind As String
       Dim zFound      As String
       
       Application.ScreenUpdating = False
       zDir = [B1].Value
       [A4].Select
       lCntr = 0
       
       Do
         zFileToFind = zDir & ActiveCell.Offset(lCntr, 0).Value
         zFound = Dir(zFileToFind)
         If zFound <> "" Then
    '       MsgBox "File: " & zFileToFind, _
    '            vbOKOnly + vbInformation, "Status:"
         
           Workbooks.OpenText Filename:= _
             zFileToFind, StartRow:=1, DataType:=xlDelimited, _
             TextQualifier:=xlDoubleQuote, _
             ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
             Comma:=False, Space:=False, Other:=False, _
             FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
            
           Rows(Rows.Count).End(xlUp).Select
           lRecCnt = ActiveCell.Row
       
           ActiveWorkbook.Close
       
           With ActiveCell
               .Offset(lCntr, 1).Value = "Yes"
               .Offset(lCntr, 2).Value = lRecCnt
           End With 'Activecell
           
         Else
            With ActiveCell
               .Offset(lCntr, 1).Value = "No"
               .Offset(lCntr, 2).Value = 0
           End With 'Activecell
          
         End If
         
         lCntr = lCntr + 1
         
       Loop While ActiveCell.Offset(lCntr, 0).Value <> ""
       
    End Sub
    FileInfo.JPG

    Note: The record count is the number of Excel rows it finds in the file. My test files were VBA code saved as text and included blank lines which were counted as records. If you don't want blank lines more code is required.

    SearchFilesGetRecords.xlsm

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    zmagic (2013-10-17)

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here is an alternate approach that does not open the text files in excel, it just reads them and counts the lines.

    Code:
    Option Explicit
    Sub GetTxtInfo()
      Dim sPath As String
      Dim sFile As String
      Dim sInput As String
      Dim lRow As Long
      Dim x As Long
      Dim rCell As Range
      Dim lFileNum As Long
      
      sPath = "C:\MyPath\" 'change as desired
      lRow = 1 'starting row
      Set rCell = Cells(lRow, 1)
      
      Do While rCell <> "" 'Stop at first blank entry in Col A
        sFile = Dir(sPath & rCell) 'look for file
        If sFile = "" Then 'File not found
          rCell.Offset(0, 1) = "No"
        Else 'file found
          rCell.Offset(0, 1) = "Yes"
          lFileNum = FreeFile
          Close lFileNum
          Open sFile For Input As lFileNum
          x = 0
          Do While Not EOF(lFileNum) 'count the lines
            Line Input #lFileNum, sInput
            x = x + 1
          Loop
          rCell.Offset(0, 2) = x
          Close lFileNum
        End If
        lRow = lRow + 1 'next row
        Set rCell = Cells(lRow, 1)
      Loop
    End Sub
    Steve

  5. #4
    Lounger
    Join Date
    Oct 2013
    Posts
    37
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks steve for your script, I tried it gives run time error '53' on this line "Open sFile For Input As lFileNum"

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    That means the file was not found. What is the value of sFile when you get the error? Is it is a valid file?

    Steve

Posting Permissions

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