Mail Merge using only Excel

[Unico]

I know this is possible, I just don't know how to do it.

I have a form made in excel and data in another excel file.

What I would like to have happen is there are 3 boxes, Course Number, Course, and Instructor, that change for each course and instructor. If we could get those three cells to read the data correctly from the data excel file and populate on the form excel file that would decrease the amount of time it takes for us to print up these evaluations.

I made "mock" files that are exactly like what I'm working at. If anyone would like these files to play around and provide some help then you can email me at jessica.west@logan.edu

Thank you.

 

[user1234567]

would you know how I could modify this code so that when new sheets are created for every row from my data, that the code reads the first column of every row and names the sheet that? i.e for the example above, each sheet will be named after the unique course number.

 

[iliace]

would you know how I could modify this code so that when new sheets are created for every row from my data, that the code reads the first column of every row and names the sheet that? i.e for the example above, each sheet will be named after the unique course number.

Most basic way to accomplish this: in the doMerge procedure, find this section of code and insert the line that's highlighted in red below:

  ' go through all row records
  For iSourceRow = 2 To rngSourceRange.Rows.Count
    ' make a new workbook based on template
    If answer = vbYes Then
      Set wkbTemp = Application.Workbooks.Add(strTemplatePath)
      Set wshTemp = wkbTemp.Worksheets(strSheetName)
    Else
      wkbTemp.Worksheets(strSheetName).Copy _
          after:=wkbTemp.Worksheets(wkbTemp.Worksheets.Count)
      Set wshTemp = wkbTemp.Worksheets(wkbTemp.Worksheets.Count)
    End If
    
    wshTemp.Name = rngSourceRange.Cells(iSourceRow, 1).Value
    
    ' populate fields
    For iFieldNum = LBound(strMergeFields) To UBound(strMergeFields)
      wshTemp.Range(strMergeFields(iFieldNum)).Value = _
          rngSourceRange.Cells(iSourceRow, iFieldNum).Value
    Next iFieldNum

However, it would be a good idea to check a few things, for example:

• whether the worksheet already exists (perhaps have a mechanism to number them)
• length is 31 characters or less (maybe truncate and account for the duplicate numbering mechanism from the previous bullet)
• make sure it does not contact illegal characters (I believe \/*[]:? but maybe a few others)

 

[user1234567]

that worked thank you! one last question; would you know how I could set it so that as opposed to creating a whole new document that it will just insert the new sheets in the same workbook that the data source lies?

 

[iliace]

that worked thank you! one last question; would you know how I could set it so that as opposed to creating a whole new document that it will just insert the new sheets in the same workbook that the data source lies?

This solution was not designed for this kind of purpose. The whole point was to preserve the original workbook as the template, and either create a new workbook for each instance, or a new worksheet within same workbook. The original workbook was not intended to be modified, if for no other reason than reuse.

That said, you can fairly easily modify the "Yes" option in the create-new-workbook prompt to achieve what you are asking.