Tutorial Copiare dati tra due fogli contenuti in file differenti

giulianovac

Access/VBA Expert
Expert
9 Giugno 2018
842
131
43
Italy
Office 2019
Best answers
61
Ciao a tutti!

PREMESSA

Ho pensato di realizzare questo tutorial perché spesso leggo sul forum richieste di procedure per la copia di dati da un foglio all'altro.
Qui mi riferisco ad caso specifico, che va oltre la mera copia di dati, perché si richiede di eseguire la copia tra colonne che in realtà hanno posizione e nomi differenti. Nel caso specifico mi riferisco a questa discussione
importare dati da altro file exel mediante una macro (con alcune colonne predeterminate)


Per comodià cito testualmente la richiesta iniziale:

Vorrei importare dati in una tabella di un foglio(tabella di destinazione), da un altro foglio (tabella di provenienza) mediante una macro.
I dati da importare dovrebbero essere associati a dei valori (colonne) predeterminati nella tabella di destinazione.
La relazione tra la tabella di provenienza e la tabella di destinazione è la seguente: tutti i valori(colonne) presenti nella tabella di provenienza associate alle colonne della tabella di destinazione.
Nel caso in cui una o piu colonne (nella tabella di destinazione) non siano presenti nella tabella di provenienza questa/e colonna non dovra risultare.
Attraverso una schermata vorrei poter associare i nomi dei campi tra la tabella di provenienza e la tabella di destinazione. Questi valori non per forza avranno lo stesso identico nome nelle due tabelle, motivo per il quale è necessario fare una associazione manuale, mediante maschera, con i vari campi di entrambe le cartelle.
Detta così la questione sembra molto complessa e, a prima vista, quasi irrrisolvibile.
Vedremo invece che, con qualche piccola astuzia, si troverà la soluzione, nemmeno poi così complicata come parrebbe.


INTERFACCIA

Chiaramente, alla fine del tutorial, trovate il link per scaricare il progetto completo, scaricatelo già, ma io suggerisco di crearvi un VOSTRO nuovo progetto ed inserire i controlli ed il codice in quest'ultimo, così da migliorare l'apprendimento. Le cose si capiscono quando le si fanno, non quando le si leggono solamente!


Prima di tutto occorre pianificare l'interfaccia adeguata allo svolgimento del compito.
Ma in il primo passo è quello di chiederci: cosa dobbiamo fare?
a) aprire i due file interessati
b) prendere nota dei nomi delle colonne
c) copiare i dati dalle colonne del foglio provenienza a quelle del foglio di destinazione

Nella fase (a) useremo un FileDialog per selezionare i file (servono dei pulsanti)
Nella fase (b) basta leggere i nomi dalle celle della prima riga (servono dei listbox)
La fase (c) è quella più ostica, vista la diversità di struttura dei due fogli (serve codice!)

FASI PRELIMINARI
- apriamo Excel e creiamo un nuovo file che salveremo in formato XLSM, che accetta le macro.
- premiamo ALT + F11 per entrare nell'ambiente di progettazione VBA di Excel
- dal menu Inserisci selezioniamo UserForm (per creare il nostro form):

Dalla finestra delle Proprietà (se non è visibile premere F4) ci posizioniamo sulla proprietà Name e sostituiamo UserForm1 con frmImportaDati, e nella proprietà Caption indicheremo Importa Dati.

Personalmente ritengo fondamentale partire con il piede giusto per cui mi scuserete se premetto due raccomandazioni:
A) Impostare le opzioni VBA in modo corretto: Aprite le Opzioni, dal menu Strumenti e verificate di aver attivato tutti i flag:

Leggete qui Consigli per i Newbies per capire il perché.

B) Usate sempre una nomeclatura significativa, come abbiamo visto per il form:
A lungo andare benedirete questa pratica perché quando aprirete i vostri progetti in futuro farete fatica a distinguere quale sia il pulsante, ad es., che Copia i dati se avete lasciato i nomi proposti dal VBA: CommandButton1, CommandButton2, e così via... e vi toccherà controllarli tutti solo per capire quale azione svolgano e trovare quello che vi serve.
Ma se il pulsante ha un nome significativo, come cmdCopiaDati, solo leggendo il nome saprete già cosa fa.
Non ultimo, quando chiedete aiuto sul forum e presentate un form con 50 Textbox con nome Textbox1, Textbox2, ..., Textbox50 è facile che a chi legge passi la voglia di aiutarvi perché è costretto a perdere un sacco di tempo solo per individuare quale textbox fa cosa!

