VBA: Riepilogare i dati di più files -Sheets(1)- in un unico file

[pic omnic]

Quella che ripropongo sotto è una procedura trovata su questo Gruppo. Serve per copiare in un unico file (WBto) i dati di Sheets(1) di parecchi altri files , posti in una cartella C:/Prova. Funziona bene, Ma io avrei una necessità aggiuntiva: per ogni riga copiata, aggiungere nel foglio di destinazione,per es nella colonna A, il nome del file di provenienza. Ho provato da solo, ma dopo 2 ore di tentativi, non riesco a trovare la soluzione. Qualche esperto può suggerirmi come fare ?
Grazie
draleo

Option Explicit

Private Sub Sfoglia_Files()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''' ATTENZIONE RICHIEDE L'ATTIVAZIONE DELLA LIBRERIA MICROSOFT SCRIPTING RUNTIME '''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim strPath As String

Dim objFSY As FileSystemObject
Dim objFOL As Folder
Dim objFIL As File

Dim wbFrom As Workbook, wbTo As Workbook
Dim wsFrom As Worksheet, wsTo As Worksheet
Dim x As Long, i As Long
Dim rngCopy As Range

Set wbTo = ThisWorkbook
Set wsTo = wbTo.Sheets(1)

strPath = "C:/Prova" '''QUI IL PERCORSO

Set objFSY = New FileSystemObject
Set objFOL = objFSY.GetFolder(strPath)

For Each objFIL In objFOL.Files
x = wsTo.Range("A" & wsTo.Rows.Count).End(xlUp).Row + 1

Set wbFrom = Application.Workbooks.Open(objFIL)
Set wsFrom = wbFrom.Sheets(1)
With wsFrom
i = .Range("A" & .Rows.Count).End(xlUp).Row
Set rngCopy = .Range("A4:Q" & i)
rngCopy.Copy wsTo.Cells(x, 1)
Set rngCopy = Nothing
End With
wbFrom.Close 0
Set wbFrom = Nothing
Set wsFrom = Nothing
Next

Set objFSY = Nothing
Set objFOL = Nothing
Set wbTo = ThisWorkbook
Set wsTo = Nothing
End Sub

[casanmaner]

Hai pensato se utilizzare powerquery (nel caso fosse disponibile per la tua versione di excel)?

ciao

[casanmaner]

Il giorno domenica 1 maggio 2022 alle 19:01:41 UTC+2 casanmaner ha scritto:
> Hai pensato se utilizzare powerquery (nel caso fosse disponibile per la tua versione di excel)?
>

In pochi passaggi partendo da questi tre file presenti in questa cartella:

https://www.dropbox.com/s/t8bujyofnjzml4r/Screenshot%202022-05-01%2019.14.32.png?dl=0

dove i tre file avevano questo contenuto:

https://www.dropbox.com/s/1ujerobmolc19kc/Screenshot%202022-05-01%2019.14.44.png?dl=0
https://www.dropbox.com/s/f8gl6lnoh3818bw/Screenshot%202022-05-01%2019.14.51.png?dl=0
https://www.dropbox.com/s/ondmw9b1cwduhxd/Screenshot%202022-05-01%2019.15.11.png?dl=0

ho ottenuto questa tabella con i tre file combinati e per i quali ho lasciato il nome del file in una delle colonne:

https://www.dropbox.com/s/jei8nrswlrgkap6/Screenshot%202022-05-01%2019.15.29.png?dl=0

[pic omnic]

ho office 2019 e non so se questo sopporti powerquery (credo di si, perchè in passato ,avevo già utilizzato qualche procedura scaricata da questo gruppo e funzionava). Ma non saprei comporre una procedura per intero. il tuo file di esempio dove si trova? nei link che hai messo ci sono solo dei screen shot.
grazie
draleo

[casanmaner]

Sicuramente lo supporta.
Sì ho messo gli screenshot per far vedere come sono arrivato ai risultati.
Per combinare i file devi andare su "Dati" e scegliere l'opzione da cartella:
https://www.dropbox.com/s/r09c4f8u4r4z4hh/Screenshot%202022-05-01%2020.09.20.png?dl=0

Si apre la finestra per scegliere il percorso e una volta selezionato si apre una finestra che elenca i file presenti:
https://www.dropbox.com/s/eogvqxhd986ipx7/Screenshot%202022-05-01%2020.10.11.png?dl=0

A quel punto clicchi su combina e trasforma e si apre una finestra per scegliere il foglio da cui prendere i dati:
https://www.dropbox.com/s/pz3s05ss7skkcrm/Screenshot%202022-05-01%2020.12.03.png?dl=0

clicchi su ok e vieni portato sull'editor di power query:
https://www.dropbox.com/s/1dnwot4813kg5eu/Screenshot%202022-05-01%2020.13.29.png?dl=0

come puoi notare tutto il foglio è stato portato comprese le intestazioni degli altri due fogli delle due cartelle.
Per avere solo le prime intestazioni io sono andato su "Trasforma file di esempio"
https://www.dropbox.com/s/6iubimc6zby5x8o/Screenshot%202022-05-01%2020.15.11.png?dl=0
e ho sollevato la prima riga come intestazione con:
https://www.dropbox.com/s/la5ilgln7mnz4o8/Screenshot%202022-05-01%2020.15.58.png?dl=0

a quel punto per avere la colonna del nome della cartella mi sono spostato su "Prova" e ho eliminato l'ultima riga di "Modifica tipo" perché non trova le intestazioni precedenti.
https://www.dropbox.com/s/p7qn6lsddnh68t6/Screenshot%202022-05-01%2020.23.17.png?dl=0

tolta l'ultima istruzione è apparso l'elenco che poi ho caricato in un foglio della cartella di lavoro:
https://www.dropbox.com/s/gmctwypohbfyi81/Screenshot%202022-05-01%2020.24.19.png?dl=0

[pic omnic]

Credo di aver capito. Voglio provare questo sistema, per me nuovo. Non nascondo, che a caldo, mi sembra un pò prolisso; spero che questa titubanza sia dovuta alla mia ignoranza in materia e che imparando, un pò per volta, si riveli semplice. Ma dove trovo questo file ? io non sarei in grado di realizzarlo.
draleo

[pic omnic]

credo di esserci riuscito (almeno fino al punto dove occorreva eliminare le 2 righe di testa). poi mi sono bloccato perchè non ho trovato il modo di salvare i risultati ottenuti. Sicuramente è la prima volta che mi cimento e troverò il modo di concludere la procedura.Prime Impressioni:è semplice perchè non occorre conoscere il VBA; è lento (5 minuti per arrivare ad avere i dati caricati, tra l altro volutamente "accorciati" ); è prolisso (tanti passaggi); ogni volta che occorrerà cambiare i dati di provenienza, occorrerà ricominciare tutto da capo ? e se volessi portare la procedura su un altro PC, dovrò ricostruirla sugli altri PC ? francamente non mi sembra un granchè. il vecchio VBA appartiene ad un altra categoria. (ma forse il mio giudizio è dovuto al fatto che non so usare questo nuovo metodo. Spero...)
Grazie e bentornato...E' stato un piacere risentirti
draleo

[casanmaner]

Una volta creata la query puoi caricarla in un foglio tramite "Carica in ...".
https://www.dropbox.com/s/4knyk5ehf8h5fsw/Screenshot%202022-05-01%2022.08.15.png?dl=0

E poi decidere in che modalità caricare i dati della query:
https://www.dropbox.com/s/6nxrga6cj8zv8v9/Screenshot%202022-05-01%2022.08.47.png?dl=0

In caso di distribuzione ad altri se i file nel percorso di provenienza sono identici basterà cambiare l'origine con le apposite funzioni.
https://www.dropbox.com/s/n1twvo59iaru52m/Screenshot%202022-05-01%2022.11.19.png?dl=0
https://www.dropbox.com/s/13hozynrflfhn8x/Screenshot%202022-05-01%2022.11.36.png?dl=0
https://www.dropbox.com/s/fmrqmjntwgrftav/Screenshot%202022-05-01%2022.11.41.png?dl=0

Volendo si può esportare anche la query solamente avendola a disposizione come "modello".
Poi la si può caricare anche in un nuovo file, senza dover ripetere i passaggi fatti inizialmente.
Così la esporti:
https://www.dropbox.com/s/kk6g382gcpildee/Screenshot%202022-05-01%2022.16.55.png?dl=0
https://www.dropbox.com/s/e5dkva8ope1wzqv/Screenshot%202022-05-01%2022.17.22.png?dl=0

Così la importi in un nuovo file excel:
https://www.dropbox.com/s/4xolf13st4ny9pl/Screenshot%202022-05-01%2022.17.51.png?dl=0
https://www.dropbox.com/s/1ksuqtgdptucuit/Screenshot%202022-05-01%2022.17.58.png?dl=0
https://www.dropbox.com/s/hgpru8a2v11zz13/Screenshot%202022-05-01%2022.18.04.png?dl=0
https://www.dropbox.com/s/0koj6koy8okysck/Screenshot%202022-05-01%2022.18.08.png?dl=0
https://www.dropbox.com/s/7h8i5vkpe4am490/Screenshot%202022-05-01%2022.18.10.png?dl=0

Poi volendo, ad alcune condizioni, o tramite formule nella stessa cartella di lavoro o vba si potrebbe fare in modo di modificare automaticamente l'origine, magari in base a dove è salvato il file excel.
Oppure semplicemente indicando in una cella di un foglio il percorso dei file.
Il linguaggio ancora non l'ho compreso appieno, considerato che è da poco che sto provando a usare power query, ma per alcuni aspetti non è così difficile entrare nella logica.

Però ad es. ho estratto alcune tabelle "fiscali" dai modelli ministeriali in modo molto immediato :-)

buona serata

[issdr]

pic omnic wrote:

> Ma io avrei una necessità aggiuntiva: per ogni riga copiata,
> aggiungere nel foglio di destinazione,per es nella colonna A, il nome
> del file di provenienza.

ho semplificato (malamente) il codice, togliendo l'opzione definizioni
esplicite e sendendo non necessaria la spunta a mano del WSH.

--8<---------------cut here---------------start------------->8---
Sub Sfoglia_Files()

Set objFSY = CreateObject("Scripting.FileSystemObject")
Set objFOL = CreateObject("Scripting.FileSystemObject")
Set objFIL = CreateObject("Scripting.FileSystemObject")

Set wbTo = ThisWorkbook
Set wsTo = wbTo.Sheets(1)

strPath = "C:\Prova" '''QUI IL PERCORSO

Set objFOL = objFSY.GetFolder(strPath)

For Each objFIL In objFOL.Files
x = wsTo.Range("A" & wsTo.Rows.Count).End(xlUp).Row + 1

Set wbFrom = Application.Workbooks.Open(objFIL)
Set wsFrom = wbFrom.Sheets(1)
With wsFrom

wsTo.Cells(x, 1) = objFIL.Name

i = .Range("A" & .Rows.Count).End(xlUp).Row
Set rngCopy = .Range("A4:Q" & i)

rngCopy.Copy wsTo.Cells(x + 1, 1)
End With
wbFrom.Close 0
Next

End Sub
--8<---------------cut here---------------end--------------->8---