Domanda:

Sono alla ricerca di una macro vba che mi permetta di andare a leggere dei dati su foglio 1 di excel di varie cartelle di una cartella denominata Magazzino

Magazzino
/abete/1098.xls/foglio1
/abete/1099.xls/foglio1
/acero/2097.xls/foglio1
/acero/2120.xls/foglio1
ecc.

Dovrei sommare in un nuovo foglio i valori contenuti nei vari foglio1 dell’elenco ad albero delle celle: G8, K8. M8, per poter fare la somma dei mq in giacenza, dei mq venduti e del valore rimasto.
Il magazzino contiene più di 400 fogli suddivisi in circa 160 cartelle.

Risposta:

Per risolvere la necessità del lettore ho creato la macro “AggiornaRiepilogo”
che si lancia con il tasto “Aggiorna Tutto” presente nella cella “F2”.

Come prima cosa bisogna inserire nel foglio “Config” cella “A2” il percorso di rete della cartella “Magazzino”.
All’apertura del file d’esempio il percorso configurato è quello del mio PC
ed un controllo sull’esistenza della cartella Magazzino richiederà di aggiornarlo inserendo il percorso dove avrete salvato i file d’esempio.

Ho utilizzato il FileSystemObject attraverso il quale con un ciclo For Next passo in tutte le sottocartelle contenute nella cartella “Magazzino” e per ogni file in esse presente estraggo per prima cosa i dati per popolare la tabella del Ripilogo:

– dal nome della sottocartella ho ricavato il tipo di Articolo

Sheet1.Cells(r, 1) = Right(fos, Len(fos) - Len(Percorso) - 1)

– dal nome del file ho ricavato il Codice

Sheet1.Cells(r, 2) = Left(f1.Name, Len(f1.Name) - 4)

– dalla data di ultima modifica del file la data di ultima modifica

Sheet1.Cells(r, 6) = f1.DateLastModified

Ho utilizzato la proprietà “.Path” dei singoli file per ricavare il nome del file comprensivo di percorso
da inserire negli Hyperlink posti sotto la colonna “Dettaglio”

ActiveSheet.Hyperlinks.Add Anchor:=Sheet1.Cells(r, 7), Address:=f1.Path, TextToDisplay:= "APRI FILE"

In questo modo si potranno aprire I file per verificare i dati riportati o modificarli, senza andarli a cercare.

La valorizzazione dei dati relativi a  “mq in giacenza “ , “mq venduti” e”valore  residuo”
viene effettuata aprendo ciascun file

Workbooks.Open (f1.Path)

e copiandone i valori delle celle che ci interessano

Sheet1.Cells(r, 3) = ActiveWorkbook.Sheets("Foglio1").Range("G8")
Sheet1.Cells(r, 4) = ActiveWorkbook.Sheets("Foglio1").Range("K8")
Sheet1.Cells(r, 5) = ActiveWorkbook.Sheets("Foglio1").Range("M8")

Alla fine dell’operazione chiudo il file aperto

ActiveWorkbook.Close

Posto che la numerosità dei file da analizzare usati nella creazione del codice è assai inferiore a quella reale (16 file contro più di 400)
ho voluto evitare la brutta sensazione di rimanere lunghi minuti davanti ad uno schermo vuoto senza sapere a che punto è la lavorazione.

All’apertura del file, la macro pubblica “ContaTotaleFile” valorizza la variabile “TotaleFile” con il numero totale presenti nella cartella “Magazzino”

Tale variabile è resa pubblica inserendo all’inizio del modulo “Conta_TotaleFile” la dichiarazione

Public TotaleFile As Integer

Il suo valore è riportato nello Sheet “Riepilogo” nella cella “J2”.
In questo modo è possibile utilizzarla durante l’esecuzione della macro “AggiornaRiepilogo”.
Al suo interno ho creato la variabile “FileAnalizzati” , dichiarata come numero ed inizializzata:

Dim FileAnalizzati As Integer
FileAnalizzati = 0

Ogni volta che aggiorno il Riepilogo, ad ogni file analizzato incremento il valore della variabile FileAnalizzati

FileAnalizzati = FileAnalizzati + 1

A questo punto posso far vedere nella cella “G2” del foglio Riepilogo, formattata come percentuale,
lo stato di avanzamento dell’analisi

Sheet1.Range("G2") = FileAnalizzati / TotaleFile

ed il numero dei file analizzati

Sheet1.Range("J3") = FileAnalizzati

 

Faccio eseguire la macro “ContaTotaleFile” in apertura del file, per fornire uno strumento di controllo all’utilizzatore:
opotizzando che tra l’aggiornamento del Riepilogo e la sua riapertura siano stati inseriti nuovi file,
la macro li conterà automaticamente e la cella “J3” si colorerà di rosso per evidenziare che il numero dei file analizzati con l’ultimo aggiornamento non corrispondono a quelli attuali.

Sempre in relazione all’elevata numerosità dei file da analizzare, ho realizzato una macro che permette di aggiornare i dati di un singolo file senza dover aggiornare tutti gli altri (comoda se si modifica un file e si vuole aggiornare velocemente il Riepilogo)

Si seleziona una cella della colonna “Ariticolo” che si desidera aggiornare
(ho inserito un controllo per verificare che sia stata selezionata una cella contenente il dato necessario)
e si preme il tasto “Aggiorna” presente nella cella “B5”.

La macro “AggiornaSelezionato” apre il file di cui si vogliono riportare i dati aggiornati
utilizzando il percorso precedentemente archiviato nell’Hyperlink contenuto della colonna “Dettaglio”

For Each PathDaAprire In Sheet1.Cells(Selected_Row, 7).Hyperlinks
    PathDaAprire.Follow
Next

A questo punto il codice è lo stesso della macro generale con l’eccezione che utilizza il numero della riga della cella selezionata

Selected_Row = Selection.Row
Sheet1.Cells(Selected_Row, 3) = ActiveWorkbook.Sheets("Foglio1").Range("G8")
Sheet1.Cells(Selected_Row, 4) = ActiveWorkbook.Sheets("Foglio1").Range("K8")
Sheet1.Cells(Selected_Row, 5) = ActiveWorkbook.Sheets("Foglio1").Range("M8")

NB:

per il corretto funzionamento delle macro è necessario:
– non modificare il posizionamento delle colonne e l’intestazione “ARTICOLO”
– che i file da analizzare abbiamo sempre lo stesso layout di quelli dei file d’esempio
– che la cartella Magazzino contenga solo i file da analizzare.

In caso di esigenze diverse si dovrà modificare il codice.

A voi il file:
APRI

Riccardo Vincenti