- come ultima fase, sempre dal menu Inserisci selezioniamo Modulo (ci servirà per aprire il form)
- salvate il file. Per comodità potete premere CTRL+S. Salvate spesso, eviterete di rischiare di perdere dati.

Ora iniziamo ad inserire i controlli che ci occorro (come sopra indicato):
1. CommandButton, per selezionare il file di provenienza
Name=cmdProvenienza, Caption=Seleziona file provenienza
2. CommandButton, per selezionare il file di destinazione
Name=cmdDestinazione, Caption=Seleziona file destinazione

Poi, siccome dobbiamo prendere nota delle colonne di entrambi i fogli le caricheremo in due caselle di riepilogo (listbox):
3. Casella di riepilogo: Name=lstProvenienza, per le colonne di provenienza
4. Casella di riepilogo: Name=lstDestinazione, per le colonne di destinazione

Posizionati sopra le caselle, mettiamoci anche le etichette (Label):
5. Label: Name=lblProvenienza, Caption=Provenienza
6. Label: Name=lblDestinazione, Caption=Destinazione

Infine, ma solo per il momento, aggiungiamo ancora:
7. CommandButton: NamecmdCopiaDati, Caption=Copia dati
8. CommandButton: Name=cmdSu, Caption=SU
9. CommandButton: Name=cmdGiu, Caption=GIU
10. Casella di riepilogo: Name=lstNascosto
11. Un casella di controllo: Name=chkEliminaColonneNonUsate,Caption=Elimina colonne non utilizzate


Ecco come dovrebbero essere posizionati i controlli sopraindicati:





CODICE

Ora iniziamo ad inserire il codice necessario.

Prima di tutto andremo ad inserire il codice che seleziona il file di Provenienza nell'evento del relativo pulsante, cmdFileProvenienza_Click():

Visual Basic:
Private Sub cmdFileProvenienza_Click()
    Dim tmp As String
    tmp = SelezionaFile()
    If tmp > vbNullString Then
        sFilePro = tmp
        lstProvenienza.ControlTipText = sFilePro
        CaricaProvenienza
    End If
End Sub
Come vedete il codice usa una routine generica SelezionaFile() che verrà sfruttata sia per aprire il file di Provenienza, sia per quello di Destinazione:

Visual Basic:
Public Function SelezionaFile() As String
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
        .InitialFileName = ThisWorkbook.Path
        .AllowMultiSelect = False
        .Filters.Add "File Excel", "*.xls; *.xlsx", 1
        .Show
        If .SelectedItems.Count = 0 Then Exit Function
        SelezionaFile = .SelectedItems(1)
    End With
End Function
Una volta selezionato il file, viene eseguita una seconda routine CaricaProvenienza() che si incarica di:

1. Aprire fisicamente il file selezionato
2. Ricavare il numero di colonne e delle righe che contengono i dati
3. alimentare il listbox lstProvenienza con le intestazioni di colonna
4. ridurre il file ad icona

Il tutto viene ripetuto in modo analogo per il file di Destinazione, per cui le routine utilizzate saranno:
a) cmdFileDestinazione_Click()
b) CaricaDestinazione() ma qui, i nomi delle colonne vengono caricati anche nel listbox lstNascosto, poi vedremo il perché.


Dopo aver inserito il codice dei due pulsanti e, ovviamente le altre due routine usate da essi, se ora avviamo il form e selezioniamo i due file allegati allo zip (file_provenienza.xlsx e file_destinazione.xlsx) otterremo quanto segue (qui ho tralasciato volutamente le dichiarazioni, le trovate nel progetto):



