Operazioni su cartelle di lavoro in sequenza

Operazioni su cartelle di lavoro in sequenza: aprirle, salvarle, copiare dati, incollarli in una terza cartella. - dal 04/09/04 pagina vista: volte

Ovvero: aprire tutti i file xls di una cartella, uno alla volta, crearne una copia salvandola in un'altra cartella, copiare dei dati di un foglio della cartella aperta, in un foglio di un terzo file xls che funga da archivio.

Una precisazione per evitare malintesi: purtroppo si chiamano "Cartelle" sia le Directory del nostro hard-disk (che sono dei "contenitori", non sono files, ed in inglese si chiamano "Folder"), sia i file di Excel che in italiano sono "Cartelle di lavoro", e che in inglese si chiamano "Cartel". Nell'articolo quindi chiamerò "cartelle" le directory, e "file" le cartelle di lavoro di Excel.

Un esercizio interessante nato da una specifica esigenza, ma che potrà interessare anche perchè non è molto difficile. Vediamo di esaminare la situazione, che sembrerebbe complessa, ma non lo è:

Supponiamo di avere una cartella, che per comodità chiameremo "Sorgente", sul nostro hard-disk. La cartella contiene più files .xls che contengono dati, ognuno con un nome diverso. Vogliamo creare un ciclo che ci consenta di aprire tutti i files, uno alla volta e richiuderlo, in sequenza.
Di ogni file che apriremo, vogliamo crearne una copia, salvandola in un'altra cartella, che chiameremo "Destinazione". Inoltre del file aperto, vogliamo prendere dei dati che sono contenuti in una zona di un foglio di lavoro, e copiare questi dati in un foglio di un terzo file.xs, che chiameremo Archivio.xls, inserendo i dati, ed accodando i dati del foglio successivo che apriremo, proprio per creare un archivio totale dei dati provenienti da tutti i files che saranno aperti.

Il file Archivio.xls sarà il file dal quale lanciare la routine, e questo file potrà risiedere dove vorremo; noi supponiamo che si trovi in una terza cartella del nostro hard-disk.

Come è impostata la procedura: Apriremo per primo il file Archivio.xls e sullo stesso foglio di lavoro dove incolleremo i dati, useremo un pulsante al quale associare la macro, per lanciarla. Vediamo subito le istruzioni, in verde le spiegazioni: Presento tre procedure, la prima, questa sotto, cercherà tutti i file con estensione .xls presenti nella cartella "Sorgente", la seconda cercherà tutti i file .xls che corrispondino ad un nome file progressivo, quindi basto sulla progressione che avrà il nome, la terza cercherà i file basandosi sulla loro data di creazione o di modifica.

Sub CreaArchivio()
Application.ScreenUpdating = False
'questo sopra evita il saltellamento a schermo durante l'apertura, il salvataggio e la copia

'sotto: impostiamo la variabile "fs" usando l'oggetto FileSearch (cerca file), che con la sua 'proprietà LookIn imposta la cartella in cui deve essere eseguita la ricerca di file specificata con 'Filename
Set fs = Application.FileSearch
With fs
.LookIn = "C:\Sorgente" 'indichiamo la cartella in cui cercare
.Filename = "*.xls" 'indichiamo l'estensione dei file da cercare all'interno della cartella

'sotto: impieghiamo il metodo Execute (già spiegato nell'articolo "Cercare l'ultimo file" in questa 'stessa sezione) senza argomenti, in pratica dice: se l'esecuzione della ricerca file risulta maggiore 'di zero, quindi esiste un certo numero di file, allora:
If .Execute() > 0 Then
'sotto: si inizia il ciclo che parte dal primo file ordinato per SortByName e terminerà all'ultimo 'valore rappresentato dal totale dei file presenti contati con FoundFiles.Count.
For i = 1 To .FoundFiles.Count
X = .FoundFiles(i) 'X sarà uguale al percorso completo del file in quel momento letto

'sotto: poichè vorremo salvare detto file in un'altra cartella, ed X rappresenta il percorso completo 'del file, quindi con unità e cartella, abbiamo bisogno di estrarre il solo nome del file da usare poi 'nel SaveAs, sfruttiamo il metodo GetFile che restituisce il nome del File corrispondente al file di 'un percorso specificato (X).
Dim f
Set fn = CreateObject("Scripting.FileSystemObject")
Set nome = fn.Getfile(X)
f = nome.Name 'f sarà quindi solo il nome del file senza il path

