Tutorial Compilare una lettera di Word con dati da Excel

Stato
Chiusa ad ulteriori risposte.

giulianovac

Access/VBA Expert
Expert
9 Giugno 2018
2.233
83
Italy
2013 2019
166
Ciao a tutti!

PREMESSA

Sulla falsariga di una richiesta apparsa sul forum (poi caduta nel vuoto) che mi ha incuriosito ho pensato di creare questo tutorial.
Può accadere infatti di dover inviare lettere di qualsiasi genere a più destinatari e non sempre gli strumenti 'standard' offrono quel grado di personalizzazione necessario.
In questo caso, invece, siamo i padroni di ogni aspetto grazie alla possibilità di gestire il codice VBA.

In questo tutorial vedremo come sia possibile prendere dei dati da un database Excel in formato tabella, con questi compilare un file di Word (anche complesso) ed infine generare una serie di file (uno per ogni riga del database Excel) sempre in formato DOCX oppure, opzionalmente, in formato PDF.

Quale può essere l'utilità di una simile procedura?
Pensiamo ad esempio all'invio di un sollecito, di una lettera informativa, di un'offerta, di un preventivo, di una richiesta di varia natura.
Nel caso specifico ho pensato all'invio a clienti, ma è possibile qualsiasi tipo di lettera (magari un invito ad amici e parenti).

Naturalmente entrambi i file sono stati creati per lavorare insieme quindi la lettera in Word è stata pensata per stampare i dati di Excel.
  • Il file di Excel contiene i dati in formato tabellare, esattamente come un database.

  • Il file di Word è un semplicissimo file in cui ho inserito manualmente:
    - dei segnalibri
    - una casella di testo
    - una tabella con due righe, la prima di intestazione delle colonne, la seconda conterrà alcuni dati.

    In sostanza, i dati del database Excel verranno 'distribuiti' tra questi elementi.
    Ho pensato di non utilizzare i bookmark come segnaposto ma ho usato del semplice testo in questo formato:
    %%COGNOME%%
    quindi cosa fattibile anche da un utente non esperto
    Il nostro compito sarà quello di sostituire questi segnalibri con i valori reali.

    N.B. Ho volutamente usato un normalissimo file DOCX, invece di un modello di Word (DOTx) che sarebbe stato più complesso da gestire per uno sviluppatore poco esperto; anche perché di solito, in azienda, gli utenti usano un file Word normale.
INTERFACCIA

Il progetto è composto da un solo userform che contiene tutto il codice necessario. in cui si impostano i due file (Excel e Word) che vogliamo usare e la cartella di uscita (output) in cui andranno salvati i file generati. Vediamo come si presente l'Userform:



Per vostra semplicità d'uso, ho già previsto che vengano automaticamente impostati i due file (Word ed Excel) e la cartella di

Output. Ciò avviene nell'evento UserForm_Initialize().
Ovviamente se intendete utilizzare il form per i vostri scopi modificate i percorsi, oppure eliminate del tutto il codice, in

tal caso questi parametri andranno selezionati manualmente tramite gli appositi tre pulsanti 'Seleziona ...'

Nel riquadro delle Opzioni ho inserito qualche flag da impostare prima di eseguire la procedura:
  1. Salva in PDF (attivo):
    se lo disattivate i file verranno salvati nel formato normale di Word (DOCX), anziché in PDF.

  2. Mostra Word (non attivo):
    Attivatelo se volete osservare in real-time come procede la compilazione della lettera.

  3. Apri cartella di output al termine (attivo):
    mostrerà la cartella in cui sono stati depositati i file appena generati
    N.B. Le eventuali copie già presenti verranno sovrascritte senza avviso.

  4. Titolo:
    Potete indicare il testo che dovrà apparire nella casella di testo (opzionale)
    Se lasciate vuoto, la casella di testo, in Word, non verrà modificata
Notare che il file di Word sarà utilizzato come 'modello' ovvero questo file non sarà mai modificato.
Una volta impostati tutti i parametri non ci resta che premere il pulsante Esegui e nel riguadro più sopra vedremo il procedere dell'elaborazione che mostra l'avanzamento (1 di 7, 2 di 7, e così via...)


CODICE
Ora analizziamo il codice.

Tralascio le routine che selezionano i file e la cartella, in cui viene usato il solito
Application.FileDialog (msoFileDialogFilePicker)
che immagino tutti conosciamo già.


