none
Hoping to streamline Excel Power Query to Word mail merge process

    Question

  • Hello All. 

    I am currently using the following process to perform a mail merge:

    • Open Excel to run a query which finds the most recent csv file in a specific folder and populates the data into columns.
    • Save the updated file in 'My Data Sources', close Excel, and open a Word mail merge document.
    • Using "Finish and Merge" to save the documents as a PDF.

    I would like to know whether it is possible to streamline this process so that it can be completed by other members of my team, ideally with one click.  I want to make it as simple for them as possible, as they are very unfamiliar with the workings of these processes.  Is it possible to do one of the following?

    1. Update the Excel Query from within Word, without having to open Excel at all?
    2. Automate Word so that it selects the most recent file in the folder as the data source? (negating the Excel step) 
    3. Use AutoHotKey, VBA or some other method to automate the current steps into 1 simple step?

    I appreciate any assistance that you can offer!

    Tuesday, April 2, 2019 8:43 PM

Answers

  • To run a mailmerge using a CSV file as a data source, there is no need to involve Excel - it can all be done from an appropriately-configured the Word mailmerge main document.

    And, if you ensure the CSV file used for the merge always has the same name, there is no need for any code to find the most recent version.

    Neither do you need to save the data file in 'My Data Sources' - it can be saved in any folder (e.g. the same folder as the Word mailmerge main document). Having configured the Word mailmerge main document appropriately, you need only open it, click on Finish & Merge>Edit Individual Documents and your output will be produced.

    If your aim is to produce separate output files for each record in the data source, see Send Mailmerge Output to Individual Files in the Mailmerge Tips and Tricks thread at http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by NDBrowder Wednesday, April 3, 2019 1:49 PM
    Tuesday, April 2, 2019 9:20 PM

All replies

  • To run a mailmerge using a CSV file as a data source, there is no need to involve Excel - it can all be done from an appropriately-configured the Word mailmerge main document.

    And, if you ensure the CSV file used for the merge always has the same name, there is no need for any code to find the most recent version.

    Neither do you need to save the data file in 'My Data Sources' - it can be saved in any folder (e.g. the same folder as the Word mailmerge main document). Having configured the Word mailmerge main document appropriately, you need only open it, click on Finish & Merge>Edit Individual Documents and your output will be produced.

    If your aim is to produce separate output files for each record in the data source, see Send Mailmerge Output to Individual Files in the Mailmerge Tips and Tricks thread at http://www.msofficeforums.com/mail-merge/21803-mailmerge-tips-tricks.html


    Cheers
    Paul Edstein
    [MS MVP - Word]

    • Marked as answer by NDBrowder Wednesday, April 3, 2019 1:49 PM
    Tuesday, April 2, 2019 9:20 PM
  • Hello Paul,

    Thanks very much for your reply, I will give your suggestions some thought!  The other complicating factor is that the csv file is actually being generated using a script in a Google Sheets document.  I am not sure if Google App Script allows you to save over an existing csv file with the same name, but that is a question for a different forum!  Thanks again for your help.

    Noah

    Wednesday, April 3, 2019 1:49 PM
  • If the Google Sheets file is suitably structured, you could use that as the mailmerge data source directly - without the need for a CSV file - thereby eliminating yet another step.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, April 3, 2019 9:31 PM
  • I ended up utilizing a Google App Script which replaces the existing CSV file with the new data, while retaining the filename.  As a bonus, it renames and moves the previous file to an archive folder for posterity.  This allows me to use the same file as a data source for the mail merge as you suggested in your original response.  I have attempted to use the Google Sheets file as a data source in the past, but it is a fairly complex document with data being entered by multiple individuals, so I find it better to convert to CSV to ensure the fidelity of the data.  Thanks again for the assistance.  In case anyone is interested in the Google App Script I used to accomplish this, here are the two separate functions (assuming that is not forbidden here)... The first function removes the existing file and saves it to an archive folder in Google Drive.  The second function saves the new data in the same source folder as the previous file.

    //REMOVE EXISTING FILE AND SAVE IT TO AN ARCHIVE FOLDER
    
    function moveFiles(source_folder, dest_folder) {
      var source_folder =  DriveApp.getFolderById('ID OF FOLDER TO REMOVE FILE FROM');
      var dest_folder = DriveApp.getFolderById('ID OF ARCHIVE FOLDER');
      var files = source_folder.getFiles();
    
      while (files.hasNext()) {
    
        var file = files.next();
        dest_folder.addFile(file);
        source_folder.removeFile(file);
    
      }
    //SAVES DATA FROM GOOGLE SHEET 'DATA' TO A CSV FILE NAMED 'DATAFILE.csv'
    
    function SAVEDATA() {
    var sss = SpreadsheetApp.openById('ID OF SPREADSHEET');
    var sheet = sss.getSheetByName('DATA');
    var data = sheet.getRange(start row, start col, num rows, num cols).getValues();
    fileName = "DATAFILE.csv";
    var csvFile = convertRangeToCsvFile_(fileName, sheet);
    DriveApp.getFolderById('ID OF SOURCE FOLDER').createFile(fileName, csvFile);
    
    Browser.msgBox('File saved to Google Drive with filename'+' "DATAFILE.csv"');
    }
    
    function convertRangeToCsvFile_(csvFileName, sheet) {
    // get available data range in the spreadsheet
    var activeRange = sheet.getDataRange();
    try {
    var data = activeRange.getValues();
    var csvFile = undefined;
    // loop through the data in the range and build a string with the csv data
    if (data.length > 1) {
    var csv = "";
    for (var row = 0; row < data.length; row++) {
    for (var col = 0; col < data[row].length; col++) {
    if (data[row][col].toString().indexOf(",") != -1) {
    data[row][col] = "\"" + data[row][col] + "\"";
    }
    }
    // join each row's columns
    // add a carriage return to end of each row, except for the last one
    if (row < data.length-1) {
    csv += data[row].join(",") + "\r\n";
    }
    else {
    csv += data[row];
    }
    }
    csvFile = csv;
    }
    return csvFile;
    }
    catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
    
    }
    
    }
    
    
    


    Tuesday, April 9, 2019 3:04 PM