Word VBA: popolare una tabella con dati presi da un foglio Excel (Parte 1)

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)

Word VBA - Modello Word vuoto

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:

Word VBA - Tabella Excel

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.

Word VBA - Creare Macro

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”.

Word VBA Riferimenti Aggiunta Excel

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.

Word VBA - Prima esecuzione macro
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

Word VBA - Tipo 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:

Word VBA - errore file input non trovato
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:

Word VBA - Valore prima cella
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.

Word VBA - Prima cella NON 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:

Word VBA - Prima cella Trovata

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)

3 thoughts on “Word VBA: popolare una tabella con dati presi da un foglio Excel (Parte 1)

  1. Pingback: Word VBA: popolare una tabella con dati presi da un foglio Excel (Parte 2) | Dede Blog

  2. 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).

Leave a Reply

Your email address will not be published. Required fields are marked *