ApriURL()
Questa merita un breve cenno perché è una routine general-purpose che potrebbe tornare utile anche per altri progetti. La caratteristica positiva è quella che vi permette di aprire indifferentemente qualsiasi file, cartella o URL web (ma anche un programma eseguibile) e lo farà sempre utilizzando il programma predefinito del computer.
Ciò significa che non dovete preoccuparvi di conoscere quale programma di lettura PDF abbia installato l'utente, lei lo sa! Infatti, il 'cuore' della routine è quella di usare la funzione API di sistema ShellExecute ed è predisposta proprio a tale scopo.
A titolo di curiosità, se nella chiamata ShellExecute sostituite la "open" di un file con "print" il file verrà inviato alla

stampante predefinita del computer.


CompilaSalvaFileConDati()
Questa routine è l'artefice principale di tutta l'elaborazione, quindi merita un'analisi più approfondita.

Nella prima parte abbiamo:

1. tutte le dichiarazioni delle variabili di comodo e delle variabili oggetto, ovvero degli oggetti Excel e Word utilizzati.
2. poi i soliti controlli 'di rito' per verificare che i percorsi siano impostati e che esistano


Ora inizia il codice interessante.
Prima di tutto viene aperto il file Ecxel con i dati, e viene nascosto (dato che non è di interesse vederlo)
Visual Basic:
    Set wb = Workbooks.Open(sFileDatabaseExcel)
    wb.Windows(1).visible = False ' lo nascondo
dopo di ché contiamo quante righe contiene per sapere quanti file dovremo creare:
Visual Basic:
    Set sh = wb.Sheets(1)
    ur = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row ' ricavo ultima riga
Prima di iniziare il ciclo che elabora tutte le righe, impostiamo le variabili che servono a mostrare l'avanzamento:
Visual Basic:
    Dim counter As Long
    Dim countermax As Long
    counter = 0
    countermax = ur - riga - 1
Ora tocca alle variabili usate nel ciclo di elaborazione:
Visual Basic:
    riga = 2 ' riga in cui iniziano i dati del database Excel
    y = 1    ' usato per indicare la riga della tabella di Word
Ora inizia il ciclo For/Next che ad ogni ciclo esegue le seguenti operazioni:

1. Apre il file Word (lettera.docx)
Visual Basic:
        Set xWord = New Word.Application
        If chkMostraWord.Value = True Then
            xWord.visible = True ' così vedo cosa accade
        End If
2. Legge alcuni valori dal database Excel:
Visual Basic:
        sCodice = sh.Cells(i, 1)
        sCognome = sh.Cells(i, 2)
        sNome = sh.Cells(i, 3)
        sIndirizzo = sh.Cells(i, 4)
        sPratica = sh.Cells(i, 5)
e li copia al posto dei segnaposto:
Visual Basic:
        Set xRange = xWord.ActiveDocument.Range
        xRange.Find.Execute "%%COGNOME%%", , , , , , , , , sCognome, True
        Set xRange = xWord.ActiveDocument.Range
        xRange.Find.Execute "%%NOME%%", , , , , , , , , sNome, True
        Set xRange = xWord.ActiveDocument.Range
        xRange.Find.Execute "%%CODICE%%", , , , , , , , , sCodice, True
        Set xRange = xWord.ActiveDocument.Range
        xRange.Find.Execute "%%INDIRIZZO%%", , , , , , , , , sIndirizzo, True
N.B. Si sarebbe potuto copiare direttamente il valore dalla cella,
ma ho preferito fare così per rendere il codice più leggibile.

3. Ora valorizziamo il titolo della casella di testo, ma solo se è indicato:
Visual Basic:
        If lblTitolo.Caption > vbNullString Then
            xWord.ActiveDocument.Shapes.Item(1).TextFrame.TextRange = txtTitolo.Text
        End If
4. Infine compiliamo a tabella:
Visual Basic:
        Set xTabella = xWord.ActiveDocument.Tables(1)
        ' copio l'intestazione delle colonne da Excel
        For x = 1 To 5
            xTabella.Rows(y).Cells(x).Range.Text = sh.Cells(y, x + 5)
        Next x

    ' qui vediamo come aggiustare la larghezza di alcune colonne
        xTabella.Rows(1).Cells(1).Width = 60
        xTabella.Rows(2).Cells(1).Width = 60
        xTabella.Rows(1).Cells(2).Width = 120
        xTabella.Rows(2).Cells(2).Width = 120
        xTabella.Rows(1).Cells(5).Width = 95
        xTabella.Rows(2).Cells(5).Width = 95

    ' ed ora inseriamo i dati
        xTabella.Rows(y + 1).Cells(1).Range.Text = sh.Cells(i, 6)
        xTabella.Rows(y + 1).Cells(2).Range.Text = sh.Cells(i, 7)
        xTabella.Rows(y + 1).Cells(3).Range.Text = sh.Cells(i, 8)
        xTabella.Rows(y + 1).Cells(4).Range.Text = sh.Cells(i, 9)
        xTabella.Rows(y + 1).Cells(5).Range.Text = sh.Cells(i, 10)