'sotto: ora possiamo aprire il file usando Open Filename indicando il percorso completo (X)
Workbooks.Open Filename:=X
'sotto: si crea una copia salvando il file in quel momento aperto e quindi attivo, indicando il 'percorso completo formato dall'unità hard-disk, dalla cartella di destinazione, e dal nome 'rappresentato dalla variabile "f" che è completo di estensione
ActiveWorkbook.SaveAs Filename:="C:\Destinazione\" & f & "", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'sotto: attenzione: il file aperto è ancora il file attivo, si seleziona quindi il foglio dal quale vogliamo 'copiare dei dati (io ho usato come esempio il Foglio1)
Worksheets("Foglio1").Select
Range("A2:D20").Select 'si seleziona l'area dati (ognuno deciderà la propria area, magari usando 'altri sistemi tipo UsedRange o CurrentRegion, o ancora definire un'area con Cells + End, ecc. 'ecc.. Questo consentirebbe la libertà di prelevare dati in zone non necessariamente uguali tra un 'file di origine ed un altro, anche se il nome del foglio dovrà essere necessariamente uguale su tutti i 'files)

Selection.Copy 'si copia la selezione così impostata

ActiveWorkbook.Close 'si chiude il file attivo che è quello che è stato appena aperto
'si attiva il foglio Archivio che era già aperto (visto che abbiamo lanciato da qui la macro)
Workbooks("Archivio.xls").Activate

'si seleziona in foglio nel quale vorremo incollare i dati che sono stati appena copiati e che sono 'ancora in memoria anche se si è chiuso il file sorgente
Worksheets("Foglio1").Select

'iniziamo un ciclo While..Wend per trovare la prima cella libera a partire dalla riga 2 e dalla 'colonna 1 (la A)
Dim iRow As Integer
iRow = 2
While Cells(iRow, 1).Value <> ""
iRow = iRow + 1
Wend

'trovata la riga libera incolliamo i dati che sono in memoria
Cells(iRow, 1).Select
ActiveSheet.Paste Destination:=Worksheets("Foglio1").Cells(iRow, 1)

Next 'si passa ad aprire il file successivo che si trova nella cartella Sorgente, fino all'ultimo
End If
End With
ActiveWorkbook.Save 'si salva la cartella Archivio che è il file ora attivo
'ActiveWorkbook.Close 'questo solo se vorremo chiudere ANCHE la cartella Archivio
End Sub

Sicuramente il vba ci consente di seguire altre strade nella compilazione di istruzioni per ottenere gli stessi risultati; questi comunque sono un sistema pratico e sufficientemente veloce, dipenderà comunque dal numero di file da aprire e dalla quantità di dati da copiare.

La routine sopra richiede che una volta eseguito il salvataggio e la copia dei dati che interessano, archiviandoli, si cancellino i file che sono stati già trattati in quanto un successivo lancio della macro, riaprirebbe li stessi file già aperti provocando due effetti indesiderati: una duplicazione dei dati archiviati, ed un errore di run-time perchè il metodo SaveAs si rifiuta di salvare un file con lo stesso nome di uno già esistente. Oppure i file già trattati possono essere spostati in un'altra cartella. (ma non esiste già una cartella con la copia degli originali ??). Sarà poi possibile usare delle variabili per reperire i nomi delle cartelle sia dei file da aprire, sia dove salvare.

Variante2: La routine sotto invece, simile alla precedente, ma con pochissime modifiche, ci consente di memorizzare il nome dell'ultimo file aperto(copiato, salvato), in modo che ad un successivo lancio della macro, vengano aperti, copiati, salvati, solo i file più recenti, anzi, meglio dire: i file successivi al nome dell'ultimo file trattato in precedenza. In questo caso non sarà necessario eliminare i file già trattati, dalla cartella "Sorgente", in quanto verranno aperti solo i successivi.

Unico accorgimento da tenere sarà quello di mantenere una progressione numerica nel nome dei file che terremo nella cartella "Sorgente", : esempio fatt001.xls, fatt002.xls, fatt003.xls ecc..

Per le modifiche useremo una cella del foglio di lavoro del file Archivio.xls (io ho usato la H1 come esempio), dove verrà memorizzato il nome dell'ultimo file aperto, inseriamo un controllo condizionale If : se il nome del file letto nella cartella sorgente sarà minore o uguale al nome registrato in H1, si passerà al successivo tramite Next, altrimenti verrà eseguita l'apertura del file, il suo salvataggio, la copia dei dati e l'incollaggio sul file Archivio. Vediamo la routine, in grassetto evidenzio le modifiche rispetto alla precedente:

Sub CreaArchivioSuNome()
Application.ScreenUpdating = False
'questo sopra evita il saltellamento a schermo durante l'apertura, il salvataggio e la copia

ind = [H1].Value 'con "ind" prendiamo il nome del file scritto nella cella H1 (non 'dimentichiamo che ci troviamo già sul foglio1 del file Archivio).
'sotto: impostiamo la variabile "fs" usando l'oggetto FileSearch (cerca file), che con la sua 'proprietà LookIn imposta la cartella in cui deve essere eseguita la ricerca di file specificata con 'Filename
Set fs = Application.FileSearch
With fs
.LookIn = "C:\Sorgente" 'indichiamo la cartella in cui cercare
.Filename = "*.xls" 'indichiamo l'estensione dei file da cercare all'interno della cartella

