Solved

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

Posted on 2012-04-02
17
668 Views
Last Modified: 2012-04-09
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
Comment
Question by:mytfein
  • 13
  • 2
  • 2
17 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37797540
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
 

Author Comment

by:mytfein
ID: 37797561
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
 

Author Comment

by:mytfein
ID: 37797581
Hi Jeff,

tried your idea... still got the screen shot msg showing above.... tx, s
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37797629
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
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 37798352
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
 

Author Comment

by:mytfein
ID: 37823341
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
 

Author Comment

by:mytfein
ID: 37823612
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
 

Author Comment

by:mytfein
ID: 37823787
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:mytfein
ID: 37823832
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
 

Author Comment

by:mytfein
ID: 37824087
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 37824171
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
 

Author Comment

by:mytfein
ID: 37824261
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
 

Author Comment

by:mytfein
ID: 37824421
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
 

Author Comment

by:mytfein
ID: 37824604
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
 

Author Comment

by:mytfein
ID: 37824626
the moderator replaced prod files with test files....

will close this post....
0
 

Author Comment

by:mytfein
ID: 37824670
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
 

Author Comment

by:mytfein
ID: 37824702
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now