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]

Sorry, I thought I already had. No, I was checking the format of the fields and they are of general field, text, date and number. I was able to concatenate the name of the document based on the referenced fields, but I need to be able to fill each of the template variables in Word, even though I already do the copying and pasting of the Excel columns to the Word variables, With special bonding with bond.

 

[Macropod]

I was checking the format of the fields and they are of general field, text, date and number.

But what about the two fields used for the filename??? Do they ever contain any of:
"“”*./\:?|
None of those is valid in a filename.

I need to be able to fill each of the template variables in Word, even though I already do the copying and pasting of the Excel columns to the Word variables, With special bonding with bond.

I have no idea why you think you need to "do the copying and pasting of the Excel columns to the Word variables" or what you mean by "With special bonding with bond".

 

[ExcelenteNovel]

Exactly, he had not understood. No, in fact it does not have any of these characters yet, and that, please correct me if I am wrong, for cases of filling the template in Word, I do not think they are more relevant, At least the / character that is placed in the date type fields.


And again, an apology in the wrong translation I had of the process of filling the Word template, referring me clearly to the variables. Apologies

 

[Macropod]

The document content can have those characters, but not its filename.

What do you see if you input:
msgBox strFlNm
after:
strFlNm = xlWkBk.Path & "\Memorandum " & Replace(xlWkSht.Cells(xlCell.Row, 3), " ", "_") & _
" Solicitud de linea de captura" & " " & Replace(xlWkSht.Cells(xlCell.Row, 22), " ", "_")

Also, are you running the code from a saved workbook? The save won't work otherwise.

 

[ExcelenteNovel]

Good morning: when you enter

MsgBox strFlNm
after:
StrFlNm = xlWkBk.Path & "\ Memorandum" & Replace (xlWkSht.Cells (xlCell.Row, 3), "", "_") & _
"Request for capture line" & "" & Replace (xlWkSht.Cells (xlCell.Row, 22), "", "_")

I see the path that hosts the ".docx" file created

"C: \ MrExcel \ Lineas \ Memorandum U690.422.17 Request MI capture lines"

And regarding your other question, the answer is yes, so I execute the code.

In truth, I thank you very much for your great support.

 

[Macropod]

Your code snippet appears to have different spacing from what I posted. Regardless, that output seems unlikely. With the code I posted, I'd have expected to see at least:
"C:\MrExcel\Lineas\Memorandum U690.422.17 Request for capture line "
plus whatever the content from column 22 is.

 

[ExcelenteNovel]

In fact, what appears is:

“C:\MrExcel\Lineas\Memorandum U690.422.17 Solicitud de linea de captura MI”

It happens that the translation also reached this extract from the text, but is concatenated as the line you are seeing quoted. In fact the situation of the name of the document Word and Pdf is already solved, now what remains is the filling of the template in Word from the values of the columns in Excel. Something is missing my variables, same and a loop or something, excuse me, my knowledge in VBA is still limited and I can only think of.

 

[Macropod]

OK, try changing these 3 lines:
Set xlWkBk = ThisWorkbook
Set xlWkSht = xlWkBk.Sheets("Jovenes")
Set xlCell = xlWkSht.Range("V4")
to:
Set xlWkBk = Application.ActiveWorkbook
Set xlWkSht = Application.ActiveSheet
Set xlCell = Application.ActiveCell

This should allow you to select any cell on any worksheet for the reference data, which will be gleaned from the active cell's row.

 

[ExcelenteNovel]

In effect, I did this and I was allowed to select any cell in any spreadsheet, but only to concatenate the name of the document in Word (.docx)

Memorandum E5600-93-17 Solicitud de linea de captura JT.docx

But it does not allow me to replace the reference data, obtained from the row of the active cell With the variables in my document (.dotx), they continue exactly the same, I already put them [], "", "and nothing that loads the template in Word.

 

[Macropod]

In effect, I did this and I was allowed to select any cell in any spreadsheet, but only to concatenate the name of the document in Word (.docx)
...
But it does not allow me to replace the reference data, obtained from the row of the active cell With the variables in my document (.dotx), they continue exactly the same, I already put them [], "", "and nothing that loads the template in Word.

That's because the dotx file is a template. Templates are used for the creation of new documents - which is what the code does. Your variables are added to the new document, not to the dotx file.