Vedremo nei listbox le relative colonne dei due file, ma che anche il listbox lstNascosto contiene gli stessi nomi del lstDestinazione. Come mai?
Semplice, perché questo listbox verrà utilizzato per conoscere la posizione originaria delle colonne che, eventualmente, andremo a spostareo nel lstDestinazione.

Ho volutamente evidenziato le colonne PRIX e prezzo.
Cerchiamo di capire meglio, la colonna prezzo risulta l'ultima, in lstDestinazione ma, come indicato nella richiesta iniziale, dobbiamo allinearla alla colonna PRIX (in lstProvenienza).
Per farlo, useremo il pulsante [SU], cliccando 4 volte (qui non riporto il codice dei due pulsanti SU e GIU, in quanto è molto semplice, copiatelo dal sorgente).

Chiaramente, spostando la colonna su, l'indice della colonna stessa verrà modificato:
Inizialmente è 12, poi spostandolo su ed allineandolo alla colonna PRIX, diventa 8.
A questo punto come facciamo a capire che il valore di PRIX andrà ad essere scritto nella colonna 12 del file di destinazione?
Semplice! lo ricaviamo dal lstNascosto che mantiene la posizione originale delle colonne.

Adesso abbiamo bisogno del codice che vada a scoprire quale sia la la colonna in cui scrivere i dati.
Per fare questo ci avvarremo di un array che andremo a valorizzare nella routine finale:

CopiaDati()

Visual Basic:
    x = 1
    Rem ------------------------------------------------------------
    Rem Trova la posizione ordinale delle colonne abbinate
    Rem ------------------------------------------------------------
    For i = 0 To lstProvenienza.ListCount - 1
        bTrovato = False
        Rem per ogni colonna in lstProvenienza cerco in lstNascosto,
        Rem e quando lo trovo memorizzo l'indice nell'array
        For j = i To lstDestinazione.ListCount - 1
            For y = 0 To lstNascosto.ListCount - 1
                If lstNascosto.List(y) = lstDestinazione.List(j) Then
                    ReDim Preserve arCol(x)
                    arCol(x) = y + 1
                    bTrovato = True
                    x = x + 1
                    Exit For
                End If
            Next y
            If bTrovato Then Exit For
        Next j
    Next i
In pratica questo codice, come scritto nei commenti, non fa altro che:
1. leggere il campo da Provenienza
2. leggere il NOME del campo relativo in Destinazione
3. cercare il NOME del campo in lstNascosto
quando trova la corrsipondenza, ricaviamo la posizione della colonna e la memorizziamo nell'array arCol().

Al termine di questo codice otterremo un array con le posizioni di ogni colonna in cui dovremo scrivere i dati.

A conferma di ciò, la parte di codice che troviamo dopo scriverà nella finestra immediata i numeri delle colonne:
0 1 2 3 4 5 6 7 8 13

Notare che tutti i valori sono progressivi, meno l'ultimo valore che è 13, cioè la posizione della colonna prezzo prima che la spostassimo su.
Una precisazione è d'obbligo: i valori partono da 0 perché in una casella di riepilogo (listbox) l'indice inizia sempre dallo 0, mentre le colonne del foglio iniziano da 1.
Nel codice, però, io ne ho tenuto conto ed il primo elemento dell'arCol() infatti non viene preso in considerazione.

Grazie a questo array, la copia dei dati tra i due file sarà quasi banale:

Visual Basic:
    Rem Adesso copia i dati da Pro a Des
    For i = 2 To urPro
        For x = 1 To ucPro
            shDes.Cells(i, arCol(x)) = shPro.Cells(i, x)
        Next x
    Next i
La fase finale è opzionale. Se vogliamo che il foglio tabella_destinazione abbia le stesse colonne del foglio tabella_provenienza sarà necessario eliminare quelle in più tramite la routine
EliminaColonneNonUsate().

Una considerazione:
la copia dei dati sarebbe stata ancor più semplice se avessi deciso di eliminare le colonne inutilizzate prima di procedere alla copia stessa.
Infatti non avremmo avuto bisogno né del lstNascosto né del relativo codice che ricava l'indice delle colonne.
Ma in questo modo non avrei reso l'idea, e fornito una soluzione pratica, della dinamicità del progetto.
Così, invece, abbiamo potuto vedere come sia possibile 'allineare' colonne di fogli diversi che hanno nomi e posizioni differenti, ovvero il peggio che posa accadere.