5. Ora dobbiamo salvare il file modificato con un nuovo nome e nel formato richiesto:
Visual Basic:
        If Right$(sCartellaOutput, 1) <> "\" Then
            sCartellaOutput = sCartellaOutput & "\"
        End If
               
        sFileOutput = sCognome & " " & sNome & "-" & sPratica
        ' salvo il file aggiungendo l'estensione richiesta in base all'opzione:
        If chkSalvaPDF.Value = True Then
            sFileOutput = sFileOutput & ".pdf"
            xWord.ActiveDocument.SaveAs sCartellaOutput & sFileOutput, wdFormatPDF
        Else
            sFileOutput = sFileOutput & ".docx"
            xWord.ActiveDocument.SaveAs sCartellaOutput & sFileOutput, wdFormatDocumentDefault
        End If
        xWord.DisplayAlerts = False
        xWord.ActiveDocument.Saved = True
        xWord.ActiveDocument.Close wdDoNotSaveChanges
        'xWord.DisplayAlerts = True
e, cosa fondamentale, chiudere correttamente l'istanza di Word:
Visual Basic:
        xWord.Quit
        Set xWord = Nothing


6. Terminata la generazione dei file, se richiesto, apriamo la cartell di output:
Visual Basic:
    If chkApriOutput.Value = True Then
        ApriURL sCartellaOutput
    End If

CONCLUSIONE
Come abbiamo visto è relativamente semplice creare una serie di lettere di ottima fattura; pensiamo ad esempio ad una lettera aziendale completa di intestazione e piè di pagina...
Se poi andiamo ad implementarre anche l'invio dei PDF come allegati nei messaggi di posta elettronica (nel forum vi sono numerosi tutorial e discussioni) possiamo costruire un sistema completo di mailing davvero notevole. Abbiamo visto che, in fin dei conti, occorre davvero poco codice.

Possiamo espandere le possibilità qui offerte.
Ad esempio, pensiamo alla possibilità di compilare la tabella in Word con ulteriori righe e che riporti dei totali o delle medie a fine tabella.
Possiamo inserire altri testi, sia nelle caselle sia aggiungendo ulteriori paragrafi.

In definitiva, questo VBA vale davvero la pena di essere studiato ed approfondito, viste le enormi potenzialità che possiede.

Come sempre, se avete problemi, vi raccomando di NON accodarvi al tutorial, ma di creare un'apposita discussione nella sezione
Excel VBA e MACRO


Buon lavoro
 

Allegati

Marius44

VBA Expert
Moderatore
Expert
9 Settembre 2015
5.663
83
75
Catania
Excel2010
142
Ciao Giuliano
cosa posso dire: geniale nella sua semplicità (questo è un modo di dire perchè semplice non lo è affatto ma "user friendly" senza dubbio si).

ancora complimenti :applausi: per il lavoro e grazie per la condivisione.
Ciao,
Mario
 

alfrimpa

VBA Expert
Supermoderatore
Expert
18 Dicembre 2015
19.312
713
66
Napoli
2013
356
Ciao Giuliano

"E che te lo dico affaà?" MiInchino

Un lavoro da vero professionista quale sei del resto.
 

giulianovac

Access/VBA Expert
Expert
9 Giugno 2018
2.233
83
Italy
2013 2019
166
Marius44 @Marius44
alfrimpa @alfrimpa
Grazie di cuore :gongolo:
 

klingklang

Ciappinaro VBA_Expert
Expert
20 Ottobre 2017
4.795
113
42
San Giovanni in Persiceto (BO)
www.excelswissknife.com
2016, 365
325
Finalmente sono riuscito a guardarlo. Già da come è scritto il codice se ne intuisce la qualità, e soprattutto si tratta di un programmino che può essere utilissimo a tante persone. Complimenti davvero, e appena potrò studiarlo più a fondo ti farò qualche domanda specifica! :applausi::applausi::applausi:
 

ges

Excel/VBA Expert
Amministratore
Expert
21 Giugno 2015
20.197
1.733
Como
2011MAC 2016WIN
413
Complimenti, un grande tutorial.PollicioneInSu:applausi:
 

Rubik72

Excel/VBA Expert
Supermoderatore
Expert
12 Dicembre 2015
5.634
183
47
Cosenza
Excel 2016
190
Complimenti a Giuliano per questo nuovo tutorial :applausi:.

