Stampa Unione in Excel - Etichette indirizzi. - pagina vista: volte

Ovvero: Creazione di una procedura per la stampa di etichette indirizzi, simile alla Stampa Unione presente in Word.

Premessa.
Su questo sito, sezione "Lavori dei lettori", all'articolo su "Conti Marcello", è possibile scaricare e consultare un suo lavoro che tratta appunto di "Stampa Etichette". Io non ho voluto consultare le procedure da lui usate per non lasciarmi influenzare nella scelta dei passi da seguire nella realizzazione di questo articolo, e comunque anche le soluzioni che propongo mirano ad ottenere un'opzione che su Excel non esiste: la Stampa Unione.

Gli esempi delle routines che vedrete saranno da adattare alle esigenze del lettore, in particolare a causa del tipo di etichetta che si intenderà adoperare, ma lo scopo dell'articolo è quello di fornire la sequenza dei punti necessari da prendere in considerazione per lo sviluppo del progetto, e relative possibili soluzioni e commenti.

Alla base del progetto ovviamente c'è la necessità di sfruttare un elenco indirizzi presenti su un foglio di lavoro, come ad esempio un foglio "ClientI" dove in genere sono riportati i campi da utilizzare in una stampa etichette, come "Cognome", "Nome", "indirizzo", "Città". Non ha molta importanza la sequenza con cui questi "campi" appaiono nella tabella (database) di un foglio "Clienti". visto che nel ciclo usato per reperire i campi da usare per la stampa etichette possiamo "mirare" alla colonna precisa che ospiterà ognuno dei campi da usare. Sotto vediamo un tradizionale "elenco clienti", posto sul foglio1, sulla Riga1 le intestazioni di colonna (campi):

Altra cosa importante è decidere il tipo di etichetta che vorremo usare, per poter dimensionare le celle di un foglio in modo tale che corrispondano alle misure dell'etichetta stessa. Esistono molti tipi di etichette commerciali in circolazione, io mi riferisco a etichette autoadesive, che vengono fornite su fogli formato A4, dove le etichette sono pretagliate. In questo specifico esercizio, mi riferisco alle etichette Modello "0689 N 6338", serie "TAK-TO" della Ditta "BUFFETTI", etichette le cui dimensioni sono mm 63,5 x 38,1 mm, molto adatte, secondo me (io uso questo formato), per ottenere etichette con testo ben visibile e di dimensioni giuste in rapporto alle tradizionali buste da lettera. Sotto, evidenziata in rosso, l'immagine del modello delle etichette scelte, presente sulla confezione:

N.B.: come si nota, nel layout delle etichette scelte esistono i bordi pagina, bordi pagina che dovremo considerare quando imposteremo il layout del PageSetup del foglio usato per la composizione degli indirizzi. (Esistono in commercio fogli di etichette audoadesive a tutta pagina, senza bordi; in quei casi, dovremo settare le dimensioni bordi pagina a zero).

Procedure e considerazioni.

Per prima cosa pensiamo ed impostare, nel foglio di lavoro che useremo per la composizione etichette, la dimensione delle celle in modo da simulare la reale dimensione delle  etichette; la dimensione standard di una cella è espressa in punti e corrisponde a 12,75 come altezza riga e 8,43 come larghezza colonna; noi dovremo impostare, per il modello di etichetta scelta, una larghezza colonna a 32,86 punti mentre per l'altezza cella imposteremo 107 punti.

Come facciamo ad impostare queste misure? La prima volta conviene agire manualmente, aiutandosi con "Anteprima di stampa".

• Imposteremo ad occhio una certa larghezza delle tre colonne che formano il layout, ed una certa altezza delle prime sette righe (tante sono le colonne e le righe di etichette del modello scelto), e dal menù Bordi, sceglieremo di bordare tutte le 21 celle, questo per garantirci di vedere le celle in Anteprima Stampa. Dovremo poi, a fine settaggi, ricordarci di togliere i bordi altrimenti verrebbero stampati.

• poi dal Menù File sceglieremo "Anteprima di Stampa" e nella pagina di anteprima, sceglieremo "Imposta/Margini" dove dovremo settare le misure corrispondenti ai margini del modello foglio di etichette scelto tenendo presente che in questa finestra le misure sono da considerare espresse in centimetri; per il modello usato in questo articolo abbiamo margini: sup. 1,5 - inf. 1,5 - sin. 0,7 - dex. 0,3. Dovremo poi impostare a zero (0) le voci "Intestazione" e "Piè di pagina".

