Criar nova planilha de dados de várias pastas de trabalho do Excel

Question

Em retrospectiva, esta é uma questão de coleta de dados, mas eu quero ver se há uma maneira fácil de fazer isso sem fazer cada arquivo.

Tenho cerca de 350 pastas de trabalho do Excel separadas. Cada pasta de trabalho tem um pouco de informação referente a um indivíduo.

O que eu quero fazer é criar uma simples planilha de todos com os dados necessários, para que seja utilizável.

Para você ter uma ideia. A1 lista "Nome" com B1 tendo o nome da pessoa. A2 tem "Último 4 SSN" e B2 tem os 4 dígitos.

Em seguida, A3 tem a palavra "Ano", depois A4 "2014 est", A5 - "2013", A6 - "2012, etc, até 2009.

Em seguida, na linha 3 de B para N, existem diferentes informações.

O que eu quero fazer, se possível, é ter uma planilha principal com colunas de nome, últimos 4 ssn e anos para cada uma das partes de dados. ie. 2013-juros

Isso é possível? Espero que isso faça sentido.

    

Answer 1

Eu usaria o suplemento de consulta de energia para isso. Você pode iniciar uma consulta de uma tabela do Excel. Se as planilhas estiverem em arquivos separados, terá uma ótima função para importar todos os arquivos em uma pasta do Windows em uma única etapa e anexar todos os seus dados (supondo que as colunas do arquivo sejam consistentes). Se forem várias planilhas em um único arquivo, serão necessárias várias consultas, que podem ser combinadas usando o comando Anexar.

link

Há também um comando Unpivot para transformar dados armazenados em várias colunas em várias linhas - talvez isso resolva seu desafio de vários anos.

    

Answer 2

Você pode mesclar os dados de todas as pastas de trabalho em uma pasta usando o VBA. Se você estiver familiarizado com o VBA, você pode adicionar o seguinte código a uma pasta de trabalho e executá-lo. Ele criará uma nova pasta de trabalho com todos os dados combinados dos arquivos na pasta designada em FolderPath .

Observação: para salvaguardar, seria uma boa ideia fazer uma cópia de todos os arquivos em uma pasta separada enquanto você executa esse código.

Sub MergeAllWorkbooks()
    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim NRow As Long
    Dim FileName As String
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range

    ' Create a new workbook and set a variable to the first sheet. 
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

    ' Modify this folder path to point to the files you want to use.
    FolderPath = "C:\Users\Peter\invoices\"

    ' NRow keeps track of where to insert new rows in the destination workbook.
    NRow = 1

    ' Call Dir the first time, pointing it to all Excel files in the folder path.
    FileName = Dir(FolderPath & "*.xl*")

    ' Loop until Dir returns an empty string.
    Do While FileName <> ""
        ' Open a workbook in the folder
        Set WorkBk = Workbooks.Open(FolderPath & FileName)

        ' Set the cell in column A to be the file name.
        SummarySheet.Range("A" & NRow).Value = FileName

        ' Set the source range to be A9 through C9.
        ' Modify this range for your workbooks. 
        ' It can span multiple rows.
        Set SourceRange = WorkBk.Worksheets(1).Range("A9:C9")

        ' Set the destination range to start at column B and 
        ' be the same size as the source range.
        Set DestRange = SummarySheet.Range("B" & NRow)
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
           SourceRange.Columns.Count)

        ' Copy over the values from the source to the destination.
        DestRange.Value = SourceRange.Value

        ' Increase NRow so that we know where to copy data next.
        NRow = NRow + DestRange.Rows.Count

        ' Close the source workbook without saving changes.
        WorkBk.Close savechanges:=False

        ' Use Dir to get the next file name.
        FileName = Dir()
    Loop

    ' Call AutoFit on the destination sheet so that all 
    ' data is readable.
    SummarySheet.Columns.AutoFit
End Sub

Leia atentamente as notas para modificar as áreas específicas das suas necessidades. Aqui está a fonte desse código se você precisar consultá-lo - Mesclando dados de várias pastas de trabalho em uma pasta de trabalho resumida no Excel 2010 .