Preluarea datelor din mai multe fișiere Excel

Spre deosebire de exemplul în care datele pot fi citite dintr-un registru de lucru fără a-l deschide, aici exemplul este extins și explică cum pot fi preluate date din mai multe fișiere Excel sau workbooks pentru a îmbina datele într-un singur fișier Excel folosind VBA.

Să presupunem că sunt două fișiere Excel (fișiere sursă) care conțin date despre vânzări. Toate fișierele (sau registrele de lucru) au aceeași structură de date. Vreau să extrag toate datele din fișierele sursă și să îmbin datele într-un singur fișier (master). Vedeți imaginea alăturată.

Numele fișierelor sursă poate fi oricare. Însă fișierele sursă trebuie să se afle în același folder, în care să nu se afle și alte fișiere.

Fișierul Master, în care vor fi centralizate datele din fișierele sursă trebuie să se afle în altă locație. După rularea macrocomenzii, poate fi mutat în același folder.

Codul VBA (Macro)

În fișierul Master trebuie să fie scrisă macrocomanda (codul VBA) care va extrage datele din fișierele sursă și le va scrie în fișierul Master.

În fișierul Master poate fi adăugat și un buton (un control ActiveX). Cu click pe buton se declanșează macrocomanda de preluare date din folderul cu fișiere sursă.

Codul VBA poate fi copiat de pe pagina https://www.encodedna.com/excel/read-multiple-excel-files-and-merge-data-to-single-file-using-vba.htm și ar trebui să preia toate datele din fișierele sursă și să le adauge în fișierul în care se execută macrocomanda (Master). 

Option Explicit
 
Private Sub CommandButton1_Click()
    ReadAndMerceData
End Sub
 
Sub ReadAndMerceData()
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    
    ' Obiectele File System.
    Dim objFs As Object
    Dim objFolder As Object
    Dim file As Object
    
    Set objFs = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFs.GetFolder("D:\somefolder\sample")       ' aici se scrie calea la folderul in care se afla fisierele sursa.
    
    ' Randul de inceput. 
        ' Aceasta variabila este actualizata dupa ce ajunge la ultimul rand din fisierul anterior.
    Dim iStartRow As Integer
    iStartRow = 0
    
    ' Parcurge toate fisierele din folder.
    For Each file In objFolder.Files
        
        Dim src As Workbook         ' Fisierul sursa, din care se preiau informatii.
        Set src = Workbooks.Open(file.Path, True, True)
        
        Dim iTotalRows As Integer   ' Preia numărul total de randuri utilizate în fisierul sursă.
        iTotalRows = src.Worksheets("sheet1").UsedRange.Rows.Count
        
        Dim iTotalCols As Integer   ' Preia numărul total de coloane utilizate în fisierul sursă.
        iTotalCols = src.Worksheets("sheet1").UsedRange.Columns.Count
 
        Dim iRows, iCols As Integer
        
        ' Acum, citeste fisierul sursa și copie datele in fisierul master.
        For iRows = 1 To iTotalRows
            For iCols = 1 To iTotalCols
                Cells(iRows + iStartRow, iCols) = src.Worksheets("Sheet1").Cells(iRows, iCols)
            Next iCols
        Next iRows
        
        iStartRow = iRows + 1
        iRows = 0
    
        src.Close False
        Set src = Nothing
    Next
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

În exemplul de mai sus, a fost folosit modelul FSO din VBA (sau File System Object) pentru a accesa fisierele dintr-un folder. FSO furnizează instrumente bazate pe obiecte pentru lucrul cu fișiere și foldere. Aceasta este una dintre cele mai ușoare căi de a cunoaște totul despre fișierele dintr-un dosar.

Se creează un obiect al FSO.

Set objFs = CreateObject("Scripting.FileSystemObject")

Apoi, un obiect care citește foldere.

Set objFolder = objFs.GetFolder("D:\somefolder\sample")

După cum am scris mai sus, numele fișierelor poate fi oricare. Însă structura fiecărui fișier trebuie să fie aceeași.

Se parcurg toate fișierele din folder. Folosind calea către fișier, se deschide fiecare fișier - workbook (fișier Excel) unul câte unul pentru a citi datele din el.

Set src = Workbooks.Open(file.Path, True, True)

După deschiderea fișierului sursă, se preiau numărul total de rânduri și coloane din fișier. Se folosește proprietatea UsedRange. Astfel, nu se mai parcurg inutil toate rândurile și coloanele din fișierul sursă.

iTotalRows = src.Worksheets("sheet1").UsedRange.Rows.Count
iTotalCols = src.Worksheets("sheet1").UsedRange.Columns.Count

Asta e tot. Acum se citesc valorile din fiecare celulă a fișierului sursă și se scriu în fișierul Master.