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]

I also show you the body of the document in Word. Try uploading the files, but I could not:




Núm. Xxxx/[Minuta]/17


Ciudad de México, a xx de xxxx de 20xx

Addressee
Position
I presented.

By this means I allow myself to request your valuable .... [Support_Type] [Year] according to the following data:
Institution: [Institution]
Years: [year]
Amount: [Reinstatement Requested]
Internal Key: [Internal_ Key]

[TABLE="width: 391"]
<tbody>[TR]
[TD]External Key
[/TD]
[/TR]
[TR]
[TD][External_Key]
[/TD]
[/TR]
</tbody>[/TABLE]

Payment Method: [Payment Method]


Without further ado, I send a cordial greeting.

Sincerely


Who send
Uploader's post

C.c.p. Witness. - I presented

E1/T1

 

[Macropod]

The following should work:

Sub Solic_LC()

Dim xlWkBk As Workbook, xlWkSht As Worksheet, xlCell As Range
Dim wdApp As Object, wdDoc As Object, strFlNm As String
Const wdFormatXMLDocument As Long = 12: Const wdFormatPDF As Long = 17

Set xlWkBk = ThisWorkbook
Set xlWkSht = xlWkBk.Sheets("Jovenes")
Set xlCell = xlWkSht.Range("V4")

On Error Resume Next
Set wdApp = CreateObject("Word.Application")
With wdApp
  Set wdDoc = .Documents.Add(xlWkBk.Path & "\_Memorandum SLC.dotx")
  With wdDoc
    .Variables("Minuta").Value = xlWkSht.Cells(xlCell.Row, 3)
    .Variables("Recepcion_SLC").Value = xlWkSht.Cells(xlCell.Row, 4)
    .Variables("ID_Programa_Int").Value = xlWkSht.Cells(xlCell.Row, 5)
    .Variables("Programa_Pres").Value = xlWkSht.Cells(xlCell.Row, 6)
    .Variables("Programa_Tipo_Apoyo").Value = xlWkSht.Cells(xlCell.Row, 7)
    .Variables("Año").Value = xlWkSht.Cells(xlCell.Row, 8)
    .Variables("Institucion").Value = xlWkSht.Cells(xlCell.Row, 9)
    .Variables("NoConvenioConvocatoria").Value = xlWkSht.Cells(xlCell.Row, 10)
    .Variables("MontoReintegroSolicitado").Value = xlWkSht.Cells(xlCell.Row, 11)
    .Variables("MetodoPago").Value = Cells(xlCell.Row, 12)
    .Variables("Cve_interna").Value = xlWkSht.Cells(xlCell.Row, 13)
    .Variables("Cve_externa").Value = xlWkSht.Cells(xlCell.Row, 14)
    .Variables("NumeroLineaCaptura").Value = xlWkSht.Cells(xlCell.Row, 15)
    .Variables("dato_extra").Value = xlWkSht.Cells(xlCell.Row, 16)
    .Variables("Programa").Value = xlWkSht.Cells(xlCell.Row, 22)
    .Fields.Update
    strFlNm = xlWkBk.Path & "\Memorandum " & Replace(xlWkSht.Cells(xlCell.Row, 3), " ", "_") & _
      " Solicitud de linea de captura" & " " & Replace(xlWkSht.Cells(xlCell.Row, 22), " ", "_")
    
    .SaveAs strFlNm & ".docx", wdFormatXMLDocument, , False 'GUARDAR WORD
    .SaveAs strFlNm & ".pdf", wdFormatPDF, , False 'GUARDAR PDF
  End With
  'Mostrar ventana
  .Visible = True
End With

Set wdDoc = Nothing: Set wdApp = Nothing: Set xlCell = Nothing: Set xlWkSht = Nothing: Set xlWkBk = Nothing
End Sub

However, it also seems to me this could be done a whole lot more easily using OLE, since your code only ever looks at one set of cells. If you used that, your code might be no more than:

Sub Solic_LC()

Dim xlWkBk As Workbook, xlWkSht As Worksheet, xlCell As Range
Dim wdApp As Object, wdDoc As Object, strFlNm As String
Const wdFormatXMLDocument As Long = 12: Const wdFormatPDF As Long = 17

Set xlWkBk = ThisWorkbook
Set xlWkSht = xlWkBk.Sheets("Jovenes")
Set xlCell = xlWkSht.Range("V4")

