Merge multiple excel sheets into one

Question

Hey there, guys. So, I have a folder on my computer where several spreadsheets are played and I need the spreadsheet1.xlsm, worksheet2.xlsm, worksheet3.xlsm... but they are together from other worksheets because this folder on the computer is a public folder. Multiple files are played there so you run the risk of me unintentionally importing those files together.

From this I need to gather the data of a certain tab of these worksheets. Example, I have 3 tabs in each but all have the same columns and the same tabs, only different data.

When I import I need to play on a tab only one under the other.

Another point is that these files are always updated but I need to keep data from old tables + the new one by putting down...

How do I get these specific files taking into account that these spreadsheets are always updated? I’m not sure how to start. I found this code on the Internet, because I will use vba:

Option Explicit
 
Private Function ListaArquivos(ByVal Caminho As String) As String()
 
'Atenção: Faça referência à biblioteca Micrsoft Scripting Runtime
Dim FSO As New FileSystemObject
Dim result() As String
Dim Pasta As Folder
Dim Arquivo As File
Dim Indice As Long
 
ReDim result(0) As String
If FSO.FolderExists(Caminho) Then
    Set Pasta = FSO.GetFolder(Caminho)
 
    For Each Arquivo In Pasta.Files
      Indice = IIf(result(0) = "", 0, Indice + 1)
      ReDim Preserve result(Indice) As String
      result(Indice) = Arquivo.Name
    Next
End If
 
ListaArquivos = result
ErrHandler:
    Set FSO = Nothing
    Set Pasta = Nothing
    Set Arquivo = Nothing
End Function
 
Public Sub UnirTodos()
On Error GoTo trata_saida:
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Dim arquivos() As String
    Dim lCtr As Long, processados As Long
    arquivos = ListaArquivos(ThisWorkbook.Path)
    For lCtr = 0 To UBound(arquivos)
        If ValidaNomeArquivo(arquivos(lCtr)) Then
            'Debug.Print arquivos(lCtr)
            Call UnirAoArquivo(arquivos(lCtr))
            processados = processados + 1
        End If
    Next
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    MsgBox processados & " arquivos processados"
trata_saida:
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub
 
Private Function ValidaNomeArquivo(ByVal nomeArquivo As String) As Boolean
    Dim result As Boolean
    result = InStr(1, nomeArquivo, ThisWorkbook.Name, vbTextCompare) = 0
    If result Then
        result = result Or Right(nomeArquivo, 4) = ".xls"
        result = result Or Right(nomeArquivo, 4) = "xlsx"
        result = result Or Right(nomeArquivo, 4) = "xlsm"
    End If
    ValidaNomeArquivo = result
End Function
 
Private Sub UnirAoArquivo(ByVal nomeArquivo As String)
On Error GoTo trata_erro_uniraoarquivo
    Dim wb As Workbook, ws As Worksheet, mySheet As Worksheet, rngCopy As Range
    Set wb = Workbooks.Open(ThisWorkbook.Path & "\" & nomeArquivo, ReadOnly:=True)
    Set ws = wb.Worksheets(1)
    Set mySheet = ThisWorkbook.Worksheets(1)
    
    'seleciona a regiao com conteudo
    Set rngCopy = ws.Range(ws.Cells(2, 1), ws.Cells(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count))
    'copia
    rngCopy.Copy
    'cola no destino
    With mySheet
        Call .Paste(.Cells(.UsedRange.Rows.Count + 1, 1))
    End With
    
    wb.Close
trata_saida_uniraoarquivo:
    Set wb = Nothing
    Set ws = Nothing
    Exit Sub
trata_erro_uniraoarquivo:
    GoTo trata_saida_uniraoarquivo:
End Sub

This code is not specifying which files to grab... Someone to give me a light?

Answer 1

Hello.

I believe you can use Powerquery to join this content. I’ll leave an example here to help you adapt to your need.

1. In a new excel, go to import data from "Folder":

2. Select the folder path in the next. screen go to "Match and Transform":

3. This screen will give you an example of what excel did in the folder, it is only an initial example, don’t worry if it doesn’t show all the files or data here. Click on Plan1 (your name) and "OK".

4. Now we are in a new window, which is from Microsoft’s Powerquery, research more about it if you want to delve deeper or better understand how it works. See that it is an "extra" window to excel. Here I made an example filtering only the files "Plan01.xlsx" and "Plan02.xlsx", see that we have the "Plan03.xlsx", but it will be filtered in the next screen. In the right side panel, you see the steps that Powerquery has taken, see that it now has a "Filtered Lines" step (the lines were actually files, as it is a list of files).

5. After finishing all the filters/settings you need to stay only the files you want to consolidate, click "Close and Load":

6. Ready, now you have consolidated file data as a single table. When you want to update, click "Update".

Edit: The sample files have this content each:

I hope I helped. Check if the answer helped you. :)