• E' in Anteprima che controlleremo se le misure impostate alle celle sono sufficienti a riempire tutta l'anteprima di stampa (a parte i bordi) e quindi agire per modificare dette misure ritornando sul foglio per ritoccare le misure celle, e quindi di nuovo in anteprima, fino a che non completeremo l'area etichette in maniera ottimale. E' chiaro che dovremo approssimare le misure fino a che non otterremo un soddisfacente dimensionamento (agendo se necessario anche sulle misure dei bordi).

• Quando avremo terminato questa parte potremo rilevare le misure delle celle ed usarle poi in istruzione appropriata, nel frattempo se avremo provato una stampa su foglio comune A4, otterremo una bozza che, sovrapposta al modello di etichette scelto, ci consente di controllare, in trasparenza, se gli indirizzi verranno stampati giusti nelle aree etichette, esempio:

Togliamo i bordi alle celle, e proseguiamo.

Sappiamo che è possibile modificare l'alloggiamento del testo in una cella; intanto, visto che vorremo ottenere i dati allineati su più righe (nelle etichette) imposteremo l'opzione "Testo a capo" in ogni cella, inoltre imposteremo l'allineamento orizzontale del testo a: "A sinistra (rientro)" e quello verticale a : "Al centro"; imposteremo poi il "Rientro" al valore 1 o 2 che ci consente di non finire con la stampa sul bordo estremo sinistro dell'etichetta stessa. Più alziamo il valore di "rientro" e più il testo si sposterà verso destra, attenzione quindi a nomi o indirizzi troppo lunghi. Agendo sul rientro e sulla dimensione del carattere dovremo fare in modo da contenere tutto l'indirizzo nell'etichetta. Queste impostazioni potranno essere fatte manualmente dal menù "Opzioni cella", ma noi le eseguiremo via codice vba.

Come facciamo a trasferire i dati di ogni Cliente, che sono su una unica riga, su più righe in una unica cella? Semplicemente usando il segno di concatenazione (&) per "legare" insieme i campi di origine, e la costante Vblf _  per andare a capo separando il nome dall'indirizzo e dalla città. In pratica con la concatenazione otteniamo una unica stringa formata dai campi che ci interessano, e spostiamo a capo la/le parti della stringa che devono figurare su righe sottostanti. Considerando l'altezza dell'etichetta, sarà possibile inserire anche una formula come "Spett.le" oppure "Sig.", ecc., il che fa 4 righe in totale. Vediamo un esempio assegnato alla variabile "Eti":

• Eti = "Spett.le" & vbLf _    'inseriamo la formula di inizio, e andiamo a capo
  & UCase(Sheets(1).Cells(V, 1) & " " & Sheets(1).Cells(V, 2)) & vbLf _  'con UCase rendiamo maiuscolo Cognome e Nome e andiamo a capo
  & Sheets(1).Cells(V, 3) & vbLf _        'qui l'indirizzo, e a capo
  & Sheets(1).Cells(V, 4)                     'qui la città

e queste istruzioni restituiscono questo risultato sul testo.