On Error Resume Next
Set wdApp = CreateObject("Word.Application")
With wdApp
  Set wdDoc = .Documents.Add(xlWkBk.Path & "\_Memorandum SLC.dotx")
  With wdDoc
    .Fields.Update
    .Fields.Unlink
    strFlNm = xlWkBk.Path & "\Memorandum " & Replace(xlWkSht.Cells(xlCell.Row, 3), " ", "_") & _
      " Solicitud de linea de captura" & " " & Replace(xlWkSht.Cells(xlCell.Row, 22), " ", "_")
    
    .SaveAs strFlNm & ".docx", wdFormatXMLDocument, , False 'GUARDAR WORD
    .SaveAs strFlNm & ".pdf", wdFormatPDF, , False 'GUARDAR PDF
  End With
  'Mostrar ventana
  .Visible = True
End With

Set wdDoc = Nothing: Set wdApp = Nothing: Set xlCell = Nothing: Set xlWkSht = Nothing: Set xlWkBk = Nothing
End Sub

PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.
PPS: You can't attach files to posts on this board.

 

[ExcelenteNovel]

A thousand thanks for the clarification, apologies, because my level of English is basic. I'm going to try the code and I'll tell you what happened.


PPS. My personal laptop has Windows 10 and Office 2010. Anyway, I also have access in my work to a more current Office. Thank you.

 

[ExcelenteNovel]

In the first proposal, I get the legend:
Microsoft Visual Basic
Run-time error 13 occurred:
The types do not match

I do not know if in the template in Word you have to do something extra or what kind of signs to use to enclose the variables in the document in Word. For example [Minutes], "Minutes" or "Minutes"
And in the second where OLE is used, it stays in the document in Word, but without replacing any variables, so I assumed the use of signs in Word.

Taking advantage: in my example use the "Young" sheet but in reality they are about 5 sheets, with the possibility of expanding to more, and the question would be: is it possible to make the macro on any of these sheets just by positioning myself in the row containing the ID do I need to work?

I really appreciate your great support.

 

[Macropod]

You shouldn't get any errors with the first code block unless, perhaps, the workbook you're running it from doesn't have a 'Jovenes' worksheet. On what line are you getting the error? To work with different worksheets, assuming the Active sheet is the one to process, you'd have to change:
Set xlWkSht = xlWkBk.Sheets("Jovenes")
to:
Set xlWkSht = xlWkBk.ActiveSheet

To use OLE, you don't need any of the document variables. Instead, you use copy/paste to copy the source cells into Word, using Paste Special with the 'paste link' option and your preferred paste format. Once you've done that and re-saved the template, the second code block work just fine. That approach won't work if you're going to change the reference worksheets, though, without some re-coding.

 

[ExcelenteNovel]

It is in this line that marks error

     .SaveAs strFlNm & ".docx", wdFormatXMLDocument,, False

'SAVE WORD

And now do what I recommend in the Word template, locate me in each of the Excel headers, I copied and pasted to each of the "fields" Word linking in the special paste and I still fail.


Thank you.

 

[Macropod]

have you checked that you have no illegal filename characters in:
xlWkSht.Cells(xlCell.Row, 3)
and:
xlWkSht.Cells(xlCell.Row, 22)
both of which contribute to the filename is:
strFlNm = xlWkBk.Path & "\Memorandum " & Replace(xlWkSht.Cells(xlCell.Row, 3), " ", "_") & _
" Solicitud de linea de captura" & " " & Replace(xlWkSht.Cells(xlCell.Row, 22), " ", "_")

 

[ExcelenteNovel]

I comment that in this line:

.SaveAs strFlNm & ".docx", wdFormatXMLDocument, , False 'GUARDAR WORD
.SaveAs strFlNm & ".pdf", wdFormatPDF, , False 'GUARDAR PDF

... there was an extra comma, but I still fail to transfer Excel data to the template in Word, even though I already do what you recommended to copy and paste special paste "Links"; In addition to the fact that I do not know how to do, in ignoring sentences in macros, so that when I position myself in a row or cell of the reference field ID, I copy the fields of that row in the document in Word. I guess the following line limits me to just one row:

Set xlCell = xlWkSht.Range("V4")

This I knew, because in renaming the document ".docx" concatenating the text and fields I require, field 3 (“Minuta”) and field 22 (“Programa”), if I do it (well only those in row V4)

 

[Macropod]

I comment that in this line:

.SaveAs strFlNm & ".docx", wdFormatXMLDocument, , False 'GUARDAR WORD
.SaveAs strFlNm & ".pdf", wdFormatPDF, , False 'GUARDAR PDF

... there was an extra comma

There is not a 'extra' comma.

I guess the following line limits me to just one row:

Set xlCell = xlWkSht.Range("V4")

Yes, but it was done that way because that's all your own code provided for.

You have still not answered the questions I asked...