Articolo wiki
  • Questo articolo wiki è bloccato
Applicabile a 322 visualizzazioni

Excel - Creare un unico file storico da tanti file - VBA

Livello tecnico : Intermedio

Riepilogo

Spesso nella nostra Community viene chiesto come inserire in un unico file dati che si trovano in altri file. In questo articolo alcuni esempi che risolvono questo problema. Gli esempi sono testati in Excel 2013/2016 ma sono validi per le versioni precedenti.

Prima di chiedere aiuto nella Community, provare e riprovare di adattare gli esempi alle proprie esigenze.

Dettagli

Il contesto.

Abbiamo una cartella che contiene n file di Excel tutti strutturati con un Foglio1 con una tabella di un numero variabile di righe e con lo stesso numero colonne. Un caso tipico può essere una serie di file che mensilmente raccolgono in una tabella i dati del periodo. Quello che vogliamo fare è raccogliere tutti i dati in un unico file.

Nella cartella *dovrebbero* esserci solo file di Excel, ma a questo si può rimediare con il codice escludendo file di altro tipo quando andiamo a ciclare il contenuto della cartella (negli esempi è stata adottata questa soluzione).  L'importante è che nei file di Excel che andremo a considerare ci sia in tutti un Foglio1 (o comunque un foglio con lo stesso identico nome) e che al suo interno ci sia una tabella uguale per struttura (colonne e tipo di dati nelle colonne, le righe possono essere di qualsivoglia numero).

Cosa c'è nei Foglio1 dei file da importare.

Un'immagine vale più di tante parole. Questa la tabella del Foglio1 del File1.xlsx dell'esempio:

Una semplice tabella con 5 colonne e 14 righe. Il numero delle righe può variare da file a file e non è un problema. Per ciò che riguarda le colonne in discorso è più complicato. Anche qui il loro numero può essere diverso dalle 5 dell'esempio, ma deve essere identico in tutti i file e se la colonna C contiene ad esempio la data, in tutti i file la colonna C deve contenere la data. Credo sia ovvio che se così non fosse, il nostro Storico risulterebbe inutilizzabile.

Il codice.

Ecco il codice, commentato, che trovate nel file:

Public Sub m()
   
    'dichiarazioni variabili
    Dim wkMe As Workbook
    Dim wk As Workbook
    Dim shMe As Worksheet
    Dim sh As Worksheet
    Dim sPath As String
    Dim sFileName As String
    Dim lRiga As Long
    Dim rng As Range
   
    'metto un riferimento a questo Workbook
    'e al Foglio Storico
    Set wkMe = ThisWorkbook
    Set shMe = wkMe.Worksheets("Storico")
   
    'metto nella variabile il percorso di questo file
    sPath = wkMe.Path & "\"
   
    'impedisco lo *sfarfallio* del monitor
    Application.ScreenUpdating = False
   
    'ciclo TUTTI i file della Directory
    sFileName = Dir(sPath & "*.xls*")
   
    Do While (Len(sFileName) > 0)
        On Error Resume Next
        'se il nome del file è diverso da quello che contiene il codice
        If sFileName <> wkMe.Name Then
            'metto un riferimento e apro il file
            Set wk = Workbooks.Open(Filename:=sPath _
                & sFileName)
            'metto un riferimento al Foglio1
            Set sh = wk.Worksheets("Foglio1")
            'metto un riferimento al Range del Foglio1 che contiene i dati
            Set rng = sh.Range("A1").CurrentRegion
            'copio i dati ad eccezione della prima riga (di solito è l'intestazione)
            rng.Offset(1, 0).Resize(rng.Rows.Count, rng.Columns.Count).Copy
            'trovo la prima riga vuota della colonna A del Foglio Storico
            lRiga = shMe.Range("A" & Rows.Count).End(xlUp).Row + 1
            'incollo i dati nel foglio Storico
            shMe.Range("A" & lRiga).PasteSpecial
            Application.CutCopyMode = False
            'chiudo il file dal quale ho prelevato i dati
            wk.Close
            'Set a Nothing delle variabili oggetto
            Set sh = Nothing
            Set wk = Nothing
        End If
        sFileName = Dir
    Loop
   
    'visualizzo il risultato della macro
    Application.ScreenUpdating = True
   
    'Set a Nothing delle variabili oggetto
    Set shMe = Nothing
    Set wkMe = Nothing
   
End Sub

Alcune ulteriori note.

Il file Storico_1.xlsm (quello che contiene il codice) deve trovarsi nella stessa Directory dove abbiamo i file con i dati da importare. Se invece volete mettere il file in una Directory diversa dovete modificare la parte in grassetto di questa riga di codice:

sPath = wkMe.Path & "\"

con il percorso della Directory che contiene i file da importare, ad esempio:

sPath = "C:\mioPercorso\miaCartella" & "\"

Qui potete scaricare una Cartella che contiene il file Storico_1.xlsm e alcuni file con dati da importare:

http://www.maurogsc.eu/wiki/Dati_1.zip

Attenzione!!!

Se eseguite più volte la macro, i gli stessi dati verranno incolonnati più volte. Per evitare questo ci sono diverse soluzioni. La prima e più banale è *pulire* il foglio e importare nuovamente i dati. Ma se, ad esempio, di mese in mese vengono aggiunti file alla Directory, una volta che questi diventano molti l'operazione comincia a prendere molto tempo. La seconda soluzione consente di importare solo i dati dei file che sono stati aggiunti dall'ultima esecuzione della macro.