If f <= ind Then GoTo 10 'se "f", cioè il nome letto nel momento dal ciclo, è minore o 'uguale al nome rappresentato da "ind", si passa alla riga numero 10, cioè Next, 'saltando le istruzioni sottostanti
'sotto: ora possiamo aprire il file usando Open Filename indicando il percorso completo (X)
Workbooks.Open Filename:=X
'sotto: si crea una copia salvando il file in quel momento aperto e quindi attivo, indicando il 'percorso completo formato dall'unità hard-disk, dalla cartella di destinazione, e dal nome 'rappresentato dalla variabile "f" che è completo di estensione
ActiveWorkbook.SaveAs Filename:="C:\Destinazione\" & f & "", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'si seleziona in foglio nel quale vorremo incollare i dati che sono stati appena copiati e che sono 'ancora in memoria anche se si è chiuso il file sorgente
Worksheets("Foglio1").Select

[H1] = f 'si segna in H1 il nome dell'ultimo file aperto rappresentato da "f"

'iniziamo un ciclo While..Wend per trovare la prima cella libera a partire dalla riga 2 e dalla 'colonna 1 (la A)
Dim iRow As Integer
iRow = 2
While Cells(iRow, 1).Value <> ""
iRow = iRow + 1
Wend

'trovata la riga libera incolliamo i dati che sono in memoria
Cells(iRow, 1).Select
ActiveSheet.Paste Destination:=Worksheets("Foglio1").Cells(iRow, 1)

10: 'indice riga
Next 'si passa ad aprire il file successivo che si trova nella cartella Sorgente, fino all'ultimo
End If
End With
ActiveWorkbook.Save 'si salva la cartella Archivio che è il file ora attivo
'ActiveWorkbook.Close 'questo solo se vorremo chiudere ANCHE la cartella Archivio
End Sub

Variante 3: questa ultima procedura, che si basa sulla ricerca dei file in base alla loro data di creazione o di modifica, richiede delle istruzioni leggermente diverse dalla precedente: useremo ancora una cella sul Foglio1 del file Archivio (la H1) per memorizzare la data relativa all'ultimo file aperto, ma avremo bisogno di reperire la data di creazione o modifica del file, oltre al suo nome. Per reperire questa data useremo la Funzione FileDateTime , che restituisce un valore Variant (Date) che indica la data e l'ora in cui un file è stato creato o modificato.

Poichè esistono possibilità di errore nella gestione di data e ora (pensiamo ad esempio all'apertura indipendente di un file già precedentemente salvato e copiato, ma che si apri per consultazione e senza averci apportato modifiche lo si salvi comunque in uscita. Windows registra il nuovo orario come orario di modifica, e se lanciassimo la nostra macro verrebbe inevitabilmente caricato perchè la data sarebbe quella del giorno, ma l'orario lo renderebbe più recente rispetto alla data e ora registrate in H1, con i disagi già detti).

Per limitare queste possibilità di errori, useremo quindi la Funzione Mid che reperirà solo la data dal valore restituito dalla Funzione FileDateTime, e su quella eseguiremo il controllo con la sola data che avremo registrato in H1. Vediamo la procedura, in grassetto le varianti:

Sub ArchivioSuDataModificaDelFile()
Application.ScreenUpdating = False
'questo sopra evita il saltellamento a schermo durante l'apertura, il salvataggio e la copia

indu = Mid([H1], 1, 8) 'con "indu" tramite Mid prendiamo la sola data (dell'ultimo file 'aperto) scritta nella cella H1 (non dimentichiamo che ci troviamo già sul foglio1 del file 'Archivio). Ho considerato la data in formato breve es. 10/11/03 e non 10/11/2003. Questa impostazione del formato data dipende dalle impostazioni del formato data/ora del pannello di controllo, e NON dal formato cella.

'sotto: con FileDateTime reperiamo la data e l'ora del file ora letto e reso con X, e 'l'assegniamo alla variabile "fd"

fd = FileDateTime(X)

'ora estraiamo la sola data da fd, tramite Mid, e l'assegniamo alla variabile "ff"
ff = Mid(fd, 1, 8)

'sotto: ora confrontiamo "ff" con la data scritta in h1: se è inferiore o uguale passiamo 'al file successivo con GoTo che ci porta all'indice riga 10 e quindi a Next
If ff < indu Or ff = indu Then GoTo 10

'si seleziona in foglio nel quale vorremo incollare i dati che sono stati appena copiati e che sono 'ancora in memoria anche se si è chiuso il file sorgente
Worksheets("Foglio1").Select

[H1] = ff 'si registra in H1 la data dell'ultimo file aperto rappresentato da "ff"

'iniziamo un ciclo While..Wend per trovare la prima cella libera a partire dalla riga 2 e dalla 'colonna 1 (la A)
Dim iRow As Integer
iRow = 2
While Cells(iRow, 1).Value <> ""
iRow = iRow + 1
Wend

'trovata la riga libera incolliamo i dati che sono in memoria
Cells(iRow, 1).Select
ActiveSheet.Paste Destination:=Worksheets("Foglio1").Cells(iRow, 1)

Se togliete i commenti, vedrete che le istruzioni non sono poi molte, come preannunciato.

Buon lavoro.

prelevato sul sito www.ennius.altervista.org