Piccolo appunto. In questi casi conviene dichiarare le variabili come oggetti generici e richiamarli in Early Binding affinché sia compatibile su ogni macchina, in particolare gli oggetti che fanno riferimento a MSWord (io sono uno di quelli che non hanno la versione 15.0 ma 14.0 quindi il Dedug si "arrabbia" non trovando la libreria).

P.S. Anche la tabella di Word ha una proprietà simile a Excel che fa riferimento alla cella ed è Cell, quindi si poteva scrivere anche:
Codice:
xTabella.Cell(y, x).Range.Text = sh.Cells(y, x + 5)
 

giulianovac

Access/VBA Expert
Expert
9 Giugno 2018
2.233
83
Italy
2013 2019
166
Rubik72 @Rubik72
Sì, condivido la tua osservazione, in realtà non volevo complicare troppo il tutorial ad utenti 'poco pratici' sulla questione early-binding/late-binding (si vedono tante castronerie sul web su questo argomento).
Però visto che lo gradite posso publicare una progetto 'variante 2' che lo implementi.
 
  • Like
Reactions: Rubik72

giulianovac

Access/VBA Expert
Expert
9 Giugno 2018
2.233
83
Italy
2013 2019
166
Ciao a tutti!

VARIANTE 2

Alcuni utenti hanno lamentato il fatto che se la versione di Office è diversa da quella usata per lo sviluppo si trovano costretti a modificare i Riferimenti del progetto.
Caso abbastanza comune, quando si distribuiscono progetti e applicazioni a terzi che utilizzano versioni differenti di librerie referenziate.
Questo perché è stato usato l'early-binding (associazione preventiva) che gioco-forza può solo referenziare le librerie in uso a chi sviluppa il progetto.

Per cui, come richiesto, ho provveduto a creare una variante 2 del progetto che funzionerà su qualsiasi versione di Office perché non è legata ad una versione specifica, ovvero ho usato il late-binding.

Se distribuite i progetti a terzi allora è importante avere familiarità con questi concetti per cui segnalo questo articolo (in lingua inglese):

In azienda io devo usare solo il late-binding in quanto i nostri utenti hanno versioni diverse di Office (dalla 2007 alla 365).

NELLA PRATICA COSA AVVIENE?
Ma, nella pratica, cosa significa usare l'early-binding o il late-binding?
Early-binding
Quando dobbiamo usare un oggetto, di cui abbiamo referenziato la libreria, come nel caso specifico: Word, noi dichiariamo una variabile oggetto così:
Dim xWord As Word.Application
Cioè stiamo ordinando al VBA di creare un oggetto xWord di tipo Word.Application.

Poi, per accedere ai relativi metodi e proprietà, digitiamo il nome della variabile oggetto, xWord, seguita dal punto e l'Intellisense ce le elenca in ordine alfabetico:


L'Intellisense è estremamente comodo, considerando il fatto che permette di accedere a qualsiasi membro di qualsiasi oggetto seguendo a cascata la gerarchia degli oggetti stessi.
Un'altro vantaggio è che con l'early-binding possiamo accedere tramite il Visualizzatore Oggetti del VBA (basta premere F2) e si potranno esplorare tutti i membri di ogni libreria referenziata, in particolare alle costanti di enumerazione e più avanti vedremo quanto tale caratteristica sia fondamentale!

Late-Binding
Invece quando vogliamo usare il late-binding, dobbiamo dichiarare una variabile oggetto così:
Dim xWord As Object
Di conseguenza, l'Intellisense non funziona più perché per il VBA xWord è un oggetto generico, non tipizzato.
Infatti siamo costretti ad usare la seguente istruzione per comunicare al VBA quale tipo di oggetto rappresenterà la variabile oggetto xWord:
Set xWord = CreateObject("Word.Application")



VANTAGGI E SVANTAGGI
Come ho già scritto, il grande vantaggio dei progetti distribuiti che usano il late-binding è che vengono considerati Version Indipendent ovvero possono essere eseguiti su qualsiasi versione di Office.
Per contro, perdendo l'Intellisense, non avremo più il comodo controllo sugli oggetti e relativi membri, ma anche sulla sintassi! Insomma, diventa più complesso scrivere il codice.



Argomenti di compilazione condizionale
Per nostra fortuna, non dobbiamo disperare, perché possiamo eliminare gli svantaggi e sfruttare i vantaggi contemporaneamente!
Infatti, quasi tutti gli ambienti di progettazione come il VB.NET, C#, ma anche il vecchio VB6 ed il VBA non fa eccezione dispongono di una caratteristica utilissima, ma poco conosciuta e ancor meno utilizzata: gli Argomenti di compilazione condizionale.

