Nel post di oggi vediamo come creare una macro in Visual Basic for Application che ci permetta di popolare in modo automatico una tabella di un documento Word usando come sorgente dati un foglio Excel esterno.
Creiamo quindi un documento Word contenente una tabella con un’unica riga, quella delle intestazioni di colonna, e lo salviamo come documento con modalità di attivazione Macro(.docm)
Creiamo inoltre un foglio Excel contenente la tabella con i dati che vogliamo esportare nel documento Word. Anche in questo caso inseriamo la riga di intestazione, che conterrà gli stessi nomi di colonna della relativa tabella su Word. Popoliamo la tabella con alcune righe di esempio:
A questo punto non ci resta che scrivere la macro VBA che effettuerà l’operazione.
Andiamo quindi nel menù “Visualizza” di Word e clicchiamo il pulsante “Macro”. Selezionando nella combo-box il documento Word corrente, definiamo il nome della macro che vogliamo creare, ad esempio “PopolaTabellaDaExcel” e poi premiamo il pulsante “Crea”, come illustrato nella figura seguente.
Entriamo così nell’editor di VBA e siamo quasi pronti per scrivere il codice della macro.
Per prima cosa però, al fine di poter lavorare con gli oggetti di Microsoft Excel, dobbiamo aggiungere il relativo riferimento. Per farlo andiamo nel menù “Strumenti” e selezioniamo “Riferimenti”. Scorriamo la lista fino a trovare la voce “Microsoft Excel 15.0 Object Library“, la selezioniamo e premiamo “Ok”.
A questo punto abbiamo a disposizione la libreria di oggetti Excel e possiamo definire un Applicazione Excel o un foglio di lavoro. Proviamo ad esempio ad inserire il seguente codice nel corpo della nostra macro e ad eseguirla.
Public Sub PopolaTabellaDaExcel() ' ' PopolaTabellaDaExcel Macro ' ' Dim myexl As Excel.Application Dim myworkbook As Workbooks Dim myws As Worksheets Debug.Print "OK" End Sub
Se la mandiamo in esecuzione, otteniamo nella finestra di output il risultato “OK”, segno che non ci sono stati errori.
Nel caso non avessimo aggiunto il riferimento alla libreria di oggetti Excel, avremmo ottenuto un errore perchè i tipi delle variabili che abbiamo dichiarato non sarebbero stati riconosciuti. Se proviamo, ad esempio, a rimuovere il riferimento che abbiamo aggiunto in precedenza e a mandare nuovamente in esecuzione il semplice codice della macro scritto finora otteniamo il seguente errore, mostrato anche in figura:
Errore di compilazione: tipo definito dall’utente non definito
Riaggiungiamo quindi il riferimento alla libreria Excel e proseguiamo.
Per prima cosa dobbiamo aprire il file Excel che contiene i nostri dati. Per semplicità, definiamo una variabile a cui assegniamo il path assoluto del file. Con la funzione Dir di VBA verifichiamo che il file sia effettivamente presente al path indicato. In caso contrario segnaliamo un errore. Modifichiamo quindi la macro come segue:
Public Sub PopolaTabellaDaExcel() ' ' PopolaTabellaDaExcel Macro ' ' Dim myexl As Excel.Application Dim myworkbook As Workbooks Dim myws As Worksheets Dim path_file_excel As String path_file_excel = "C:\Users\davis.molinari\Desktop\EsempioVBA\SorgenteDatiTabella.xlsx" If Dir(path_file_excel) = "" Then descrizione = "Errore: Impossibile trovare file excel di input " & path_file_excel Call MsgBox(descrizione, vbExclamation) Exit Sub End If Debug.Print "OK" End Sub
Indicando nella variabile “path_file_excel” il path corretto del file Excel, otteniamo ancora in output la stringa “OK”, segno che non si sono verificati problemi.
Se proviamo a mettere come valore della variabile “path_file_excel” il percorso di un file che non esiste, otteniamo un errore e ci viene mostrata la finestra con il messaggio che abbiamo definito, come illustrato nella figura seguente:
Procediamo e inseriamo il codice per aprire il file Excel e navigare gli sheet. Le seguenti righe istanziano un oggetto di tipo applicazione Excel, aprono il nostro file di input e si posizionano sul primo sheet per elaborarlo:
Set myexl = CreateObject("Excel.Application") Set myworkbook = myexl.Workbooks.Open(path_file_excel) Set myws = myexl.Worksheets(1)
Modifichiamo nuovamente la macro come segue, aggiungendo anche il codice per “chiudere” gli oggetti utilizzati, e proviamo ad eseguirla:
Public Sub PopolaTabellaDaExcel() ' ' PopolaTabellaDaExcel Macro ' ' Dim myexl As Object Dim myws As Object Dim myworkbook As Object Dim path_file_excel As String path_file_excel = "C:\Users\davis.molinari\Desktop\EsempioVBA\SorgenteDatiTabella.xlsx" If Dir(path_file_excel) = "" Then descrizione = "Errore: Impossibile trovare file excel di input " & path_file_excel Call MsgBox(descrizione, vbExclamation) Exit Sub End If Set myexl = CreateObject("Excel.Application") Set myworkbook = myexl.Workbooks.Open(path_file_excel) Set myws = myexl.Worksheets(1) Debug.Print "Qui dobbiamo mettere il codice che estrae i dati" myworkbook.Close myexl.Quit Set myws = Nothing Set myworkbook = Nothing Set myexl = Nothing End Sub
Anche a questo punto, non otteniamo alcun errore.
Ora possiamo finalmente concentrarci sulla logica di reperimento dei dati. Quello che facciamo è scorrere tutti gli oggetti di tipo tabella presenti nel file Word e controllare se il testo contenuto nella prima cella della tabella corrisponde a quello che stiamo cercando. Una volta identificata la tabella che dobbiamo popolare, iniziamo a scorrere le celle dalla riga seguente, spostandoci di colonna fino a quando non troviamo una cella vuota e proseguendo di riga in riga fino a quando non troviamo nuovamente una cella vuota.
Nel caso in cui nel file Excel non siano presenti dati relativi alla tabella che stiamo cercando, segnaliamo un errore.
Il codice per iterare su tutte le tabelle del documento Word attivo è il seguente:
Dim tbl As Table For Each tbl In ActiveDocument.Tables . . . . . . Next tbl
Pe recuperare il valore della prima cella della riga di intestazione della tabella Word dobbiamo utilizzare il codice seguente:
Dim s As String s = tbl.Rows(1).Cells(1).Range.Text
Modifichiamo ancora la macro come segue e proviamo ad eseguirla:
Public Sub PopolaTabellaDaExcel() ' ' PopolaTabellaDaExcel Macro ' ' Dim myexl As Object Dim myws As Object Dim myworkbook As Object Dim path_file_excel As String path_file_excel = "C:\Users\davis.molinari\Desktop\EsempioVBA\SorgenteDatiTabella.xlsx" If Dir(path_file_excel) = "" Then descrizione = "Errore: Impossibile trovare file excel di input " & path_file_excel Call MsgBox(descrizione, vbExclamation) Exit Sub End If Set myexl = CreateObject("Excel.Application") Set myworkbook = myexl.Workbooks.Open(path_file_excel) Set myws = myexl.Worksheets(1) Dim tbl As Table Dim s As String For Each tbl In ActiveDocument.Tables s = tbl.Rows(1).Cells(1).Range.Text Debug.Print "Valore:" & s & "-" Next tbl myworkbook.Close myexl.Quit Set myws = Nothing Set myworkbook = Nothing Set myexl = Nothing End Sub
Con l’istruzione
Debug.Print "Valore:" & s & "-"
stampiamo in output il valore della variabile “s”, cioè della prima cella di intestazione della tabella sul file Word. Abbiamo aggiungo un carettere “-” per verificare quale sia esattamente il valore e controllare che non ci siano caratteri speciali al fondo della stringa.
Eseguendo la macro, il risultato che otteniamo è quello mostrato nella figura seguente:
Come possiamo vedere, a separare la fine della stringa dal “-” sono presenti sia un carattere di ritorno a capo che un carattere speciale. Questo nonostante nella relativa cella essi non siano assolutamente presenti. Dobbiamo quindi tenerne conto quando andiamo ad eseguire il confronto tra la stringa della tabella Word e quella di ricerca.
Per semplicità, definiamo ora una variabile che contiene la stringa relativa al contenuto della prima cella della tabella che vogliamo popolare e che quindi dobbiamo cercare nella prima colonna del file Excel. Tale valore, al fine di generalizzare la procedura, dovrebbe essere invece passato come parametro. In base alla verifica fatta poco fa, il confronto secco di uguaglianza tra i due valori da esito negativo, a causa appunto dei caratteri speciali che avevamo identificato.
Se proviamo infatti a modificare la macro come segue e ad eseguirla:
Public Sub PopolaTabellaDaExcel() ' ' PopolaTabellaDaExcel Macro ' ' Dim myexl As Object Dim myws As Object Dim myworkbook As Object Dim path_file_excel As String path_file_excel = "C:\Users\davis.molinari\Desktop\EsempioVBA\SorgenteDatiTabella.xlsx" If Dir(path_file_excel) = "" Then descrizione = "Errore: Impossibile trovare file excel di input " & path_file_excel Call MsgBox(descrizione, vbExclamation) Exit Sub End If Set myexl = CreateObject("Excel.Application") Set myworkbook = myexl.Workbooks.Open(path_file_excel) Set myws = myexl.Worksheets(1) Dim tbl As Table Dim s As String Dim primaCella As String primaCella = "Codice Articolo" For Each tbl In ActiveDocument.Tables s = tbl.Rows(1).Cells(1).Range.Text If s = primaCella Then Debug.Print "La prima cella della tabella è stata trovata" Else Debug.Print "La prima cella NON è stata trovata" End If Next tbl myworkbook.Close myexl.Quit Set myws = Nothing Set myworkbook = Nothing Set myexl = Nothing End Sub
otteniamo il risultato illustrato in figura, che ci indica che la cella contenente la stringa “Codice Articolo” non è stata trovata.
Per ovviare al problema dei caratteri speciali, possiamo utilizzare la funzione Instr e, invece che verificare il match esatto tra i due valori, controlliamo che il testo contenuto nella cella della tabella Word inizi con il valore che stiamo cercando.
Sostituiamo quindi la riga di codice
If s = primaCella Then
con la riga
If InStr(1, s, primaCella) Then
Se proviamo a eseguire nuovamente la macro vediamo che questa volta la cella contenente il valore che cerchiamo viene identificata. La figura seguente ce ne da la conferma:
Definiamo quindi una variabile booleana che utilizziamo per tenere traccia del fatto che abbiamo trovato o meno una tabella il cui valore della prima cella è quello che stiamo cercando. Una volta identificata tale tabella, dobbiamo andare a verificare se sul file Excel sono disponibili dei dati con cui popolarla. Utilizziamo quindi una seconda variabile booleana per memorizzare tale informazione e segnalare eventualmente un errore nel caso in cui l’excel non contenga dati relativi a tale tabella.
A questo punto dobbiamo iniziare a processare il file Excel alla ricerca delle informazioni e lo facciamo nella seconda parte del tutorial: Word VBA: popolare una tabella con dati presi da un foglio Excel (Parte 2)
Pingback: Word VBA: popolare una tabella con dati presi da un foglio Excel (Parte 2) | Dede Blog
Domanda 1.
E’ un bell’esempio, trattato passo passo, ma non riesco ad andare fino in fondo.
Con office 2007, ottengo sempre l’errore
Errore di run-time ‘-2147417851 (80010105)’;
Metodo ‘Open’ dell’oggetto ‘Workbooks’ non riuscito
Per la verità non funziona alcun esempio del genere – ne il tuo né quello di altri – perché il mio word non riesce a superare l’istruzione:
Set myworkbook = myexl.Workbooks.Open(path_file_excel)
Forse è un problema di settaggio?
Dopo questa istruzione non so se l’esempio funziona…
Domanda 2.
Perché se attivo i riferimenti alla libreria di Excel quando riapro word non se ne ricorda?
Domanda 3.
Ma c’è un sistema ‘semplice’ per importare, ad esempio, dal Foglio1 e Foglio6 di Excel il valore di una o più celle?
Con stampa unione, infatti, si può collegare solo uno dei tanti fogli disponibili nel file Excel.
Va da sé, che in ogni caso, ti ringrazio (anche per la pazienza).
Ciao, anche io ho lo stesso problema. Quale è la soluzione?
Ciao, innanzi tutto Ti ringrazio per la bella guida.
Ti vorrei chiedere tuttavia lumi in relazione al mio problema, che è parzialmente legato al popolamento di una tabella in word:
io avrei una serie di dati in excel tipo per semplificare: ID, indirizzo, nome
e dovrei inserirli in tanti file word diversi, ciascuno dei quali ha nel titolo del file il campo ID.
Non posso usare stampa/unione perché in ogni file word ci sono molte foto, diverse da file a file e fra loro e devo compilare circa una cinquantina di file alla volta.
Grazie in anticipo se potrai aiutarmi o darmi delle indicazioni e in ogni caso grazie per la guida che ho letto.
Ciao,
Fabio