VBA/Excel - Mailmerge from a .csv file

[stemby]

Is there any way, using VBA in Excel, I can take the contents of a .CSV file and merge it with a word doc?

I have a .csv file called 'mailmerge.csv' saved in c:\temp.

Borrower Number	First_Name	Last_Name	Date_of_Birth	Gender	Input_Branch	School_Name	New_Member	Books_Read	Mini_Challenge
B66666666	Barry	Trotter	12/12/2018​	Boy	GR	School X	Y	6​	Y
B12121213	Walter	Wilbursmith	12/12/2018​	Boy	GR	School Y	Y	6​	Y

The number of rows in this .csv will vary, but these are the columns.

I'm looking to place a button on a form in one of my spreadsheets that will open word, then pull out all the first_names and last_names from this .CSV, and then merge them into a word doc for printing certificates.

Is there a bit of VBA code I can use (or can someone point me in the right direction?). The certificates are all 'pre-printed', so basically I just need the first_name/last_name outputting to a certain location on a page, e.g. centered in the middle (although this may vary now and again), then onto the next first_name/Last_name on the second page, and so on...

I'm not a programmer and new to VBA, so please be gentle with me

Many thanks,
Mark

 

[stemby]

I've found some code that seems to do the trick:

Sub RunMerge()
    Dim strWorkbookName As String
    strWorkbookName = "C:\Temp\Mailmerge.csv"
    Dim wdapp As New Word.Application
    Dim wddoc As Word.Document
    With wdapp
        
        'Disable alerts to prevent an SQL prompt
        .DisplayAlerts = wdAlertsNone
        
        'Open the mailmerge main document
        Set wddoc = .Documents.Open(ThisWorkbook.Path & "\Certificates-Mailmerge.docx")
        With wddoc
            With .MailMerge
                
                'Define the mailmerge type
                .MainDocumentType = wdFormLetters
                
                'Connect to the data source
                .OpenDataSource Name:="C:\Temp\Mailmerge.csv", AddToRecentFiles:=False, _
                Revert:=False, Format:=WdOpenFormat.wdOpenFormatAuto, Connection:="Data Source=" _
                & strWorkbookName & ";Mode=Read", SQLStatement:="SELECT * FROM 'Mailmerge'"
                .SuppressBlankLines = True
                With .DataSource
                    .FirstRecord = wdDefaultFirstRecord
                    .LastRecord = wdDefaultLastRecord
                End With
                
                'Define the output
                .Destination = wdSendToNewDocument
                
                'Excecute the merge
                .Execute
                
                'Disconnect from the data source
                .MainDocumentType = wdNotAMergeDocument
            End With
            
            'Close the mailmerge main document
            .Close False
        End With