L'altro codice.

Anche questo codice è commentato e lo trovate nel file Storico_2.xlsm.

Public Sub m()
   
    'dichiarazioni variabili
    Dim wkMe As Workbook
    Dim wk As Workbook
    Dim shMe1 As Worksheet
    Dim shMe2 As Worksheet
    Dim sh As Worksheet
    Dim sPath As String
    Dim sFileName As String
    Dim lRiga As Long
    Dim lImportati As Long
    Dim lTot As Long
    Dim rng As Range
   
    'metto un riferimento a questo Workbook
    'e ai Foglio Storico e Importati
    Set wkMe = ThisWorkbook
    Set shMe1 = wkMe.Worksheets("Storico")
    Set shMe2 = wkMe.Worksheets("Importati")
   
    'metto nella variabile il percorso di questo file
    sPath = wkMe.Path & "\"
   
    'impedisco lo *sfarfallio* del monitor
    Application.ScreenUpdating = False
   
    'ciclo TUTTI i file della Directory
    sFileName = Dir(sPath & "*.xls*")
   
    Do While (Len(sFileName) > 0)
        On Error Resume Next
        'se il nome del file è diverso da quello che contiene il codice
        If sFileName <> wkMe.Name Then
            'trovo l'ultima riga con un valore del foglio Importati
            lImportati = shMe2.Range("A" & Rows.Count).End(xlUp).Row
            'controllo che il file non sia già stato importato
            lTot = Evaluate("=COUNTIF(Importati!A2:A" & _
                lImportati & "," & """" & "=" & _
                sFileName & """" & ")")
            'se il file non è stato importato
            If lTot = 0 Then
                'metto un riferimento e apro il file
                Set wk = Workbooks.Open(Filename:=sPath _
                    & sFileName)
                'metto un riferimento al Foglio1
                Set sh = wk.Worksheets("Foglio1")
                'metto un riferimento al Range del Foglio1 che contiene i dati
                Set rng = sh.Range("A1").CurrentRegion
                'copio i dati ad eccezione della prima riga (di solito è l'intestazione)
                rng.Offset(1, 0).Resize(rng.Rows.Count, rng.Columns.Count).Copy
                'trovo la prima riga vuota della colonna A del Foglio Storico
                lRiga = shMe1.Range("A" & Rows.Count).End(xlUp).Row + 1
   
                'incollo i dati nel foglio Storico
                shMe1.Range("A" & lRiga).PasteSpecial
                'scrivo il nome del file importato nel foglio Importati
                shMe2.Range("A" & lImportati + 1).Value = sFileName
                Application.CutCopyMode = False
                'chiudo il file dal quale ho prelevato i dati
                wk.Close
                'Set a Nothing delle variabili oggetto
                Set sh = Nothing
                Set wk = Nothing
            End If
        End If
        sFileName = Dir
    Loop
   
    'visualizzo il risultato della macro
    Application.ScreenUpdating = True
   
    'Set a Nothing delle variabili oggetto
    Set shMe1 = Nothing
    Set wkMe = Nothing
   
End Sub

Alcune note.

Ho aggiunto un foglio a Storico_2.xlsm e l'ho chiamato: Importati. Ogni volta che importo i dati da un file, vado ad aggiungere in colonna A di questo nuovo foglio il nome del file importato. Nel codice, controllo che il suo nome non sia già presente in colonna A con questa istruzione:

lTot = Evaluate("=COUNTIF(Importati!A2:A" & _
                lImportati & "," & """" & "=" & _
                sFileName & """" & ")")

Se lTot è = 0 (quindi non è presente il nome del file in colonna A del foglio Importati) importo i dati. Se viene restituito un valore differente, non importo i dati. Utilizzo il metodo Evaluate per eseguire la funzione COUNTIF (CONTA.SE) e controllare o meno la presenza del nome del file in colonna A, in quanto se i nomi presenti sono molti è più veloce rispetto ad un ciclo.

Se provate a creare nuovi file nella Directory, vedrete che verranno incolonnati solo i dati dei nuovi file e via via nel foglio Importati verranno aggiunti i nome dei file sui quali avete già fatto l'operazione.

Qui potete scaricare una Cartella che contiene il file Storico_2.xlsm e alcuni file con dati da importare:

Note finali.

Come già scritto, se nei file da importare il nome del foglio è diverso da Foglio1, modificate nel codice la parte in grassetto con il nome del vostro foglio:

Set sh = wk.Worksheets("Foglio1")

Se i nomi dei fogli nel file che contiene la macro sono diversi da Storico e Importati, modificate nel codice la parte in grassetto con i nomi dei vostri fogli.

Nell'esempio 1:

    Set shMe = wkMe.Worksheets("Storico")

Nell'esempio 2:

    Set shMe1 = wkMe.Worksheets("Storico")
    Set shMe2 = wkMe.Worksheets("Importati")

Per ulteriori aiuti, dopo avere provato e riprovato, come sempre c'è il Forum di Excel nella nostra (di noi utenti) Community:

Ciao a tutti.

Articoli Wiki simili.

Risorse.


 Avvio Pulito di Windows
(courtesy of Microsoft MVP Franco Leuzzi)

Computer infettato da malware (courtesy of Microsoft MVP Vincenzo Di Russo)

Commenti (0)

avanzamento