Lettura e riepilogo dati da cartelle esterne

Ultima modifica: 27-05-2016

 

In questa pagina si parlerà del FileSystemObject che fa parte della libreria VbScript e che ci permette di accedere più agevolmente ai files.

In questo esempio viene usato il FileSystemObject, abbreviato col nome FSO, che fornisce una struttura completa per manipolare, leggere e creare files.

La programmazione nel modello FSO comporta tre operazioni principali:

Per avere a disposizione il ricco bagaglio di metodi e proprietà del FileSystemObject nel nostro progetto VBA è possibile importarne la nutrita libreria scegliendo:
Strumenti => Riferimenti e poi attivando la voce Microsoft Scripting runtime.
In questo modo le classi del VbScript vengono così ad aggiungersi alla classe FileSystem tradizionale già presente nella raccolta delle librerie già disponibili all'interno del VBA.

Tuttavia tale impostazione, anche se raccomandata, non è indispensabile in quanto il VbScript fa già parte di molte delle versioni di Windows

Tra le classi che il FileSystemObject mette a disposizione ce ne sono alcune particolarmente utili nel lavoro che ci accingiamo a fare: L'oggetto File e relativa collection Files, l'oggetto Folder e relativa collection Folders, l'oggetto Drive e relativa collection Drives.

Tra le propretà dell'oggetto FileSystemObject è da segnalare Path che appartiene sia ad un oggetto Drive che ad un oggetto File. Nel primo caso restituisce la directory attiva di un determinato drive, nel secondo caso il percorso del file in esecuzione.

Il lavoro da compiere si svolge in due tempi:

Perciò, per semplificare la lettura e la comprensione del codice parto con due frammenti di codice che mostrano i due passaggi e poi il codice integrale coi due passaggi assemblati in una unica routine.

 

Leggere i files di una cartella col FileSystemObject

Iniziamo quindi con un semplice esempio. Con questo primo esempio ci limitiamo a leggere i files contenuti nella cartella assegnata alla variabile Src.

Le cartelle sul disco sono cosi disposte:

Il lavoro inizia, col primo blocco di codice, con l'individuare il percorso della cartella nella quale stiamo lavorando e quella dove sono depositati gli altri files di Excel ottenendo una cosa del genere:

D:\lavoro\Riepilogo: il percorso del file Excel con cui stiamo lavorando

D:\lavoro\PRATICHE: il percorso dove si trovano gli altri files Excel con cui intendiamo lavorare

Col secondo blocco del codice si passa poi al lavoro che ci preme portare avanti in questo tutorial.

Col terzo blocco vengono mostrati i files trovati

L'ultimo blocco distrugge tutti gli oggetti creati precedentemente per liberare le risorse impegnate.

 

Quel che ci interessa di tutta questa routine è quel che si trova nel terzo blocco.

Innanzitutto con la funzione CreateObject() viene fissato nella variabile "fso" l'oggetto FileSystemObject.

Analogamente a questa azione con la funzione GetFolder() viene impostato e memorizzato come oggetto Folder nella variabile "oFolder" il percorso su cui intendiamo lavorare.

L'ultimo passaggio da compiere è quello di memorizzare l'insieme Files racchiusi in "oFolder" e metterli a loro volta nella variabile matrice "oFiles"

Compiute queste tre operazioni abbiamo i files disponibili e possiamo lavorarci come da progetto.

