Sostituisci dati da Excel in Word
EnFo
vi disturbo ancora per chiedere aiuto, ho il seguente problema:
sto predisponendo un file in Excel i cui dati devono essere esportati in
un file Word (e la stampa unione non è proprio quello che fa per me)
così ho fatto (a dir la verità è con un grande aiuto da parte di mio
fratello) una macro che genera un nuovo documento Word da un modello
incorporato nel file Excel e fin qui funziona tutto orrettamente.
Nel file Word ci sono centinaia di sigle (#ANSO0001#, #ANPP0001#,
#ANPP00002#, #ANPC00002#, #BPC00001#,#BPP00001#,#BDE00001#, ecc. ecc.)
di diversi colori in base al foglio di origine, tali sigle vengono
sostituite dai dati contenuti nelle specificate celle di Excel, tutto
avviene correttamente (anche per quanto riguarda il formato uguale a
quello visualizzato in Excel).
Quello che vorrei fare è però colorare di nero (automatico) il dato
importato visto che una volta importato mantiene il colore della sigla
che ha sostituito e non posso nemmeno fare "seleziona tutto" ecc. ecc.
perché il documento ha anche altre formattazioni.
Inoltre dovrei fare le sostituzioni di cui sopra anche nell'intestazione
e nel pie' di pagina. Sapreste cortesemente aiutarmi?
Ecco la macro fatta su un file di prova:
========================================================================
Option Explicit
Const wdReplaceAll = 2
Private Sub cmdCreaFile_Click()
Dim objIncorporato As Object
Dim objDoc As Object
Set objIncorporato = Worksheets("Foglio1").OLEObjects(1)
Set objDoc = CreateObject("Word.Document")
'Apre l'oggetto incorporato
objIncorporato.Activate
'Copia tutto il contenuto dell'oggetto incorporato
objIncorporato.Object.Application.Selection.WholeStory
objIncorporato.Object.Application.Selection.Copy
'Chiude l'oggetto incorporato
objIncorporato.Object.Application.ActiveDocument.Close
'Incolla il conteuto nel nuovo file
objDoc.Application.Selection.WholeStory
objDoc.Application.Selection.Paste
'Ricerca la sigla specificata e la sostituisce con il contenuto
della cella A2
objDoc.Application.Selection.WholeStory
With objDoc.Application.Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "#ANSO0001#"
.Replacement.Text = Format(Worksheets("Foglio1").Range("A2"))
End With
objDoc.Application.Selection.Find.Execute Replace:=wdReplaceAll
'Ricerca la sigla specificata e la sostituisce con il contenuto
della cella A4
objDoc.Application.Selection.WholeStory
With objDoc.Application.Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "#ANPC0001#"
.Replacement.Text = Format(Worksheets("Foglio1").Range("A4"),
"d MMMM yyyy")
End With
objDoc.Application.Selection.Find.Execute Replace:=wdReplaceAll
'Ricerca la sigla specificata e la sostituisce con il contenuto
della cella A4
objDoc.Application.Selection.WholeStory
With objDoc.Application.Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "#ANPC0002#"
.Replacement.Text = Format(Worksheets("Foglio1").Range("A4"),
"dd/MM/yyyy")
End With
objDoc.Application.Selection.Find.Execute Replace:=wdReplaceAll
'Ricerca la sigla specificata e la sostituisce con il contenuto
della cella A6
objDoc.Application.Selection.WholeStory
With objDoc.Application.Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "#ANPP0002#"
.Replacement.Text = Format(Worksheets("Foglio1").Range("A6"),
"dd/MM/yyyy")
End With
objDoc.Application.Selection.Find.Execute Replace:=wdReplaceAll
'Ricerca la sigla specificata e la sostituisce con il contenuto
della cella A8
objDoc.Application.Selection.WholeStory
With objDoc.Application.Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = ("#BPC00001#")
.Replacement.Text = Format(Worksheets("Foglio1").Range("A8"),
"#,##0;(#,##0)")
End With
objDoc.Application.Selection.Find.Execute Replace:=wdReplaceAll
'Ricerca la sigla specificata e la sostituisce con il contenuto
della cella A10
objDoc.Application.Selection.WholeStory
With objDoc.Application.Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "#BPP00001#"
.Replacement.Text = Format(Worksheets("Foglio1").Range("A10"),
"#,##0;(#,##0)")
End With
objDoc.Application.Selection.Find.Execute Replace:=wdReplaceAll
objDoc.Application.Selection.Find.Execute Replace:=wdReplaceAll
'Ricerca la sigla specificata e la sostituisce con il contenuto
della cella A12
objDoc.Application.Selection.WholeStory
With objDoc.Application.Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "#BDE00001#"
.Replacement.Text = Format(Worksheets("Foglio1").Range("A12"),
"#,##0;(#,##0)")
End With
objDoc.Application.Selection.Find.Execute Replace:=wdReplaceAll
'Visualizza il file di Word
objDoc.Application.Selection.HomeKey
objDoc.Application.Activate
End Sub
========================================================================
Grazie mille.
Enrico
Abbassa LA VOCE per rispondermi
Giovanni Zezza
scriveva:
>sto predisponendo un file in Excel i cui dati devono essere esportati in
>un file Word (e la stampa unione non è proprio quello che fa per me)
>...
>Quello che vorrei fare è però colorare di nero (automatico) il dato
>importato visto che una volta importato mantiene il colore della sigla
>che ha sostituito e non posso nemmeno fare "seleziona tutto" ecc. ecc.
>perché il documento ha anche altre formattazioni.
Non mi è chiaro perché la stampa unione non sia "proprio quello che fa
per te", dal momento che alla fine ne duplichi sostanzialmente il
comportamento, comunque Find può sostituire anche la formattazione:
objDoc.Application.Selection.WholeStory
With objDoc.Application.Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Replacement.Font.ColorIndex = wdBlack
.Text = "#ANSO0001#"
.Replacement.Text = Format(Worksheets("Foglio1").Range("A2"))
.Format = True
End With
objDoc.Application.Selection.Find.Execute Replace:=wdReplaceAll
>Inoltre dovrei fare le sostituzioni di cui sopra anche nell'intestazione
>e nel pie' di pagina. Sapreste cortesemente aiutarmi?
Le ricerche e sostituzioni nelle intestazioni e nei piè di pagina si
fanno esattamente come nel resto del documento. Si tratta solo di
usare il range giusto. Si può usare l'insieme StoryRanges di Document:
Dim brano as Range
set brano = objDoc.StoryRanges(wdPrimaryHeaderStory)
With brano.Find
....
End With
I valori possibile per l'indice di StoryRanges sono: wdMainTextStory,
wdFootnotesStory, wdEndnotesStory, wdTextFrameStory,
wdEvenPagesHeaderStory, wdPrimaryHeaderStory, wdEvenPagesFooterStory,
wdPrimaryFooterStory, wdFirstPageHeaderStory, wdFirstPageFooterStory
Ciao.
EnFo
Ciao Giovanni, intanto grazie per aver risposto; beh forse non conosco
bene la stampa unione, ma tale funzionalità l'ho usata, poco a dir la
verità, per inserire dei dati diversi tra loro in un medesimo punto di
un documento che si "duplicava" in base al numero dei dati (es. nome,
cognome e indirizzo in una circolare da inviare ai clienti), ma
sopratutto partivo da un documento word che andava a prelevare i dati da
un file excel e i dati in questo file dovevano essere sempre nel primo
folgio da sx. Nel mio file, intanto i dati sono contenuti in vari fogli,
ma sopratutto tempo fa avevo provato a incorporare in excel un file word
dove l'origine dati era lo stesso file excel dove era incorporato e non
mi funzionava nulla.
Cmq grazie per i codici, mio fratello li ha cmq dovuti adattare in
quanto la macro sebbene lavori su word, è generata da excel e di
conseguenza ha dovuto usare i comandi di excel, non sempre uguali e
conformi a word.
Cmq grazie ancora, e a chi potesse interessare ecco il codice:
=======================================================================
Option Explicit
Const wdReplaceAll = 2
Const wdPrimaryHeaderStory = 7
Const wdSeekCurrentPageHeader = 9
Const wdSeekCurrentPageFooter = 10
Const wdSeekMainDocument = 0
Public Sub Sostituisci()
Dim objIncorporato As Object
Dim strFile As String
Dim objDoc As Object
strFile = ActiveWorkbook.Path & "\Prova 01 " &
Worksheets("Foglio1").Range("B4") & " - " &
Worksheets("Foglio1").Range("A2") & ".doc"
Set objIncorporato = Worksheets("Foglio1").OLEObjects(1)
Set objDoc = CreateObject("Word.Document")
'Apre l'oggetto incorporato
objIncorporato.Activate
'Copia tutto il contenuto dell'oggetto incorporato
objIncorporato.Object.Application.ActiveDocument.SaveAs
Filename:=strFile
'Chiude l'oggetto incorporato
objIncorporato.Object.Application.ActiveDocument.Close
Set objDoc = objDoc.Application.Documents.Open(strFile)
Set objDoc = objDoc.Application.Documents(1)
objDoc.Application.Documents(2).Close
Call Intestazione(objDoc)
Set objIncorporato = Nothing
Set objDoc = Nothing
End Sub
Private Function Intestazione(ByRef objX As Object) As Boolean
On Error GoTo GestioneErrori
'Attiva l'intestazione di pagina
objX.Application.Windows(1).View.SeekView = wdSeekCurrentPageHeader
'Ricerca la sigla specificata e la sostituisce con il contenuto
della cella A2
With objX.Application.Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Replacement.Font.ColorIndex = 0
.Text = "#ANSO0001#"
.Replacement.Text = Format(Worksheets("Foglio1").Range("A2"))
End With
objX.Application.Selection.Find.Execute Replace:=wdReplaceAll
'Attiva il corpo del documento
objX.Application.Windows(1).View.SeekView = wdSeekMainDocument
Intestazione = True
Exit Function
GestioneErrori:
Intestazione = False
End Function
=======================================================================
Giovanni Zezza
>Nel mio file, intanto i dati sono contenuti in vari fogli,
Questo non è un problema: in stampa unione si possono riferire quanti fogli
si vogliono nella stessa cartella.
>ma sopratutto tempo fa avevo provato a incorporare in excel un file word
>dove l'origine dati era lo stesso file excel dove era incorporato e non
>mi funzionava nulla.
Ci credo, ma a questo punto non mi è chiaro perché dovresti incorporare in
Excel un file Word che, di più, riferisce la stessa cartella Excel. La mia
sensazione è che tu abbia scelto la strada più complicata per ottenere
quello che vuoi, ma a questo punto è probabile che ti convenga continuare
così, piuttosto che rivoluzionare tutta la procedura.
>Cmq grazie per i codici, mio fratello li ha cmq dovuti adattare in
>quanto la macro sebbene lavori su word, è generata da excel e di
>conseguenza ha dovuto usare i comandi di excel, non sempre uguali e
>conformi a word.
I metodi sono sempre i metodi di Word, e quindi sono sempre uguali, da
qualsiasi applicazione e con qualsiasi linguaggio vengano chiamati. Al più,
bisognerà recuperare le costanti (ma dovrebbe essere automatico, in vba).
Tuo fratello ha semplicemente scelto un'altra strada (neanche a dirlo, più
complicata: dev'essere un carattere di famiglia) per fare le stesse cose.
>Private Function Intestazione(ByRef objX As Object) As Boolean
>On Error GoTo GestioneErrori
> 'Attiva l'intestazione di pagina
> objX.Application.Windows(1).View.SeekView = wdSeekCurrentPageHeader
> 'Ricerca la sigla specificata e la sostituisce con il contenuto
>della cella A2
> With objX.Application.Selection.Find
Se invece di ostinarsi ad usare Selection, si fosse usato, come suggerito,
un Range, la stessa cosa si otteneva più semplcimente con:
Private Function Intestazione(ByRef objX As Object) As Boolean
Dim brano as Range
On Error GoTo GestioneErrori
set brano = objx.StoryRanges(wdPrimaryHeaderStory)
With brano.Find
....
End With
End Function
Ciao.
EnFo
> EnFo, nel messaggio <zh1Rf.30867$A83.8...@twister1.libero.it>, scriveva:
>
>>Nel mio file, intanto i dati sono contenuti in vari fogli,
>
> Questo non è un problema: in stampa unione si possono riferire quanti fogli
> si vogliono nella stessa cartella.
>
Grazie della dritta, approfondirò appena ho un attimo di tempo... non si
finisce mai di imparare.
>>ma sopratutto tempo fa avevo provato a incorporare in excel un file word
>>dove l'origine dati era lo stesso file excel dove era incorporato e non
>>mi funzionava nulla.
>
> Ci credo, ma a questo punto non mi è chiaro perché dovresti incorporare in
> Excel un file Word che, di più, riferisce la stessa cartella Excel. La mia
> sensazione è che tu abbia scelto la strada più complicata per ottenere
> quello che vuoi, ma a questo punto è probabile che ti convenga continuare
> così, piuttosto che rivoluzionare tutta la procedura.
>
Perchè il file non l'ho fatto solo per me, ma anche per persone che non
hanno grandi conoscenze informatiche e troverebbero un po' complicato
(lo dico perchè ci ho già provato con altri file) aprire un file word
(che poi in realtà sono una decina) che pesca i dati da un file excel.
Inoltre se qualcuno accidentalmente cancellasse un campo "mergefield", o
anche solo decidesse di cambiare destinazione ad un file tutto il
"sistema" non funzionerebbe più correttamente.
Col mio sistema, da un unico file, e semplicemente cliccando un tasto,
una persona poco esperta ha risolto i problemi, almeno spero. Infatti la
macro apre il file incorporato lo salva con un nome definito nella
directory del file excel e poi effettua le sostituzioni con i dati
provenienti da excel, inoltre in aggiunta i vari file sono indipendenti.
In poche parole usa il file incorporato come una sorta di modello di
word e lo compila.
>>Cmq grazie per i codici, mio fratello li ha cmq dovuti adattare in
>>quanto la macro sebbene lavori su word, è generata da excel e di
>>conseguenza ha dovuto usare i comandi di excel, non sempre uguali e
>>conformi a word.
>
> I metodi sono sempre i metodi di Word, e quindi sono sempre uguali, da
> qualsiasi applicazione e con qualsiasi linguaggio vengano chiamati. Al più,
> bisognerà recuperare le costanti (ma dovrebbe essere automatico, in vba).
>
> Tuo fratello ha semplicemente scelto un'altra strada (neanche a dirlo, più
> complicata: dev'essere un carattere di famiglia) per fare le stesse cose.
>
Credevo anch'io, ma ad esempio per i colori del carattere ho trovato
delle differenze (es. nero=0, bianco=8, ... fino a 18) e il colore che
cercavo Blu Notte (in excel cod. 49) nella macro segnava errore se
invece mettevo il codice di word che non ricordo ma corrispondeva al
wdColorDarkTeal la macro segnava nuovamente errore. Ho risolto mettendo
il colore Blu Scuro codice 9 e tutto funziona corretamente. Cmq nei
riferimenti del VBA di excel non avevamo richiamato la libreria di word
sempre per il fatto che se qualcuno di quelli di cui sopra non l'avesse
spuntata o se avesse una versione differente potrebbe generarsi un
errore. Senza poi escludere che nell'utilizzare l'help mio fratello
potrebbe anche aver cercato nel posto sbagliato, non è un programmatore
ma un seplice autodidatta per "semplificarsi" il lavoro. Cmq è vero a
volte ci complichiamo la vita per niente. :-)
>>Private Function Intestazione(ByRef objX As Object) As Boolean
>>On Error GoTo GestioneErrori
>> 'Attiva l'intestazione di pagina
>> objX.Application.Windows(1).View.SeekView = wdSeekCurrentPageHeader
>> 'Ricerca la sigla specificata e la sostituisce con il contenuto
>>della cella A2
>> With objX.Application.Selection.Find
>
> Se invece di ostinarsi ad usare Selection, si fosse usato, come suggerito,
> un Range, la stessa cosa si otteneva più semplcimente con:
>
> Private Function Intestazione(ByRef objX As Object) As Boolean
> Dim brano as Range
> On Error GoTo GestioneErrori
> set brano = objx.StoryRanges(wdPrimaryHeaderStory)
> With brano.Find
> ....
> End With
> End Function
>
Con Range ha provato, ma non funzionava. Ora ho provato io e mi dà
questo errore: "Errore di compilazione - Argomento non facoltativo"
evidenziandomi ".Find" dopo "Intestazione", ma magari ho sbagliato
qualcosa io.
================================================================================
Private Function Intestazione(ByRef objX As Object) As Boolean
On Error GoTo GestioneErrori
Dim Intestazione As Range
Set Intestazione = objX.StoryRanges(wdPrimaryHeaderStory)
With Intestazione.Find <================
.ClearFormatting
.Replacement.ClearFormatting
.Replacement.Font.ColorIndex = 0
.Text = "#ANSO0001#"
.Replacement.Text = Format(Worksheets("Foglio1").Range("A2"))
End With
Exit Function
GestioneErrori:
Intestazione = False
End Function
================================================================================
> Ciao.
Ciao anche a Te.
Giovanni Zezza
>Credevo anch'io, ma ad esempio per i colori del carattere ho trovato
>delle differenze (es. nero=0, bianco=8, ... fino a 18) e il colore che
>cercavo Blu Notte (in excel cod. 49) nella macro segnava errore se
>invece mettevo il codice di word che non ricordo ma corrispondeva al
>wdColorDarkTeal la macro segnava nuovamente errore.
Non so, io lavoro abitualmente in Perl (un linguaggio, quindi, anche più
distante da Word del vba di Excel) e, a parte le particolarità del
linguaggio, c'è veramente poco da cambiare rispetto al corrispettivo codice
in vba di Word. E lo stesso codice gira di solito senza problemi su
macchine diverse e con Word di versioni diverse. Certo, comunque, le mie
non sono applicazioni critiche, e io sono di solito anche l'unico utente,
quindi se mai anche qualcosa andasse male so come rimediare.
>Con Range ha provato, ma non funzionava. Ora ho provato io e mi dà
>questo errore: "Errore di compilazione - Argomento non facoltativo"
>evidenziandomi ".Find" dopo "Intestazione", ma magari ho sbagliato
>qualcosa io.
No, ho sbagliato io: in Excel naturalmente non si può dichiarare una
variabile di tipo Range; o, meglio, formalmente si può, perché per
avventura anche in Excel esiste un Range, che però è un'altra cosa e si
comporta in un altro modo e non può essere usato per riferire un Range di
Word.
Questo è un aspetto a cui effettivamente bisogna stare attenti nel
passaggio fra Word ed Excel: ovviamente i tipi delle variabili possono
essere solo quelli nativi del linguaggio che si sta usando. Scrivendo a
memoria mi era sfuggito.
In Excel bisogna usare una variabile generica, di tipo Object:
>================================================================================
>Private Function Intestazione(ByRef objX As Object) As Boolean
>On Error GoTo GestioneErrori
> Dim Intestazione As Range
Dim Intestazione As Object
Così dovrebbe funzionare, anche se non ho verificato.
Ciao.
Tiziano Marmiroli
> No, ho sbagliato io: in Excel naturalmente non si può dichiarare una
> variabile di tipo Range; o, meglio, formalmente si può, perché per
> avventura anche in Excel esiste un Range, che però è un'altra cosa e si
> comporta in un altro modo e non può essere usato per riferire un Range di
> Word.
>
> Questo è un aspetto a cui effettivamente bisogna stare attenti nel
> passaggio fra Word ed Excel: ovviamente i tipi delle variabili possono
> essere solo quelli nativi del linguaggio che si sta usando. Scrivendo a
> memoria mi era sfuggito.
>
> In Excel bisogna usare una variabile generica, di tipo Object:
CUT
> Dim Intestazione As Object
>
> Così dovrebbe funzionare, anche se non ho verificato.
Probabilmente intendevi
Dim Brano As Object
Così funziona. Comunque, una volta impostati i riferimenti, si può anche
usare
Dim Intestazione As Word.Range
--
Tiziano Marmiroli
Microsoft MVP - Office System
Giovanni Zezza
<lp5b129isfdtnub9h...@4ax.com>, scriveva:
>Probabilmente intendevi
>
>Dim Brano As Object
Lui l'ha rinominata Intestazione, e io ho copiato, senza far caso che è in
conflitto con il nome della funzione. In effetti, questo spiega meglio il
messaggio di errore che ha ricevuto: "Errore di compilazione - Argomento
non facoltativo", che si riferisce appunto all'argomento di Intestazione,
interpretata come una chiamata di funzione, piuttosto che al tipo Range
nella dichiarazione della variabile (che comunque era sbagliata).
Purtroppo, lavorare a memoria espone a queste imprecisioni, ma del resto
non ho avuto tempo per verificare.
Ciao.
EnFo
> Tiziano Marmiroli, nel messaggio
> <lp5b129isfdtnub9h...@4ax.com>, scriveva:
>
>
>>Probabilmente intendevi
>>
>>Dim Brano As Object
>
>
> Lui l'ha rinominata Intestazione, e io ho copiato, senza far caso che č in
> conflitto con il nome della funzione. In effetti, questo spiega meglio il
> messaggio di errore che ha ricevuto: "Errore di compilazione - Argomento
> non facoltativo", che si riferisce appunto all'argomento di Intestazione,
> interpretata come una chiamata di funzione, piuttosto che al tipo Range
> nella dichiarazione della variabile (che comunque era sbagliata).
>
> Purtroppo, lavorare a memoria espone a queste imprecisioni, ma del resto
> non ho avuto tempo per verificare.
>
> Ciao.
Ora funziona! Come mi hai suggerito ho messo "Object" invece di "Range"
e la macro si esegue correttamente... in effetti č piů semplice di
quella fatta da mio fratello.
Cmq il nome della variabile uguale al nome della funzione non ha creato
nessun conflitto. Ora devo solo implementarla anche per il pič di pagina
e per il corpo principale.
Ho provato anche con "Word.Range" ma non funziona, mi dice "Tipo
definito dall'utente non definito" e mi evidenzia appunto "Intestazione
Word.Range" (ma ho provato anche con "Dim Prova as Word.Range"), ma
questo č perchč manca la libreria di word.
Grazie infinite.