• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 703
  • Last Modified:

Access 2003: Automating Mail Merge using Excel file as input - works a little... but....

Hi EE,

I found code here to automate imported an Excel file into the Word mail merge process.

http://www.jpsoftwaretech.com/vba-mail-merge-in-excel/

In the zip file below are adapted versions of their example:

excel file with data
word doc for use with merge
excel file with vba code to run the merge

When i run the vba using their excel file as input... the code runs well
When i run the vba using MY excel file as input i get  the dialogue box in the screen shot
below....

Can you pls run the code using my file xDiscoverer_MSPE.xls to see the issue and how i can resolve it....

tx so much, sandra

screen
Code is also, below, tx...

Option Explicit


Sub DoMailMerge_Difficult()

Dim msWord         As Object  ' Word.Application
Dim wordDoc        As Object  ' Word.Document
Dim wkbk           As Excel.Workbook
Dim headerRange    As Excel.Range
Dim headerValues   As Variant
Dim i              As Long

Const wdFormLetters = 0
Const wdFieldMergeField = 59
Const wdSendToNewDocument = 0
Const wdDefaultFirstRecord = 1
Const wdDefaultLastRecord = -16

Dim strExcelName As String
Dim strWordName As String


' strExcelName = "Data_MailMerge.xls"

strExcelName = "xDiscoverer_MSPE.xls"

strWordName = "Word_Mail_Merge_Difficult.doc"

  ' grab MS Word
  Set msWord = GetWordApp

  ' open mail merge document
  If Not msWord Is Nothing Then
    Set wordDoc = GetWordDoc(msWord, ActiveWorkbook.Path & _
                            "\" & _
                            strWordName)

    ' link document to data source
    wordDoc.MailMerge.MainDocumentType = wdFormLetters
    wordDoc.MailMerge.OpenDataSource Name:=ActiveWorkbook.Path & _
                                     "\" & _
                                     strExcelName, _
                                     SQLStatement:="SELECT * FROM `Sheet1$`"

    ' populate body of document with fields from data source

    ' first get field names from worksheet
    Set wkbk = Excel.Workbooks.Open(ActiveWorkbook.Path & _
                       "\" & _
                       strExcelName)
                       
    Set headerRange = Excel.Range(wkbk.Sheets("Sheet1").Range("A1"), _
                            wkbk.Sheets("Sheet1").Range("IV1").End(xlToLeft))
                           
    headerValues = Application.Transpose(headerRange.Value)
    wkbk.Close False

    ' put header values onto worksheet along with merge fields
    For i = 1 To UBound(headerValues)

      ' field name
      msWord.Selection.TypeText Text:=headerValues(i, 1) & ": "
      ' field value
      wordDoc.Fields.Add Range:=msWord.Selection.Range, _
                         Type:=wdFieldMergeField, _
                         Text:="""" & Replace(headerValues(i, 1), " ", "_") & """"
      ' line break
      msWord.Selection.TypeParagraph
    Next i

    ' perform mail merge
    With wordDoc.MailMerge
      .Destination = wdSendToNewDocument  ' wdSendToPrinter if you want to print instead
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
      .Execute Pause:=False
    End With

    ' show merged document
    msWord.Visible = True

  End If

End Sub


Function GetWordApp() As Object  ' Word.Application
  On Error Resume Next
  Set GetWordApp = CreateObject("Word.Application")
End Function

Function GetWordDoc(wordApp As Object, Filename As String) As Object  ' Word.Document
  Set GetWordDoc = wordApp.Documents.Open(Filename)
End Function
ARG-05-May-MSPE-2.zip
0
mytfein
Asked:
mytfein
  • 13
  • 2
  • 2
1 Solution
 
Jeffrey CoachmanCommented:
This seems more like an Excel question than Access...

First impressions:
The sample is very simple
3 fields, 5 record, no nulls

You sample has 7 fields and some are null (empty string, Blank, ...ect)
...and 430 records...

...In other words, the code my be very specific to the sample data

What if your data was like this in Excel
(just as a test)
...what happens?
Test this
JeffCoachman
0
 
mytfeinAuthor Commented:
Hi Jeff,

tx for writing...

If I could get the above vba to work with my data,
    then would work on making the vba work under Access off a form's command button.

Will test out your idea and get back to you ....tx, s
0
 
mytfeinAuthor Commented:
Hi Jeff,

tried your idea... still got the screen shot msg showing above.... tx, s
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Jeffrey CoachmanCommented:
Again, that code seems to be specific to the sample data Excel file,

You would have to rename a few things, I think...

Better for you to wait for an Excel Expert...

JeffCoachman
0
 
GrahamSkanCommented:
You seem to be creating a new instance of Word every time, and only making it visible at the end. Try this code instead:

