目录的VBA代码和粘贴到主表 [英] VBA code for Directory and Paste to Master Sheet

问题描述

所以我有以下宏，它从工作簿的C列提取了多个工作表的唯一值，并将其粘贴到新页面。我确实意识到他们是另一个类似的问题，但我不明白。有没有办法：

So i have the following macro, which extracts unique values from column C of a workbook with multiple sheets and pastes it to a new page. I do realize their is another question similar, but I do not understand it. Is there a way to:

1）在文件目录中执行此操作？

1) do this amongst a directory of files?

2）把新的值为主表，而不是在每个文件中制作新的表：

2) put new values into a master sheet instead of just making a new sheet in each file:

 Sub extractuniquevalues()
Dim wks As Excel.Worksheet
Dim wksSummary As Excel.Worksheet
'----------------------------------------------------------------------------------

On Error Resume Next
Set wksSummary = Excel.ThisWorkbook.Worksheets("Unique data")
On Error GoTo 0

If wksSummary Is Nothing Then
    Set wksSummary = Excel.ThisWorkbook.Worksheets.Add
    wksSummary.Name = "Unique data"
End If


'Iterate through all the worksheets, but skip [Summary] worksheet.
For Each wks In Excel.ActiveWorkbook.Worksheets

    With wksSummary

        If wks.Name <> .Name Then
            If Application.WorksheetFunction.CountA(wks.Range("C:C")) Then
                Call wks.Range("C:C").AdvancedFilter(xlFilterCopy, , .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row + 1, 1), True)
            End If
        End If

    End With

Next wks

 End Sub

任何帮助将非常感激，谢谢。

Any help would be Greatly Appreciated, Thanks.

推荐答案

您的两个请求都可以完成：（见我的评论）

Both of your requests can be done: (See my comments)

Sub Main()
    'Turn off alerts like "Do you really want to quit?"
    Application.DisplayAlerts = False

    Call LoopThroughDirectory("D:\Private\Excel\", "*.xls*")

    'Turn alerts on
    Application.DisplayAlerts = True
End Sub

Sub LoopThroughDirectory(dirPath As String, filter As String)
    Dim filename
    'Loop throug all of the files in the given directory
    filename = Dir(dirPath & filter)
    Do While Len(filename) > 0
        ' Filename variable contains the name of the file in the directory
        ' (dirPath & Filename) will be the full path to the file

        ' Lets call here another Sub which will open up workbooks for us.
        OpenAnotherWorkbook (dirPath & filename)

        'Move on to the next file
        filename = Dir
    Loop
End Sub

Sub OpenAnotherWorkbook(filePath As String)
    'Your master workbook to copy to
    Dim master_wb As Workbook
    Set master_wb = ThisWorkbook

    'Your source workbook to copy from
    Dim source_wb As Workbook
    Set source_wb = Application.Workbooks.Open(filePath)

    'Call your subroutine
    Call YourSub(master_wb, source_wb)

    'Close source workbook after everything is done
    source_wb.Close
End Sub

Sub YourSub(master_wb As Workbook, source_wb As Workbook)
    ' Do your stuff here
    '   For example:

    'Find your master sheet
    Dim master_ws As Worksheet
    Set master_ws = GetOrCreateWorksheet(master_wb, "YourSheetName")

    Dim source_ws As Worksheet
    Set source_ws = source_wb.Sheets(1)

    'Lets save some data from the another workbook to the master one.
    Dim lastRowNo As Integer
    lastRowNo = master_ws.UsedRange.Rows.Count
    'If lastRowNo is 1 that means the worksheet is empty or only the headers had been initialized
    If lastRowNo = 1 Then
        'Create headers for the columns
        master_ws.Cells(lastRowNo, 1).Value = "Workbook"
        master_ws.Cells(lastRowNo, 2).Value = "Worksheet"
    End If
    'Give some value to the next empty row's first and second cell
    'Source workbook's name
    master_ws.Cells(lastRowNo + 1, 1).Value = source_wb.Name
    'Source worksheet's name
    master_ws.Cells(lastRowNo + 1, 2).Value = source_ws.Name 

End Sub

Function GetOrCreateWorksheet(wb As Workbook, wsName As String) As Worksheet
    Dim ws As Worksheet
    'Loop through each sheet to find yours
    For Each ws In wb.Sheets
        If ws.Name = wsName Then
            'If found return with it
            Set GetOrCreateWorksheet = ws
            Exit Function
        End If
    Next ws

    'If not exists, create one and return with it
    Set ws = wb.Sheets.Add
    ws.Name = wsName
    Set GetOrCreateWorksheet = ws
End Function

这篇关于目录的VBA代码和粘贴到主表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！