Per chi non li conosce, occorre andare su Strumenti ed aprire la finestra Proprietà di.. <nome del progetto>:


Come vedete, nell'apposita casella (immagine 1) ho inserito:
EarlyBinding = 1
In pratica, si deve semplicemente indicare una stringa ed assegnarle un valore.
Nel caso specifico vogliamo comunicare al progetto che EarlyBinding è uguale a 1.
Questa è una dichiarazione di una costante particolare chiamata in gergo tecnico costante condizionale.

Nell'immagine 2 vediamo invece come possiamo sfruttare questa costante per capire se stiamo usando l'early-binding oppure il late-binding.
Notare che per verificare il valore di una costante condizionale dobbiamo utilizzare il costrutto condizionale If/Then/EndIf ma in cui le parole chiave sono precedute da un cancelletto:
Visual Basic:
#If ... Then
    ...
#Else
    ...
#End If
In pratica avviene che a seconda del valore attribuito alla costante EarlyBinding (immagine 1) le variabili oggetto saranno dichiarate in modo differente:
1) se impostate EarlyBinding = 1 allora il VBA utilizzerà la libreria referenziata (Word.Application)
2) se impostate EarlyBinding = 0 allora il VBA NON userà tale libreria (la ignora completamente)

La dimostrazione di ciò la potete verificare immediatamente nella finestra di codice:
Se digitate xWord.:
- nel primo caso otterrete l'Intellisense.
- nel secondo caso non otterrete un bel nulla.

A questo punto credo sia sufficientemente chiaro che il fatto di usare o meno le librerie referenziate dipende esclusivamente dal valore della costante condizionale EarlyBinding.
Per cui, come ho affermato più sopra, se vogliamo l'Intellisense imposteremo EarlyBinding =1, poi prima di distribuire il progetto impostiamo EarlyBinding =0.

Chiaramente, quando sarà il momento di utilizzare la variabile oggetto xWord si dovrà usare il costrutto di cui sopra:
Visual Basic:
        #If EarlyBinding = 1 Then
            Set xWord = New Word.Application
        #Else
            Set xWord = CreateObject("Word.Application")
        #End If

IMPORTANTE
Per vostra comodità, nel progetto di esempio allegato ho impostato
EarlyBinding = 0
così se avete una versione diversa dalla mia (Office 2013) non avrete problemi.
Se volete usare l'Intellisense ricordatevi di impostare
EarlyBinding = 1


COSTANTI
Come accennavo prima c'è un ulteriore intoppo da superare ovvero le costanti di enumerazione fornite dalla libreria, in questo caso di Word. Infatti, togliendo il riferimento a tale libreria, le costanti non saranno più disponibili per cui è necessario dichiararle esplicitamente.
Nel caso del progetto in questione, all'inizio del form ho aggiunto le dichiarazioni che ho utilizzato:
Visual Basic:
Const wdFormatPDF = 17
Const wdFormatDocumentDefault = 16
Const wdDoNotSaveChanges = 0
Ecco che qui ci aiuta il Visualizzatore oggetti perché ci permette di conoscere il valore delle costanti enumerate e con un semplice copia & incolla inserirle nel nostro progetto.


CONCLUSIONE
Spero di aver illustrato nei dovuto modi questa caratteristica che, almeno per quanto mi riguarda, ritengo estremamente comoda.
L'esempio qui discusso è relativo a Word, ma sappiate che può essere esteso a qualsiasi applicazione di Office Automation non solo della suite Office di Microsoft, ma anche di altre applicazioni di terze parti che espongono librerie di oggetti, come ad esempio, una per tutte, AutoCAD anche se per certi versi il linguaggio non è strettamente uguale al VBA ma è diciamo così proprietario.

Spero di non aver dimenticato niente, ma nel caso sono sicuro che qualcuno ci penserà.
 

Allegati

Ultima modifica:

giulianovac

Access/VBA Expert
Expert
9 Giugno 2018
2.233
83
Italy
2013 2019
166
AGGIORNAMENTO

Un'tente mi ha segnalato che, aprendo il file database.xlsx il file è vuoto!
In realtà non è vuoto ma il foglio è solo nascosto.

In effetti nel progetto c'è una dimenticanza nel codice, nel senso che prima nascondo la finestra con l'istruzione:
wb.Windows(1).Visible = False ' lo nascondo

ma poi, prima di chiudere tutto, manca l'istruzione per ripristinare il foglio, basta aggiungerla:
wb.Windows(1).Visible = True ' ripristino lo stato della finestra
 
  • Like
Reactions: gighen
Stato
Chiusa ad ulteriori risposte.

Sostieni ForumExcel

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