Ma procediamo con ordine: intanto se vogliamo automatizzare tutta la procedura, dovremo iniziare a contare, sul foglio clienti (nell'esempio il Foglio  1) il numero dei nominativi inseriti, per determinare quante saranno le celle da destinare ad etichetta e prepararne la formattazione (sopra citata ) e per quanto riguarda orientamento e disposizione del testo. Visto che dovremo sapere, impiegando tre colonne, quante righe saranno occupate sul foglio etichette, dividiamo il numero dei nominativi per 3; inoltre, dal momento che una divisione di un intero per 3, se l'intero non è un multiplo di 3, restituisce un numero con decimali, useremo l'operatore Mod eseguendo una verifica: se l'intero diviso per 3 restituisce zero, occuperemo tante righe quante ottenute dalla divisione del numero nominativi / 3,in caso contrario aggiungeremo 1 riga che conterrà solo una o due etichette. Usiamo quindi una variabile (Uro : ultima riga occupata) che restituisce il numero dell'ultima riga occupata del campo "Cognome" , cioè la colonna A (sul Foglio1):

• Uro = Sheets(1).[A65536].End(xlUp).Row   'Uro sarà uguale al numero dell'ultima riga occupata, colonna A, Foglio1, e ci servirà anche come limite superiore del ciclo che leggerà i nominativi per realizzare le etichette.

ma usiamo Uro anche per la verifica con l'operatore Mod: attenzione, la variabile Uro restituisce il numero dell'ultima riga occupata, quindi tutto l'elenco, ma nell'elenco abbiamo usato la prima riga come intestazioni di colonna, e le intestazioni non devono figurare nel conteggio per determinare quante etichette stampare; toglieremo quindi 1 da Uro per eseguire i conteggi:

• If (Uro - 1) Mod 3 = 0 Then
  Nriga = (Uro - 1) / 3
  Else
  Nriga = Int(Uro / 3) + 1
  End If

Bene, la variabile Nriga ora la useremo per selezionare l'area sul foglio etichette a cui applicare la formattazione, (allineamento testo, ritorno, ecc.) e la dimensione celle (larghezza delle tre colonne e altezza righe). Nell'esempio che segue imposto anche la dimensione carattere a 12 (di defalut è 10):

• Range("A1:C" & Nriga).Select   'il Range ora sarà formato dall'area A1 : C Nriga, e lo selezioniamo per impostare i formati alle celle
  With Selection
  .RowHeight = 107   'impostiamo l'altezza celle
  .ColumnWidth = 32.86     'impostiamo la larghezza colonne
  .HorizontalAlignment = xlLeft        'impostiamo l'allineamento del testo a sinistra
  .VerticalAlignment = xlCenter       'impostiamo tutto il testo centrale come allineamento verticale
  .WrapText = True         'testo a capo
  .AddIndent = False
  .IndentLevel = 2           'valore rientro
  .Font.Size = 12             'dimensione carattere
  End With

Dopo queste istruzioni inseriremo un ciclo For...Next che iniziando da 2 (il numero della riga da cui iniziano i nominativi sul Foglio1) scorra fino al numero riga ottenuto con la variabile Uro, inserendo in ogni cella-etichetta i dati raccolti con la variabile "Eti" (vista sopra). Vedremo le istruzioni nella procedura completa.

Una volta completato il riempimento degli indirizzi nelle celle-etichetta dovremo passare alla stampa. Le istruzioni per la stampa le potremo inserire a seguire le istruzioni viste sopra, oppure decidere di usare una seconda macro da lanciare dopo che avremo controllato che il primo passaggio sia tutto regolare. Per comodità del lettore ho separato le due procedure, comunque per quanto concerne la stampa, imposteremo le istruzioni per impostare il layout di stampa (distanze dai bordi, ecc.) e le istruzioni che leggeranno tutta l'area celle-etichette da stampare. Inoltre inseriamo, a fine procedura stampa, le istruzioni per cancellare le celle-etichette stampate. Le/la macro le lanceremo dal foglio etichette.

Procedura ComponiEtichette :

Ricapitolando quindi, per modificare il testo (in funzione della lunghezza) nelle etichette potremo variare il valore di rientro e la dimensione del carattere, mentre per quanto riguarda le dimensioni cella tutto dipenderà dal tipo di etichette di cui disporremo; questo influenzerà anche il layout di stampa, e occorrerà agire sull'anteprima di stampa per ottimizzare il dimensionamento del layout stesso.

Suggerimento: per ottenere il codice relativo alle impostazioni fatte in "Anteprima di stampa / imposta/margini", consiglio di eseguire tutti gli aggiustamenti manualmente; una volta ottimizzato il tutto, uscire da "Anteprima di stampa" e dal foglio lanciare il "Registratore di macro", richiamare "Anteprima di stampa" "Imposta/margini", e senza toccare altro premere OK e uscire da anteprima, stoppando il registratore. Troverete la macro già compilata con i valori giusti. Alcune proprietà di PageSetup sono ininfluenti ai fini di queste stampe e li potremo eliminare, e li evidenzio in rosso nella procedura sottostante.

Procedura StampaEtichette :

(**) Le misure dei margini in vba vengono espresse in InchesToPoints, metodo che restituisce la conversione tra pollici (sistema di misura inglese) e punti, valore di tipo Double. Sappiamo che 1 pollice equivale a 25,4 mm (arrotondato) è semplice fare a mano un'eventuale correzione, basta infatti dividere la misura che vorremo impostare ad un bordo, espressa in mm , diviso 25,4 per ottenere il valore espresso in InchesToPoints: se infatti vorremo un margine impostato a mezzo centimetro (sono 0,5 ovvero 5 millimetri) faremo 5 / 25,4 ed otterremo 0,196850393700787; questo sarà il valore da assegnare al relativo margine nelle istruzioni. Se invece useremo misure espresse in cm la divisione dovrà essere fatta per 2,54.

Chiaramente le procedure si intendono per foglio dedicato alla stampa etichette e dove NON siano presenti altre celle occupate, in particolare sotto le celle-etichetta, colonna A (altrimenti si sballa il valore ottenuto con la variabile Urs) .

Buon lavoro.

prelevato sul sito www.ennius.altervista.org