Results 1 to 8 of 8

Thread: Loop through a folder and find word

  1. #1
    Member
    Join Date
    Nov 2012
    Posts
    47
    Rep Power
    0

    Loop through a folder and find word

    Hello to all.

    My second problem:
    This macro also works perfectly: I open a new workbook (book1), I put my macro button to call after his appearance, immediately began to seek how many workbooks are in a folder where it is located (book1). For example, in a folder Trii / 10 I excel file that shows them to me in column A: A (book1), then in the box izkochiliyat I wanted to write about what I want to look and how to replace it - this is the same as standard option in excel (Find and Replace) but looking in all workbooks and the problem here is that the macro is designed to look at specific cells and truly replaces words.
    So the problem is whether you less to fix it to look absolutely all cells in all worksheets.
    I believe that is a matter of minor fixes somewhere in the code, but because they do not understand, I do not know where to Complete the thing.

    Code:
    Sub general()
    Dim z  As Long, e As Long, g As Long
    Dim f As String, m As String, n As String
    Sheets("Sheet1").Select
    Cells(1, 1) = "=cell(""filename"")"
    Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
    Cells(2, 1).Select
    f = Dir(Cells(1, 2) & "*.xls")
        Do While Len(f) > 0
        ActiveCell.Formula = f
        ActiveCell.Offset(1, 0).Select
        f = Dir()
        Loop
    m = InputBox("Enter search string")
    n = InputBox("Enter replacement string") 'common replacement
    z = Cells(Rows.Count, 1).End(xlUp).Row
        For e = 2 To z
            If Cells(e, 1) <> ActiveWorkbook.Name Then
            Workbooks.Open Filename:=Cells(1, 2) & Cells(e, 1)
                For a = 1 To Sheets.Count
               'n = InputBox("Enter replacement string for sheet -  " & Worksheets(a).Name) ' sheetwise replacement
                x = Worksheets(a).Cells(Rows.Count, 3).End(xlUp).Row
                    For b = 2 To x
                        If InStr(Worksheets(a).Cells(b, 3), m) > 0 Then
                        Worksheets(a).Cells(b, 11) = n
                        End If
                    Next b
                Next a
            ActiveWorkbook.Close True
            End If
        Next e
    MsgBox "collating is complete."
    End Sub
    I want to thank you in advance for all your help.
    Last edited by Admin; 11-29-2012 at 02:18 PM.

  2. #2
    Member
    Join Date
    Nov 2012
    Posts
    47
    Rep Power
    0
    Hi, if he could help with the repair of that code?
    Thank you in advance.

  3. #3
    Member
    Join Date
    Nov 2012
    Posts
    47
    Rep Power
    0
    Hi friends,
    I submitted a working macro that somewhere must be touched to my work. Nobody can respond and help me, it is very difficult to implement??

  4. #4
    Member rollis13's Avatar
    Join Date
    Nov 2012
    Posts
    36
    Rep Power
    0
    Have a try with something like this:
    Code:
    Option Explicit
    Sub general()
        Dim z As Long, e As Long, a As Long, b As Range
        Dim f As String, m As String, n As String
        Sheets("Sheet1").Select
        'Cells(1, 1) = "=cell(""filename"")"      '<=not working
        Cells(1, 1) = "filename"      '<=changed
        'Cells(1, 2) = "=left(A1,find(""["",A1)-1)"      '<=didn't understand
        Cells(1, 2) = "E:\Test\"      '<=to be adjusted
        Cells(2, 1).Select
        f = Dir(Cells(1, 2) & "*.xls")
        Do While Len(f) > 0
            ActiveCell.Formula = f
            ActiveCell.Offset(1, 0).Select
            f = Dir()
        Loop
        m = InputBox("Enter search string")
        n = InputBox("Enter replacement string") 'common replacement
        z = Cells(Rows.Count, 1).End(xlUp).Row
        Application.ScreenUpdating = False      '<=added
         '---test all workbooks found---
        For e = 2 To z
            If Cells(e, 1) <> ActiveWorkbook.Name Then
                Workbooks.Open Filename:=Cells(1, 2) & Cells(e, 1)
                 '---test all sheets within book---
                For a = 1 To Sheets.Count
                    With ActiveWorkbook.Sheets(a)
                         '---test all used cells within sheet---
                        For Each b In .UsedRange.Cells
                            If b.Value = m Then
                                b = n
                            End If
                        Next b
                    End With
                Next a
                ActiveWorkbook.Close True
            End If
        Next e
        '---------------------------------------------------
        Application.ScreenUpdating = True      '<=added
        MsgBox "collating is complete."
    End Sub
    Last edited by rollis13; 12-08-2012 at 03:04 PM.

  5. #5

  6. #6
    Member
    Join Date
    Nov 2012
    Posts
    47
    Rep Power
    0
    Hello rollis13
    Everything works perfectly.
    Now I can do my entire business operation. Thank you very much. infinite
    Be alive and well
    There's been an honest mistake to confuse the names.
    Rollis13 very sorry.
    Yet all thank warmly.
    Regards
    Last edited by k0st4din; 12-09-2012 at 01:04 AM.

  7. #7
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    @ k0st4din, it is rollis13, who replied your post not jazbah.

    @ jazbah, please start a new thread to ask your question.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  8. #8
    Member rollis13's Avatar
    Join Date
    Nov 2012
    Posts
    36
    Rep Power
    0
    Glad being of some help .
    Last edited by rollis13; 09-19-2023 at 02:07 AM.

Similar Threads

  1. Replies: 7
    Last Post: 08-24-2015, 10:58 PM
  2. Replies: 1
    Last Post: 05-21-2013, 11:58 AM
  3. Loop Through And Delete Multiple File Types In A Folder
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 03-30-2013, 04:47 PM
  4. Replies: 1
    Last Post: 10-16-2012, 01:53 PM
  5. Find Parent Folder From Given Folder / File Path
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 05-28-2011, 03:50 PM

Posting Permissions

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