In questo caso viene eseguito un semplice debug stampando semplicemente nella Finestra Immediata dell'editor VBA l'elenco dei files trovati.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Sub elenco_files()
Dim Src As String
Dim fso As Object
Dim oFolder As Object, oFiles As Object, oFile As Object
Dim i As Integer, X As Integer
Dim PratFold, Parts, tmpvar
'   =======================================================
Sheets("Foglio2").Select
PratFold = ActiveWorkbook.Path
Parts = Split(PratFold, "\")
For X = 0 To UBound(Parts) - 1
    Src = Src & Parts(X) & "\"
Next
Src = Src & "PRATICHE"
'   =======================================================
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(Src)
Set oFiles = oFolder.Files
tmpvar = oFiles.Count
Debug.Print tmpvar
'   =======================================================
For Each oFile In oFiles
    Debug.Print oFile
Next
'   =======================================================
Set fso = Nothing
Set oFolder = Nothing
Set oFiles = Nothing
End Sub

Aprire e leggere un file Excel

Per aprire e leggere un file Excel l'operazione è più semplice.

Nel settore della dichiarazione delle variabili occorre istanziare una nuova Application con New Excel.Application e relativo oggetto Workbook con Book As Excel.Workbook.

Quindi, una volta definito il percorso ed il nome del file da aprire lo si assegna all'oggetto Workbook appena creato con Set Book = App.Workbooks.Add(NomeFile).

Da questo momento il file è aperto e quindi fruibile.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Sub Apri_file()
Dim App As New Excel.Application, Book As Excel.Workbook
Dim PratFold, Parts, X, Src, NomeFile
Sheets("Foglio2").Select
'   =======================================================
PratFold = ActiveWorkbook.Path
Parts = Split(PratFold, "\")
For X = 0 To UBound(Parts) - 1
    Src = Src & Parts(X) & "\"
Next
Src = Src & "PRATICHE"
NomeFile = Src & "\01.xlsx"
'   =======================================================
App.Visible = False
Set Book = App.Workbooks.Add(NomeFile)
With Book.Worksheets(1)
    X = .UsedRange.Rows.Count
End With
'   =======================================================
Book.Close SaveChanges:=False
App.Quit
Set Book = Nothing
End Sub

Unire le due routines per ottenere un'applicativo più complesso e completo

Nell'esempio finale posso finalmente usare, sebbene con piccole ovvie modifiche, le due routines appena viste.

Anche questa routine, per una più facile comprensione del codice, è stata suddivisa in blocchi.

Il primo ed il secondo blocco sono simili a quelli già visti più sopra e, nella zona della dichiarazione delle variabili, le due dichiarazioni già viste in precedenza:

Dim App As New Excel.Application, Book As Excel.Workbook

che serve per preparare una nuova istanza dell'applicazione Excel vuota

Entrando nel terzo blocco viene fatto un controllo preliminare per verificare che nel percorso indicato siano stati trovati dei files.

Se i files sono stati trovati viene eseguito il codice scritto nel blocco, altrimenti, dopo un messaggio di avviso, si passa al quarto blocco che chiude gli oggetti usati per liberare le risolte impegnate.

In questo terzo blocco si inizia col referenziare l'intervallo nel quale si intende trasferire i dati ponendo la sua origine nel Range("A2"). Quindi si cancellano i dati eventualmente già presenti per prepararsi ad accogliere i nuovi.

Per fare in modo che un eventuale errore non interrompa bruscamente l'esecuzione del codice, s'inserisce l'istruzione On Error Resume Next all'inizio delle istruzioni critiche per chiuderlo alla fine con l'istruzione On Error GoTo 0.

 

Col ciclo For Each viene spazzolata la matrice consegnata dall'istruzione Set oFiles = oFolder.Files del secondo blocco, quindi si lavorerà con tutti i flies trovati, ma uno alla volta.

Viene controllata l'estensione del file, usando il FileSystemObject con l'istruzione fso.GetExtensionName(oFile), e se questa è xlsx si continua col lavoro.

All'oggetto Workbook creato nella zona della dichiarazione delle variabili viene aggiunto il file corrente letto dal ciclo con l'istruzione App.Workbooks.Add(oFile).

A questo punto il nuovo file è aperto, anche se invisibile, ed è possibile lavorare tranquillamente con questo.

Nel codice che segue viene semplicemente copiata la prima riga del file aperto, ma si possono compiere qualsiasi tipo di operazioni.

La cosa importante da tener presente è che non dobbiamo confondere i riferimenti al file principale, che è il file che contiene la routine, e quello aperto.

In questo esempio i riferimenti fatti con l'intervallo, denominato appunto Intervallo, si riferiscono al file attivo, mentre i riferimenti fatti tramite .Range() o .Cells() si riferiscono al file esterno mantenuto attivo dall'istruzione With.

Perciò:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
Sub copia_dati()
Dim fso As Object
Dim oFolder As Object, oFiles As Object, oFile As Object
Dim App As New Excel.Application, Book As Excel.Workbook
Dim Src As String, Parts() As String
Dim Intervallo As Range
Dim Riga, Colonna, R, ColEst, C1
Dim PratFold As String, sheet_original As Worksheet, Sheet_Destination As Worksheet
Dim FileName, tmpvar, X
Dim Ext As String
'   BLOCCO 1 =======================================================
PratFold = ActiveWorkbook.Path
Parts = Split(PratFold, "\")
For X = 0 To UBound(Parts) - 1
Src = Src & Parts(X) & "\"
Next
Src = Src & "PRATICHE"
Set Sheet_Destination = ThisWorkbook.Sheets("Foglio1")
Sheet_Destination.Activate
'   BLOCCO 2 =======================================================
'   con la funzione CreateObject() viene fissato nella variabile "fso" l'oggetto FileSystemObject
'   con la funzione GetFolder() viene impostato e memorizzato come oggetto Folder nella variabile "oFolder" il percorso su cui intendiamo lavorare
'   l'insieme Files racchiusi in "oFolder" viene memorizzato nella variabile matrice "oFiles"
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(Src)
Set oFiles = oFolder.Files
tmpvar = oFiles.Count
'   BLOCCO 3 =======================================================
'   se sono stati trovati dei files si continua con questo blocco di codice
If tmpvar > 0 Then
    Set Intervallo = Range("A2")
    Range("A2").CurrentRegion.ClearContents
    Application.ScreenUpdating = False
    On Error Resume Next
    R = 0
    '   START COPIA da file esterno =======================================================
    For Each oFile In oFiles
        R = R + 1
        Ext = fso.GetExtensionName(oFile)
        If Ext = "xlsx" Then
            App.Visible = False
            Set Book = App.Workbooks.Add(oFile)
            With Book.Worksheets(1)
                '   la With controlla la cartella esterna che si sta leggendo
                '   Intervallo si riferisce alla cartella "riepilogo"
                ColEst = .Range("A1").CurrentRegion.Columns.Count
                For C1 = 1 To ColEst
                    Intervallo(R, C1) = .Cells(1, C1)
                Next
            End With
            Book.Close SaveChanges:=False
            App.Quit
            Set App = Nothing
        End If
    Next
    '   FINE COPIA da file esterno =======================================================
    On Error GoTo 0
    Application.ScreenUpdating = True
Else
    MsgBox "Nessun file trovato"
End If
'   BLOCCO 4 =======================================================
Set fso = Nothing
Set oFolder = Nothing
Set oFiles = Nothing
End Sub

Questo è tutto per ora