CODEWITHSUNDEEP
vbaexcel
EdN
EdN

Reputation: 41

Excel VBA loop folder and check if Excel file name is equal to worksheet

I'm in need of some enlightment. I'm trying to match a folder of Excel files with some sheets in an Excel workbook. So far, I'm able to read these Excel files names and corresponding sheets and copy to them to sheet1 B1 of my workbook. After that I create a sheet for every file.

I would like the macro to continue and compare every file in the directory with the sheets I have in my workbook. If the sheet name from workbook is equal to filename, than copy file contents (only sheet1 from these files has data).

This is what I have so far:

Sub readme()

Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer

Application.ScreenUpdating = False
directory = "D:\Claro Chile\Report_sem_formulas\"
fileName = Dir(directory & "*.xl??")

        Do While fileName <> ""

             i = i + 1
             j = 2
             Cells(i, 1) = fileName
             Workbooks.Open (directory & fileName)

                    For Each sheet In Workbooks(fileName).Worksheets

                    Workbooks("Report Status v1.xlsm").Worksheets(1).Cells(i, j).Value = sheet.Name

                    j = j + 1

        Next sheet

    Workbooks(fileName).Close
    fileName = Dir()

Loop

Application.ScreenUpdating = True
Call create_sheets_starting_from_B1

End Sub

Sub create_sheets_starting_from_B1()

Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Summary").Range("B1")
Set MyRange = Range(MyRange, MyRange.End(xlDown))

    For Each MyCell In MyRange
        Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
        Sheets(Sheets.Count).Name = MyCell.Value 'renames the new worksheet
    Next MyCell

Sheets("Summary").Move Before:=Sheets(1)

End Sub

Upvotes: 0

Views: 4631

Answers (1)

tretom
tretom

Reputation: 635

untested!

but do you might need something like:

Sub sheetCompare()
    Dim i As Integer
    Dim mDirs As String
    Dim path As String
    Dim OutFile As Variant, SrcFile As Variant
    Dim file As Variant

    OutFile = ActiveWorkbook.Name
    mDirs = "c:\" 'your dir here
    file = Dir(mDirs)
    While (file <> "")
        path = mDirs + file
        Workbooks.Open (path)
        SrcFile = ActiveWorkbook.Name

        For i = 1 To Workbooks(OutFile).Sheets.Count
            If file = Workbooks(OutFile).Sheets(i).Name Then
                'copy logic
            End If
        Next i
        Workbooks(file).Close (False)
        file = Dir
    Wend
End Sub

Upvotes: 0

Related Questions