CONCLUSIONE
Credo che questo tutorial abbia dimostrato che a volte i problemi sembrano irrisolvibili solo nella nostra mente, ma che sforzandosi un po', riflettendoci su ed analizzando le questioni con calma e raziocinio molte volte quello che sembra un limite in realtà non lo è. Certamente, occorre studiare, studiare ed ancora studiare.
Ma alla fine ne vale davvero la pena!
 

Allegati

Reactions: ges

klingklang

Ciappinaro VBA_Expert
Expert
20 Ottobre 2017
3.151
349
83
42
San Giovanni in Persiceto (BO)
www.excelswissknife.com
2016, 365
Best answers
192
Molto bello e ottimamente spiegato e realizzato! :applausi:
Ho una proposta per renderlo ancora più potente e versatile: prendere in considerazione il caso in cui il file di provenienza abbia PIU' colonne di quello di destinazione, e pertanto siano quelle del file di provenienza a dover essere spostate su/giù per allinearle correttamente (ed eventualmente prevedere come opzione l'aggiunta in coda delle colonne "extra"). Che dici si può fare?
 

giulianovac

Access/VBA Expert
Expert
9 Giugno 2018
842
131
43
Italy
Office 2019
Best answers
61
Errata corrige #1
Nell'evento cmdGiu_Click()
questa istruzione genera un'errore quando si tenta di spostare la voce selezionata oltre il limite:
If lstDestinazione.ListIndex = lstDestinazione.ListCount Then Exit Sub
va sostituita con questa:
If lstDestinazione.ListIndex = lstDestinazione.ListCount - 1 Then Exit Sub


Errata corrige #2
Aggiungere all'inizio degli eventi cmdGiu_Click() e cmdSu_Click() l'istruzione:
If lstDestinazione.ListIndex = -1 Then Exit Sub
per evitare che generi un errore nel caso si faccia clic e nessuna voce è selezionata.
 
Ultima modifica:

giulianovac

Access/VBA Expert
Expert
9 Giugno 2018
842
131
43
Italy
Office 2019
Best answers
61
Variante 2


Ho realizzato questa variante a seguito della seguente richiesta:
Ho una proposta per renderlo ancora più potente e versatile: prendere in considerazione il caso in cui il file di provenienza abbia PIU' colonne di quello di destinazione, e pertanto siano quelle del file di provenienza a dover essere spostate su/giù per allinearle correttamente (ed eventualmente prevedere come opzione l'aggiunta in coda delle colonne "extra"). Che dici si può fare?
La modifica è stata piuttosto veloce, 'quasi' è stato sufficiente modificare i nomi degli oggetti. Ho scritto quasi perché chiaramente qualche modifica si è resa necessaria.

Novità
Come richiesto sopra, questa variante 2 copierà i dati da un foglio di provenienza che ha più colonne rispetto a quello di destinazione.
Opzionalmente, copierà anche le colonne che non sono presenti.

In questa variante ho aggiungo anche i combobox per la selezione del foglio dai rispettivi file.

Come si vede nell'immagine ho lasciato anche il listbox nascosto di destinazione (anche se non utilizzato).
A qualcuno potrebbe venir voglia di perfezionare ulteriormente il progetto e così potrebbe tornargli utile.


Buon divertimento!
Saluto_saluto
 

Allegati

Reactions: klingklang and ges

ges

Excel/VBA Expert
Amministratore
Expert
21 Giugno 2015
15.461
418
83
Como
2011MAC 2016WIN
Best answers
184
Grazie per l'ottimo e chiaro Tutorial :applausi:
 

giulianovac

Access/VBA Expert
Expert
9 Giugno 2018
842
131
43
Italy
Office 2019
Best answers
61
Grazie!!! Rubik72 @Rubik72
Ricevere i complimenti dai 'grandi' fa molto piacere.
:felice:
 

Sostieni ForumExcel

Aiutaci a sostenere le spese e a mantenere online la community attraverso una libera donazione!