Option Explicit


Sub DoMailMerge_Difficult()

    Dim msWord         As Object  ' Word.Application
    Dim wordDoc        As Object  ' Word.Document
    Dim wkbk           As Excel.Workbook
    Dim headerRange    As Excel.Range
    Dim headerValues   As Variant
    Dim i              As Long
    
    Const wdFormLetters = 0
    Const wdFieldMergeField = 59
    Const wdSendToNewDocument = 0
    Const wdDefaultFirstRecord = 1
    Const wdDefaultLastRecord = -16
    
    Dim strExcelName As String
    Dim strWordName As String
    
    
    strExcelName = "Data_MailMerge.xls"
    
    ' strExcelName = "xDiscoverer_MSPE.xls"
    
    strWordName = "Word_Mail_Merge_Difficult.doc"

  ' grab MS Word
    On Error Resume Next 'supress errors for one line only
    Set msWord = GetObject(, "Word.Application")
    On Error GoTo 0
    If msWord Is Nothing Then
        Set msWord = CreateObject("Word.Application")
    End If
    msWord.Visible = True
  ' open mail merge document
    If Not msWord Is Nothing Then
    Set wordDoc = msWord.documents.Open(ActiveWorkbook.Path & "\" & strWordName)

    ' link document to data source
    wordDoc.MailMerge.MainDocumentType = wdFormLetters
    wordDoc.MailMerge.OpenDataSource Name:=ActiveWorkbook.Path & _
                                     "\" & _
                                     strExcelName, _
                                     SQLStatement:="SELECT * FROM `Sheet1$`"

    ' populate body of document with fields from data source

    ' first get field names from worksheet
    Set wkbk = Excel.Workbooks.Open(ActiveWorkbook.Path & _
                       "\" & _
                       strExcelName)
                       
    Set headerRange = Excel.Range(wkbk.Sheets("Sheet1").Range("A1"), _
                            wkbk.Sheets("Sheet1").Range("IV1").End(xlToLeft))
                            
    headerValues = Application.Transpose(headerRange.Value)
    wkbk.Close False

    ' put header values onto worksheet along with merge fields
    For i = 1 To UBound(headerValues)

      ' field name
      msWord.Selection.TypeText Text:=headerValues(i, 1) & ": "
      ' field value
      wordDoc.Fields.Add Range:=msWord.Selection.Range, _
                         Type:=wdFieldMergeField, _
                         Text:="""" & Replace(headerValues(i, 1), " ", "_") & """"
      ' line break
      msWord.Selection.TypeParagraph
    Next i

    ' perform mail merge
    With wordDoc.MailMerge
      .Destination = wdSendToNewDocument  ' wdSendToPrinter if you want to print instead
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
      .Execute Pause:=False
    End With

    ' show merged document
    msWord.Visible = True

  End If

End Sub

Open in new window

0
 
mytfeinAuthor Commented:
Hi Graham,

tx so much for writing...

sorry for delay in responding.... i was away for a few days and am back at work today....

will study your response... will get back to you....

tx again, s
0
 
mytfeinAuthor Commented:
Hi Graham,

can you help me with the following, here, please....

the author in the web site link that pasted in post above wrote:

<Since it's a blank document, we open the source workbook and pull the header values in order to populate the document. The merge fields that correspond to each header are also added into the document. This is actually a great way to create a mail merge on any data source without knowing the field names or values beforehand; the code sets all of that up for you and puts the header values neatly into the document all on its own.>

so when i run using his test data, the merge works
when i replace his test data(Data_MailMerge.xls)
                  with
                          my excel file (xDiscoverer_MSPE.xls)
    i get a dialogue box, in the screen shot above
                   giving me the feeling that it cannot find my data,

           and i do not know why this is happening.....

   am attaching my excel file separately below....

   an attaching word doc separately below....

   tx, s
Mail-Merge-Difficult.doc
xDiscoverer-MSPE-small.xls
0
 
mytfeinAuthor Commented:
Hi Graham,

In a different post, you taught me how to open Word/test if Word is already open
so i replaced the author's open Word code with your technique instead, as i understand
your technique better....

still am having a problem using my excel file as a data source,

btw, another EE expert (Jeff C.) suggested i test with a smaller excel file, so am uploading it below
xDiscoverer-MSPE-small.xls
0
 
mytfeinAuthor Commented:
oh, below is modified code using your open Word logic:

Option Explicit


Sub DoMailMerge_Difficult()

Dim appWord         As Object  ' Word.Application
Dim wordDoc        As Object  ' Word.Document
Dim wkbk           As Excel.Workbook
Dim headerRange    As Excel.Range
Dim headerValues   As Variant
Dim i              As Long

Const wdFormLetters = 0
Const wdFieldMergeField = 59
Const wdSendToNewDocument = 0
Const wdDefaultFirstRecord = 1
Const wdDefaultLastRecord = -16

Dim strExcelName As String
Dim strWordName As String


strExcelName = ActiveWorkbook.Path & _
                       "\" & _
              "Data_MailMerge.xls"


'strExcelName = ActiveWorkbook.Path & _
                       "\" & _
              "xDiscoverer_MSPE_small.xls"

strWordName = ActiveWorkbook.Path & _
                            "\" & _
               "Word_Mail_Merge_Difficult.doc"



Dim bNewInstance As Boolean

On Error Resume Next 'supress error checking
Set appWord = GetObject(, "Word.Application")
On Error GoTo 0 'resume error checking

If appWord Is Nothing Then
    Set appWord = CreateObject("Word.Application")
    appWord.Visible = True
    bNewInstance = True
End If


Set wordDoc = appWord.Documents.Open(strWordName)


' link document to data source
wordDoc.MailMerge.MainDocumentType = wdFormLetters
wordDoc.MailMerge.OpenDataSource Name:=strExcelName, _
                                 SQLStatement:="SELECT * FROM `Sheet1$`"

' populate body of document with fields from data source

' first get field names from worksheet
Set wkbk = Excel.Workbooks.Open(strExcelName)
                   
Set headerRange = Excel.Range(wkbk.Sheets("Sheet1").Range("A1"), _
                        wkbk.Sheets("Sheet1").Range("IV1").End(xlToLeft))
                       
headerValues = Application.Transpose(headerRange.Value)
wkbk.Close False

    ' put header values onto worksheet along with merge fields
    For i = 1 To UBound(headerValues)

      ' field name
      appWord.Selection.TypeText Text:=headerValues(i, 1) & ": "
      ' field value
      wordDoc.Fields.Add Range:=appWord.Selection.Range, _
                         Type:=wdFieldMergeField, _
                         Text:="""" & Replace(headerValues(i, 1), " ", "_") & """"
      ' line break
      appWord.Selection.TypeParagraph
    Next i

    ' perform mail merge
    With wordDoc.MailMerge
      .Destination = wdSendToNewDocument  ' wdSendToPrinter if you want to print instead
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
      .Execute Pause:=False
    End With


End Sub
0
 
mytfeinAuthor Commented:
Hi Everyone,

i solved it....

     the sample code that i copied had:

                                                    SQLStatement:="SELECT * FROM [Sheet1$]"

     i needed a space between Sheet and 1 like this for my spreadsheet:

                                                  SQLStatement:="SELECT * FROM [Sheet 1$]"

so the merge worked, tx, s








full code:

Option Explicit


Public Sub DoMailMerge_Difficult()

' http://www.wordbanter.com/showthread.php?t=134922

Dim appWord         As Object  ' Word.Application
Dim wordDoc        As Object  ' Word.Document

Dim wkbk           As Excel.Workbook
Dim headerRange    As Excel.Range

Dim headerValues   As Variant
Dim i              As Long

Const wdFormLetters = 0
Const wdFieldMergeField = 59
Const wdSendToNewDocument = 0
Const wdDefaultFirstRecord = 1
Const wdDefaultLastRecord = -16

Dim strExcelName As String
Dim strWordName As String


' strExcelName = ActiveWorkbook.Path & _
                       "\" & _
              "Data_MailMerge.xls"


strExcelName = ActiveWorkbook.Path & _
                       "\" & _
              "xDiscoverer_MSPE_small.xls"

strWordName = ActiveWorkbook.Path & _
                            "\" & _
               "Word_Mail_Merge_Difficult.doc"



Dim bNewInstance As Boolean

On Error Resume Next 'supress error checking
Set appWord = GetObject(, "Word.Application")
On Error GoTo 0 'resume error checking

If appWord Is Nothing Then
    Set appWord = CreateObject("Word.Application")
    appWord.Visible = True
    bNewInstance = True
End If


Set wordDoc = appWord.Documents.Open(strWordName)


' link document to data source
wordDoc.MailMerge.MainDocumentType = wdFormLetters
wordDoc.MailMerge.OpenDataSource Name:=strExcelName, _
                                 SQLStatement:="SELECT * FROM [Sheet 1$]"



' populate body of document with fields from data source

' first get field names from worksheet
Set wkbk = Excel.Workbooks.Open(strExcelName)

Set headerRange = Excel.Range(wkbk.Sheets("Sheet 1").Range("A1"), _
                        wkbk.Sheets("Sheet 1").Range("IV1").End(xlToLeft))

headerValues = Application.Transpose(headerRange.Value)
wkbk.Close False
'
'    ' put header values onto worksheet along with merge fields
    For i = 1 To UBound(headerValues)

      ' field name
      appWord.Selection.TypeText Text:=headerValues(i, 1) & ": "
      ' field value
      wordDoc.Fields.Add Range:=appWord.Selection.Range, _
                         Type:=wdFieldMergeField, _
                         Text:="""" & Replace(headerValues(i, 1), " ", "_") & """"
      ' line break
      appWord.Selection.TypeParagraph
    Next i

    ' perform mail merge
    With wordDoc.MailMerge
      .Destination = wdSendToNewDocument  ' wdSendToPrinter if you want to print instead
      .SuppressBlankLines = True
     
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
     
      .Execute Pause:=False
    End With

Set wordDoc = Nothing

Set wkbk = Nothing
Set headerRange = Nothing

Set appWord = Nothing

End Sub

'' Close Active Word Document
'appWord.ActiveDocument.Close wdDoNotSaveChanges
'
'' Close word object instance
'appWord.Quit
0
 
GrahamSkanCommented:
Your Word document is well named. It isn't clear  why you are doing this the hard way.

Normally you can design the mail merge main document in Word. There is a mail merge entry (under Tools) to do that, and there is a step-by-step wizard there.

I have named the range (from row three downwards) in your spreadsheet as MyDatasource. With that as the recipient list, you can insert fields which will have the same names as the cells in row three (now the headings  for that range).

You will probably have to link (Select recipients) to the spreadsheet again, as the location will be different.

Once designed, if you still need to automate it, you can use much less code
  Sub DoMailMerge_Easier()

    Dim appWord         As Object  ' Word.Application
    Dim wordDoc        As Object  ' Word.Document
    Dim bNewInstance As Boolean
    
    Const wdSendToNewDocument = 0 'late binding, so Word constants need to be defined
    
    strWordName = ActiveWorkbook.Path & _
             "\MailMergeMain.doc"
    On Error Resume Next 'supress error checking
    Set appWord = GetObject(, "Word.Application")
    On Error GoTo 0 'resume error checking
    
    If appWord Is Nothing Then
        Set appWord = CreateObject("Word.Application")
        appWord.Visible = True
        bNewInstance = True 'not used here
    End If
    
    Set wordDoc = appWord.Documents.Open(strWordName)
    ' perform mail merge
    With wordDoc.MailMerge
        .Destination = wdSendToNewDocument  ' wdSendToPrinter if you want to print instead
        .SuppressBlankLines = True
        .Execute
    End With
    
    wordDoc.Close False
    appWord.ActiveDocument.SaveAs "C:\MyDocs\MergeResult.doc"
    appWord.Close False
    If bNewInstance Then 'only close application if opened especilly for this automated merge
        appWord.Quit
    End If
        
End Sub

Open in new window

MailMergeMain.docx
Copy-of-xDiscoverer-MSPE-small.xls
0
 
mytfeinAuthor Commented:
Graham,

am so sorry, i mistakenly posted a production file... pls can you delete from your pc

i sent a request to admin to see if they could delete it from this website and so to prevent

copying to other websites....

tx...s
0
 
mytfeinAuthor Commented:
Hi Graham,

i copied this code from the internet, i thought it was interesting that he was building a template dynamically

my first goal was just to get his logic to work so i could learn how to automate mail merge via vba

i really DO have a template for the excel spreadsheet to use
and planned on commenting out his dynamic code and point to my template

However, currently working with moderation to fix my urgent issue with the file(s)
will get back to you soon....

tx, s
0
 
mytfeinAuthor Commented:
Hi Graham,

Does your approach "build in" the excel file data source as part of the word template document?  If yes,  tx for the idea, however, i like setting the excel source in the vba code because want to give user an Access form for them to select the file that they wish.

Thx so much for presenting the idea, bec. that's how i notice that i posted the wrong file
and am the moderator is graciously helping me replace them with test data files.

So, technically the answer is that it was a spelling mistake on my part which brought up the select table dialogue box, it should be sheet 1 and i had it incorrectly as sheet1.

However in the spirit of your generosity of your time, and the gift of opening my eyes
to my file mistake, i am so appreciative and would like to acknowledge you....

i do have a related question to this, and do not know how to post a related question as in the old EE interface.

i just want to keep this post open until the mod has completed his work.

tx so much, s
0
 
mytfeinAuthor Commented:
the moderator replaced prod files with test files....

will close this post....
0
 
mytfeinAuthor Commented:
Hi Graham,

would like to split the points bet myself and you, so it went into a "request attention",
where i explained why want to split the points....

hopefully, they will answer soon,

tx, s
0
 
mytfeinAuthor Commented:
Hi Graham,

i posted a related question here:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27667894.html


in case you have an interest..... tx, s
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 13
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now