Copy data from excel to word

[ExcelenteNovel]

Hello dear masters:
I hope that each and every one of the participants of the forum are well in health, I proceed to my request: It turns out that I have an Excel document that I will use as a database so that some of the captured in different sheets (in the example I put two sheets ) Can be pasted into a Word template in a sort of Correspondence Combination, but vice versa and when positioning, in either of the two sheets, the row containing the information I require in the template and executing the macro, me I created a new document with the concatenation of the text "Memo" + data of the field ID [of the row where I am located] + the text "SLC" + another secondary field + termination (.docx) and (.pdf) Of course filling The data that I frame in the template in Word.


I already have the macro, but something is missing to make it work. Initially in the tests if it worked (at least in saving the new document), but when going in the same it began to leave to the half.

Sub Solic_LC()


Dim WordApp As Object, Cell As Range
Dim Minuta As Range, Recepcion_SLC As Range, ID_Programa_Int As Range, _
Programa_Pres As Range, Programa_Tipo_Apoyo As Range, Año As Range, Institucion As Range, _
NoConvenioConvocatoria As Range, MontoReintegroSolicitado As Range, MetodoPago As Range, _
Cve_interna As Range, Cve_externa As Range, NumeroLineaCaptura As Range, dato_extra As Range, Programa As Range




With Sheets("Jovenes").Range("V4")


Minuta = Cells(ActiveCell.Row, 3)
Recepcion_SLC = Cells(ActiveCell.Row, 4)
ID_Programa_Int = Cells(ActiveCell.Row, 5)
Programa_Pres = Cells(ActiveCell.Row, 6)
Programa_Tipo_Apoyo = Cells(ActiveCell.Row, 7)
Año = Cells(ActiveCell.Row, 8)
Institucion = Cells(ActiveCell.Row, 9)
NoConvenioConvocatoria = Cells(ActiveCell.Row, 10)
MontoReintegroSolicitado = Cells(ActiveCell.Row, 11)
MetodoPago = Cells(ActiveCell.Row, 12)
Cve_interna = Cells(ActiveCell.Row, 13)
Cve_externa = Cells(ActiveCell.Row, 14)
NumeroLineaCaptura = Cells(ActiveCell.Row, 15)
dato_extra = Cells(ActiveCell.Row, 16)
Programa = Cells(ActiveCell.Row, 22)
End With
On Error Resume Next
Set WordApp = CreateObject("Word.Application")
WordApp.Documents.Open Filename:=ThisWorkbook.Path & "\_Memorandum SLC.dotx"


WordApp.ActiveDocument.Variables("Minuta").Value = Minuta
WordApp.ActiveDocument.Variables("Recepcion_SLC").Value = Recepcion_SLC
WordApp.ActiveDocument.Variables("ID_Programa_Int").Value = ID_Programa_Int
WordApp.ActiveDocument.Variables("Programa_Pres").Value = Programa_Pres
WordApp.ActiveDocument.Variables("Programa_Tipo_Apoyo").Value = Programa_Tipo_Apoyo
WordApp.ActiveDocument.Variables("Año").Value = Año
WordApp.ActiveDocument.Variables("Institucion").Value = Institucion
WordApp.ActiveDocument.Variables("NoConvenioConvocatoria").Value = NoConvenioConvocatoria
WordApp.ActiveDocument.Variables("MontoReintegroSolicitado").Value = MontoReintegroSolicitado
WordApp.ActiveDocument.Variables("MetodoPago").Value = MetodoPago
WordApp.ActiveDocument.Variables("Cve_interna").Value = Cve_interna
WordApp.ActiveDocument.Variables("Cve_externa").Value = Cve_externa
WordApp.ActiveDocument.Variables("NumeroLineaCaptura").Value = NumeroLineaCaptura
WordApp.ActiveDocument.Variables("dato_extra").Value = dato_extra
WordApp.ActiveDocument.Variables("Programa").Value = Programa


WordApp.ActiveDocument.Fields.Update


WordApp.Selection.Goto What:=0 'Let's go to the beginning of the document


'Mostrar ventana
WordApp.Visible = True


'I save the document with another name
WordApp.Documents("_Memorandum SLC.dotx").SaveAs ThisWorkbook.Path & "\Memorandum " & Replace(Minuta, " ", "_") & " Solicitud de linea de captura" & " " & Replace(Programa, " ", "_") & ".docx" 'GUARDAR WORD
'pdf = wordApp.Documents("Memorandum SLC.dotx").ExportAsFixedFormat(ThisWorkbook.Path & "" & "Memorandum" & Replace(Minuta, " ", "_") & " Solicitud de linea de captura" & " " & Replace(Programa, " ", "_") & ".pdf", 17, False, 0, 0, , , 0, False, True, 1) 'GUARDAR PDF


Set WordApp = Nothing


End Sub


Right-click the macro, and then attach the workbook to the macro and Word document template.


Greetings and a thousand thanks for your attention and support. A big hug!

 

[ExcelenteNovel]

The new document (.docx) stays exactly the same as the template (.dotx) That is, they do not change at all, the variables remain the same.


Example:

In Excel:

Institucion = Consejo de Ciencia, Tecnología e Innovación

In Word Template (.dotx): Institución = Institucion
In Word document (.docx): Institución = Institucion

Is there any way to solve this or am I better give up?

 

[Macropod]

Somehow I doubt we'll be able to resolve this issue without access to both the Word template and the Excel workbook. You could copy both to a suitable file hosting site (e.g. OneDrive) and post a link here. Delete or obfuscate any sensitive content.

 

[ExcelenteNovel]

Ready. I hope you have no problem seeing the Zip archive

MrExcel.zip - Google Drive

 

[Macropod]

It's no wonder nothing is updating! Neither your document nor the template contain any DOCVARIABLE fields - all they contain is text where those fields should appear. You should insert the fields, which you can do via Insert|Quick Parts|Field>DocVariable).

 

[ExcelenteNovel]

Taking his valuable suggestion, I followed that order:

Insert | Quick elements | Field> DocVariable

... and in effect, the template Word (.dotx) received the data of the selected record and the document in the document (.docx) With a single "but" in a currency type field in Excel ($ 81,620.00) when passed to Word, I do not respect the format and I just leave it as (81620) Is there a way to respect the format?

The above I could only do with the first code suggested, ie the one containing the variables of the Excel fields, in the OLE code, could not.
It is a pleasure to have the support of a professional like you. Receive my most sincere thanks.

 

[Macropod]

You really should spend a bit of time learning how to use Word - especially fields. First you complain the fields aren't working when, in fact, you have no fields in your document, then you complain the formatting is wrong.

In this case, you need to apply a formatting switch to the field. For a demonstration of a wide range of Word field switches, see the Mailmerge Tips and Tricks thread at:
Mailmerge Tips & Tricks
or:
Word Mailmerge Tips & Tricks | Windows Secrets Lounge
Although written for mailmerges, most of the switches described there apply equally to other fields. See also:
Insert fields in Word - Office Support

 

[ExcelenteNovel]

Excellent, I will go to the website that recommends me. I thank you very much for your great support and with this I close my doubt. Thank you