I need to grab a list of names from Excel and insert them into a Word document, printing one document per name. The document has some text and a bookmark called "name". The code is below.
First, I want to know if it's possible to detect how long is the list of names in the Excel spreadsheet and grab that, instead of hardcoding the number.
Second, I can't figure out how to delete the text I already put inside the document. When I insert text in a bookmark, it gets appended after the bookmark, so if I keep adding names they all stack together.
Maybe with the code this will be clearer:
Sub insertar_nombre() Dim Excel As Excel.Application Dim Planilla As Excel.Workbook Dim Hoja As Excel.Worksheet Set Excel = CreateObject("Excel.Application") Dim Filename As String Dim fname As Variant With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = False .Title = "Seleccionar Documento de Excel" .Show For Each fname In .SelectedItems Filename = fname Next End With Set Planilla = Excel.Workbooks.Open(Filename) Set Hoja = Planilla.Worksheets(1) Dim Nombre As String For Count = 2 To 10 Nombre = Hoja.Cells(Count, 1).Value ActiveDocument.Bookmarks("name").Range.Text = Nombre ActiveDocument.PrintOut Next End Sub
Forgive me if this code is obviously wrong or something, I'm just beginning with this.