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:
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.
- Excel - Dividere una tabella in più fogli nello stesso file- VBA
- Excel - Dividere i fogli di un file in tanti nuovi file, uno per foglio - VBA
- Excel - Dividere una tabella in più file - VBA
Risorse.
Avvio Pulito di Windows (courtesy of Microsoft MVP Franco Leuzzi)
Computer infettato da malware (courtesy of Microsoft MVP Vincenzo Di Russo)