Free Microsoft Excel 2013 Quick Reference

Mail merge data source problems in Word

Hi,

I'm hoping someone can help me because I'm completely stuck!
I have been using Office at work (which I think is 2003) and trying to mail merge in Word using an Excel data source.

I have 3 different formats of letters in Word. I have 4 sheets in the workbook - one for each of the different types of letters, and 1 that I use as a lookup table for the others.

2 of the 3 letters have merged beautifully, but when it comes to the final merge document:

I get the "Browse" part to find my data source
I locate the file and select it
I get varying results in the list of sheets in the workbook - I get "NSW - FC Proposals", "NSW - FC Proposals_", "NSW - FC Proposals - Print Area", and most disturbingly "NSW - FC Proposals-FilterDatabase". These results seem to vary each time I try the merge, without me having made any changes to the Excel file.

No matter which option I select, the next dialog box lists NO tables and I cannot go any further. It tells me that it was unable to open the data source.

The spreadsheet that I'm having all this trouble with is the only one that uses the lookup table (done using =INDEX and matched to 2 criteria) and so is quite formula-heavy. I have also used every column available in the spreadsheet, so I don't know if that is a problem.

I don't understand why the other sheets in the workbook merge perfectly and this one refuses to work - any suggestions?

Thank you!


Post your answer or comment

comments powered by Disqus
Hi,

I am running into a problem specifying a mail merge range in Excel 2002/2003 that only includes non-blank cells. In Excel/Word 2000 I was able to achieve this by hiding unused rows in Excel before performing the mail merge. This also added simplicity to the Excel “form” by showing the user only rows needing to be filled out at the time. In Excel/Word 2002 and beyond, this no longer seems to work, however, one peculiarity seems to make me thing perhaps Excel 2002 and beyond is still capable of limiting a mail merge data source range in this way.

Any ideas of how I might either modify the Excel or Word documents or the VBA code so that only shown/filled rows are included in the mail merge, thus eliminating unnecessary blank pages?

The actual mail merge is being performed using Microsoft Word VBA code (as I ran into far too many problems in executing the mail merge in Excel) while the Excel VBA code is used for showing/hiding cells, clearing the form, determining the number of printed pages in the final step, and saving and continuing the next step in the mail merge process.

Full details to follow and I’d be more than happy to upload screen captures if that would be of help. If you believe this to be a Word VBA solution I will try and find a Word coding forum in which to ask this question.

Thanks much!

-------------------------------------------------------------------------------------

A year ago I came up with an automation “mini-app” that staff can use to generate the correct number of student aide tags needed for the student aides helping them in their classes. This automation uses a batch script, Excel VBA and Word VBA to complete a mail merge in Microsoft Word.

The batch file copies the Excel and Word documents to the user’s Desktop, launches Excel first, inviting staff to fill out the required columns “TEACHER’S LAST NAME,” “STUDENT’S FULL NAME,” “PERIOD # (INCLUDING SUFFIX),” and “TYPE OF TAG.” There is a textbox in column F that allows users to show or hide cells (eight cells are shown by default), a button to clear the form, a button to find out how many printed pages they should expect (based on a mathematical formula that divides the number of shown cells by eight and another formula that divides the number of filled cells by eight) and a button that saves and closes the Excel document.

Upon clicking the “Save, Close and Continue” button in the Excel spreadsheet, the batch script launches the Microsoft Word document on the Desktop where Word VBA code automatically completes the mail merge with the Excel document on the user’s Desktop and initiates the printing of the completed merged document to the default printer, closes the Word mail merge template document and (upon closure of the Word application) deletes the temporary documents off the user’s Desktop.

The version of Microsoft Word used in our building prior to the upcoming school year was Microsoft Office 2000 (the OS is Windows XP). However, we recently upgraded all of our licenses to Microsoft Office 2003 and I soon realized that I also needed to make a few adjustments to my “mini-app” in order for it to work with the upgraded version of Office.

Everything is now working as it should except for one interesting little bug. In column F of the Excel spreadsheet I added a textbox with some VBA code to hide/show rows in the spreadsheet. By default, only nine rows (eight plus the header row in row 1) show as there are eight student aide tags to a printed page once the mail merge has been completed in Word. The instructors can use the textbox to hide or show additional rows as they need to. This works to simplify the user’s view by allowing them to only show the number of rows needed but also, in Excel/Word 2000, creates a situation where (by default) Word only performs a mail merge against the unhidden cells. This limits the number of pages printed and eliminates the unnecessary printing of blank pages following the completed mail merge page(s).

The rub is this. In Excel 2000 hiding unused rows eliminates the blank pages from being generated in Word by limiting the data source range. However, in Excel 2002 and beyond, hiding rows does not limit the data source range in Word and a number of blank pages (based upon the number of rows unhidden in the first place) are generated following the final mail merge. It doesn’t seem to make a difference if I manually rehide the rows or rehide these using the VBA code associated with the textbox.

For example, the Excel document starts with only nine rows unhidden, and if I precede without unhiding any additional rows only one printed page is generated in Microsoft Word. However, if I unhide say the first 50 rows and then, prior to saving and continuing, decide to rehide all but the first nine rows, six additional blank pages are still generated in Microsoft Word upon the completion of the mail merge. Blank pages occur when unhiding more rows than the user fills out.

What I find interesting about this is that there has to be something saved in the Excel 2003 document upon hiding/unhiding rows. I can temporarily rectify the additional blank pages problem by copying the original, untouched, Excel document and reapplying the mail merge. So I’d deduce that there must be something saved in the original, untouched, Excel 2003 document that allowed Word to limit the data source range to only the non-hidden cells; There must be something that changes in the Excel document when I unhide/rehide cells? However, I have not yet been able to duplicate the results.

Any ideas of how I might either modify the Excel or Word documents or the VBA code so that only shown/filled rows are included in the mail merge, thus eliminating unnecessary blank pages?

Thanks much!

-Matt-

----------------------------------------------

Here is the VBA code from the Microsoft Excel file:

Excel - Sheet 1 (Generate Aide Tags App):

Option Explicit
Private varNumCellsToShow
Public varReturnDetected

Private Sub btnHowManyPrintedPgs_Click()
Call HowManyPrintedPgs
End Sub

Private Sub btnNumStudsEnter_Click()
On Error Resume Next
Application.ScreenUpdating = False
varNumCellsToShow = txtNumCellsToShow.Text + 2
If txtNumCellsToShow.Text = 65535 Then
MsgBox ("You entered a number greater than or equal to 65535. Showing all 65535 rows.")
txtNumCellsToShow.Text = 65535
varNumCellsToShow = 65536
Range("B1").Select
End If
If varNumCellsToShow = "" Then
Exit Sub
End If
Cells.Select
Selection.EntireRow.Hidden = False
Rows(varNumCellsToShow & ":65536").Select
Selection.EntireRow.Hidden = True
varNumCellsToShow = ""
End Sub

Private Sub btnSaveAndClose_Click()
ActiveWorkbook.Save
Excel.Application.Quit
End Sub

Private Sub bttnClear_Click()
Application.ScreenUpdating = False
ActiveWindow.SmallScroll Down:=-33
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("B2").Select
End Sub

Private Sub txtNumCellsToShow_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = 13 Then
varReturnDetected = 1
Call btnNumStudsEnter_Click
End If
End Sub

Private Sub txtNumCellsToShow_LostFocus()
If varReturnDetected 1 Then
Call btnNumStudsEnter_Click
End If
varReturnDetected = 0
End Sub

Excel - Module 1:

Option Explicit
Private mycount As Long

Sub HowManyPrintedPgs()
Dim FinalRow
Dim LastFilledRow
Dim NumberofPages As Integer
Call NumberUnhiddenBlank
If mycount = 0 Then
FinalRow = (Range("B65536").End(xlUp).Row)
If FinalRow 7 Then
NumberofPages = WorksheetFunction.RoundUp((FinalRow - 1) / 8, 0)
MsgBox ("There are 8 student aide tags to a page and you have elected to print " & FinalRow - 1 & " tags. Tags will print to " & NumberofPages & " pages.")
End If
End If
If mycount > 0 Then
FinalRow = (Range("B65536").End(xlUp).Row) + mycount
LastFilledRow = (Range("B65536").End(xlUp).Row)
If FinalRow 2 Then
NumberofPages = WorksheetFunction.RoundUp((FinalRow - 1) / 8, 0)
MsgBox ("There are 8 tags to a page, you have elected to print " & LastFilledRow - 1 & " tags. IMPORTANT NOTE: You have " & mycount & " blank rows at the bottom of your form. To reduce the number of pages printed, please be sure to adjust your Number of Tags to Create textbox settings. Tags will print to " & NumberofPages & " pages.")
End If
End If
End Sub

Sub NumberUnhiddenBlank()
Dim c As Range
mycount = 0
For Each c In Range("B1:B65536")
If Rows(c.Row).Hidden = False And c.Value = "" Then mycount = mycount + 1
Next c
'MsgBox ("The number of unhidden, blank, rows is " & mycount & ".")
End Sub

Here is the VBA code from the Microsoft Word file:

Option Explicit

Private Sub ActivateMailMerge_Click()
Call Document_Open
End Sub

Private Sub Document_Open()
Dim UserName
UserName = Environ("username")
MsgBox ("This step will take a couple of seconds. Please hang on...")
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:Documents and Settings" & UserName & "DesktopStudent Aides.xls", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="Entire Spreadsheet", SQLStatement _
:="", SQLStatement1:=""
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With

Dim NumPgs As Long

NumPgs = Selection.Information(wdNumberOfPagesInDocument)

If NumPgs < 2 Then
Select Case MsgBox("Please insert " & NumPgs & " page of purple colored paper into the printer and press OK to print or press Cancel to cancel", vbOKCancel, "Printing to the Default Printer...")
Case vbOK
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0
Case vbCancel
Documents(2).Close SaveChanges:=False
Exit Sub
End Select
End If

If NumPgs >= 2 Then
Select Case MsgBox("Please insert " & NumPgs & " pages of purple colored paper into the printer and press OK to print or press Cancel to cancel", vbOKCancel, "Printing to the Default Printer...")
Case vbOK
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0
Case vbCancel
Documents(2).Close SaveChanges:=False
Exit Sub
End Select
End If

End Sub

Hello All, just wondering if someone could help me with my problem.

I get regular emails in a list format like:

Blah blah blah text text text text and some more text
blah text text text text and some more text
blah text text text text and some more text

NAME: Bob

COMPANY: Durex

LOCATION: South West London

REF No: 01/200111 - (Please quote)

DATES: 19/07/2029 to 04/08/2030

Is there any way that I can mail merge the fields into a word document?

Hi all,

due to a change of our directory structure I have to update the link to the Access data source for about 400 mail merges.
In our database we use parameter queries. All main documents work with such a parameter query (all to the same query). Therefore, we must connect to our database using DDE and not ODBC. How can I define this DDE connection in VBA? I have tried it via wdMergeSubType:

	VB:
	
[CENTER][LEFT]Sub ChangeDataSource() 
 
ActiveDocument.MailMerge.OpenDataSource Name:= _ 
"C:Finale Datenstrukturneue Dateien   LaptopDiagnostikSerienbriefaktualisierung Testdatenquelle2.mdb", _ 
ConfirmConversions:=True, ReadOnly:=True, LinkToSource:=True, AddToRecentFiles:=False, _ 
Connection:="QUERY qry_SEQ_Befund", SQLStatement:=   "SELECT * FROM [qry_SEQ_Befund]", _ 
SubType:=wdMergeSubTypeOther 
End Sub 
[/LEFT] 
[/CENTER] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Here the correct query seems to be selected (at least I don’t get an error message) and the modification of the linked data source seems to work well as the correct (changed) data source is returned upon the following macro:

	VB:
	
 ReturnDataSource() 
     
    If ActiveDocument.MailMerge.DataSource.Name  "" Then _ 
    MsgBox ActiveDocument.MailMerge.DataSource.Name 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, when executing the macro I get a dialog which asks for the choice of a table, which is not what I intended. If I select the table on which my query is based, everything works fine so far. I only just don't completely understand why this choice is necessary at all. The problem which arises from it, is that the system doesn't ask for my parameter any more if I close the document and reopen it. I get the message that the table which was selected before is being opened, but I need the query to be linked to my main document otherwise there is no way to enter my parameter in order to select a certain data set.

Does anybody have a clue? I would be grateful for any ideas as I just don't get any further here.

Many thanks in advance!!!

Hi all...

I am attempting to complete a mail merge with currency data in Excel. We are mailing letters out to individuals stating they have checks that have not yet been cashed, even though they are way past the "stale" date...and then we can get them off of our bank reconciliation.

The problem is this: when providing information as to the check number, check date, and check amount, the formatting transfers from Excel into Word differently than expected. I've tried a few different things, including ensuring the values are in the same font, and that the dollar amount field is set up as follows: Accounting, 2 decimal points, and $ currency sign displaying. This wasn't working, so I reviewed the help document in Word, and it says to do the following:
---
Before you connect to the worksheet, do the following in Word:
Click the Microsoft Office Button , and then click Word Options.
Click Advanced.
Scroll to the General section, and select the Confirm file format conversion on open check box.
Click OK.
With the mail merge main document open, in the Start Mail Merge group of the Mailings tab, click Select Recipients, and then click Use Existing List.
Locate the Excel worksheet in the Select Data Source dialog box, and double-click it.
In the Confirm Data Source dialog box, click MS Excel Worksheets via DDE (*.xls), and then click OK.
Note If you don't see MS Excel Worksheets via DDE (*.xls), select the Show all check box.
In the Microsoft Office Excel dialog box, for Named or cell range, select the cell range or worksheet that contains the information that you want to merge, and then click OK.
Note To prevent being prompted every time you open a data file, you can turn off the Confirm conversion at Open option after you have connected to the worksheet.
---
Of course, I saw this after I'd linked the documents so I don't know if that's the problem - if it is the problem, how can I break the link? I know how to break links in Excel but I don't see an option to do that in Word. Also, I notice in the MS Excel Worksheets via DDE (*.xls) the file extension is not the same as the 2007 (*.xlsx) format and there is no selection for that. I also don't know if that is causing issues.

In any event, I'm getting the two following error messages.

In Excel 2007:

The document path is listed, and then "is currently in use. Try again later."

In Word 2007

"Word could not establish a connection to Microsoft Excel to complete the current task."

So, my formatting isn't preserved and I'd really rather not have to peruse 304 documents for appropriate decimal / currency formatting!

If anyone could help I would be extremely appreciative.

Thanks, JBG

mail merge data

When my computer was migrated from Office 2003 to 2007 all of my Word documents which use the mail merge capability ceased to work correctly.  Specifically, the initial SQL that does record select&sort on opening the database no longer does so, and instead I get all of the records in whatever order they happen to appear in the Excel file used as the database.

I have discovered that I can check on the sel/sort criteria once the database file is opened, but sure enough there are NO select and sort criteria.  When I put them in manually and click "OK" to use them they get automatically modified for some reason into some other criteria, and one of those "something else" select criteria results in all records being selected.

At the same time as the Office 2007 migration the computer configuration also changed so that the "D:" partition of "C:" (which had been used for all non-OS data) was eliminated, so all data is now on the "C:" drive.  The WORD docs trying to mailmerge all still look to "D:" until I specifically change each one by hand when the Open fails, but Save of the Word doc doesn't seem to save the new location of the Excel database.  I do not think that this is related to the problem of record select&sort, but I would like an answer to what I can do about having to re-point to "C:"every time a merge document tries to open.

Hi everyone -

I want to set up a one step button in an Excel workbook to open up a Word Mailmerge Document that I have already set up complete with Excel data source.

I have used this code so far but it's not working. What else do I have to do?

	VB:
	
 OpenWordMailmergeMasterB() 
     
    Set appWD = CreateObject("Word.Application") 
    appWD.Visible = True 
     
    appWD.Documents.Open Filename:="X:Detention filesMail MergeLesley's MailmergeMASTER Interim Report.doc" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
'To Run the Mail Merge
'Data source is a range name in Excel workbook called "MailmergeReport":


	VB:
	
appWD.MailMerge.OpenDataSource Name:="'X:Detention filesAdministration Lesley.xlsMailmergeReport" _ 
, ConfirmConversions:=False, _ 
ReadOnly:=False, LinkToSource:=False, AddToRecentFiles:=False, _ 
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _ 
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _ 
Connection:="", SQLStatement:="", SQLStatement1:="" 
 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hey guys, I'll be the first to admit that I'm no expert on Excel/Coding in any language, however I keep running into a strange issue that has just frustrated myself to no end. I'm performing a mail merge on a sheet in an excel workbook into word and occasionally I'll get some strange output. It's not an error, however a word dialogue box will pop up trying to find a datasource that it shouldn't be looking for.

For example, if the path of the datasource is C:TestDatasource.xlsm(the macro is being ran from Datasource.xlsm), Word appears to be looking for C:Test.xls instead. I've tried defining a string with ThisWorkbook.FullName, global variables, static locations, and nothing I can do seems to fix this. The worst part is that if I retype the variable/location of the datasource EXACTLY as it is in the code already, it works the 2nd time around. Listed below is the current code, I tried going to a ThisWorkbook.Fullname to fix this error. I'm sorry if any of this sounds confusing, but hopefully the code will help you guys pin down any issues im having. Thanks!


	VB:
	
 RunMerge() 
    Dim wd As Object 
    Dim wdocSource As Object 
    Dim iMsgBox As Integer 
    Dim strDocumentName 
     'Select Word mail merge template from user
    strDocumentName = ThisWorkbook.Path & "RetinaReportTemplate.dotm" 
    On Error Resume Next 
    Set wd = GetObject(, "Word.Application") 
    If wd Is Nothing Then 
        Set wd = CreateObject("Word.Application") 
    End If 
    wd.Visible = False 
    wd.DisplayAlerts = wdAlertsNone 
    On Error Goto 0 
    Set wdocSource = wd.Documents.Open(strDocumentName) 
     'Imports the template with macros into word
    wd.Run "ImportAddin" 
     'performs mailmerge
    wdocSource.MailMerge.MainDocumentType = wdFormLetters 
    wdocSource.MailMerge.OpenDataSource _ 
    Name:=ThisWorkbook.FullName, _ 
    AddToRecentFiles:=False, _ 
    Revert:=False, _ 
    Format:=wdOpenFormatAuto, _ 
    Connection:="Data Source=" & ThisWorkbook.FullName & ";Mode=Read", _ 
    SQLStatement:="SELECT * FROM `tmp_Export$`" 
    With wdocSource.MailMerge 
        .Destination = wdSendToNewDocument 
        .SuppressBlankLines = True 
        With .DataSource 
            .FirstRecord = wdDefaultFirstRecord 
            .LastRecord = wdDefaultLastRecord 
        End With 
        .Execute Pause:=False 
    End With 
     'Close the template without saving
    wdocSource.Close SaveChanges:=False 
     'Run the formatting macro with the OS type
    wd.Run "RunAfterMerge", OS 
     'Add front matter if the user wants to
     'iMsgBox = MsgBox("Would you like to add Front Matter to the document?", vbYesNo + vbSystemModal, "Select Front Matter")
     'If iMsgBox = vbYes Then
    strDocumentName = ThisWorkbook.Path & "FrontMatterTemplate.docx" 
    With wd.Documents(1).ActiveWindow 
        .Selection.WholeStory 
        .Selection.Copy 
    End With 
     'save a copy without front matter
    wd.Documents(1).SaveAs Filename:=ThisWorkbook.Path & "" & ProjID & "RawRetinaReport.doc" 
    wd.Documents(1).Close 
     'Open the front matter
    wd.Documents.Open (strDocumentName) 
    With wd.Documents(1).ActiveWindow 
         'Paste the previous report information into the front matter
        .Selection.EndKey Unit:=wdStory 
        .Selection.InsertBreak Type:=wdPageBreak 
        .Selection.MoveRight 
        .Selection.Paste 
        .Selection.EscapeKey 
        .Activate 
        .Selection.HomeKey Unit:=wdStory 
        .Selection.Fields.Update 
    End With 
     
     'Run the RestartNumbering Macro
    wd.Run "RestartNumbering", OS 
     'Update the Table of Contents
    wd.Documents(1).TablesOfContents(1).Update 
     
     'save the copy with front matter
    wd.Documents(1).SaveAs Filename:=ThisWorkbook.Path & "" & ProjID & "RetinaReport.doc" 
    wd.Documents(1).Close 
     'Else
     'wd.Documents(1).Close
     'End If
     'Close word
    wd.DisplayAlerts = wdAlertsAll 
    wd.Quit 
     'Unload the word resources
    Set wdocSource = Nothing 
    Set wd = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


(Office 2003) Please can someone help me with a mail merge to word from two
seperate excel sources (microsoft help page WD97: How to Use MS Query to
Merge Two Excel Files for a Mail Merge Data Source offers the advice for
Word97 ) but this doesn't seem to translate to 2003! Sorry if this question
has been answered previously but I had a good look around and can find no
help. Thank You

I'm trying to do a mail merge to a label document. I have 10 separate names
to put in 10 separate labels. I insert the merge field. When it goes to merge
& print the doc, it only merges the 1st name under the title it isn't
grabbing all the names to put in each of the labels.

Thank you in advance for your help!

Harriet

I am mail merging data from Excel onto Word. When the information shows up on Word I have no commas. For example, the cell in Excel reads 35,500 and shows up in Word as 35500. Does anyone know what I need to do?

I'm trying to do a mail merge to a label document. I have 10 separate names
to put in 10 separate labels. I insert the merge field. When it goes to merge
& print the doc, it only merges the 1st name under the title it isn't
grabbing all the names to put in each of the labels.

Thank you in advance for your help!

Harriet

Anyone knows how to do mail merge from MS Excel to word. I want to mail merge to the table in MS Word...how to do it? I have attached a sample file.

Hi I am importing XML file in my worksheet as a XML Map.But when I map any XML file,my Refresh XML Daata option in XML Map gets disabled.
This is happening since my "Save Data source definition in Workbook" option in XML Map properties is non editable.Please tell me how to turn on this option.

I am attaching Screenshot

Hello,

I have some code that runs a mail merge between excel and microsoft word. However, it is only successful about 50% of the time. During the other 50% I get a run-time error that says "Remote Server Machine Does Not Exist or Not Available":


	VB:
	
 FillLabels_Click() 
    Dim BlankRow As Long 
    Dim lbs As Worksheet 
    Set lbs = Worksheets("Labels") 
     
    Dim strThisWorkbook As String 
    strThisWorkbook = ThisWorkbook.FullName 
     ' create the word document:
     
    Dim oWORD As Word.Application, wrdDoc As Word.Document, wrdTable As Word.Table 
    Set oWORD = New Word.Application 
    Set wrdDoc = oWORD.Documents.Add 
     ' so we can see what is happening in word:
     
    oWORD.Visible = True 
    wrdDoc.Activate 
     ' adjusting the page setup properties first:
     
    With wrdDoc.PageSetup 
        .Orientation = wdOrientPortrait 
        .TopMargin = CentimetersToPoints(1.3) 
        .BottomMargin = CentimetersToPoints(1.3) 
        .LeftMargin = CentimetersToPoints(0.5) 
        .RightMargin = CentimetersToPoints(0.5) 
        .Gutter = CentimetersToPoints(0#) 
        .HeaderDistance = CentimetersToPoints(1.25) 
        .FooterDistance = CentimetersToPoints(1.25) 
        .PageWidth = CentimetersToPoints(21) 
        .PageHeight = CentimetersToPoints(29.7) 
    End With 
     
     ' creating the table of labels (in this example, 3 columns, 10 rows):
     
    Set wrdRange = wrdDoc.Range 
    Set wrdTable = wrdDoc.Tables.Add(Range:=wrdRange, NumRows:=10, NumColumns:=3, _ 
    DefaultTableBehavior:=wdWord9TableBehavior, _ 
    AutoFitBehavior:=wdAutoFitFixed) 
     ' adjusting the table properties:
     
    With wrdTable 
        .Columns.PreferredWidth = CentimetersToPoints(9.9) 
        .TopPadding = CentimetersToPoints(0) 
        .BottomPadding = CentimetersToPoints(0) 
        .LeftPadding = CentimetersToPoints(0.3) 
        .RightPadding = CentimetersToPoints(0.3) 
        .Rows.HeightRule = wdRowHeightExactly 
        .Rows.Height = CentimetersToPoints(2.48) 
        .Rows.Alignment = wdAlignRowCenter 
        .Spacing = 0 
        .AllowPageBreaks = True 
        .AllowAutoFit = True 
        .AutoFitBehavior (wdAutoFitFixed) 
        .AutoFitBehavior (wdAutoFitFixed) 
        .Borders(wdBorderLeft).LineStyle = wdLineStyleNone 
        .Borders(wdBorderRight).LineStyle = wdLineStyleNone 
        .Borders(wdBorderTop).LineStyle = wdLineStyleNone 
        .Borders(wdBorderBottom).LineStyle = wdLineStyleNone 
        .Borders(wdBorderHorizontal).LineStyle = wdLineStyleNone 
        .Borders(wdBorderVertical).LineStyle = wdLineStyleNone 
        .Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone 
        .Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone 
        .Borders.Shadow = False 
    End With 
     ' getting the range of data from out excel data tabel:
     
    Range(Rng("Labels")).Select 
     ' finally, propagate the data into your labels with a mail merge:
     
    With oWORD.MailingLabel.Application.ActiveDocument 
        .mailmerge.MainDocumentType = wdMailingLabels 
         ' choosing the right document and data table:
         
        .mailmerge.OpenDataSource ThisWorkbook.FullName, ConfirmConversions = "False", ReadOnly = "False", _ 
        LinkToSource = "True", AddToRecentFiles = "False", , , , , , , _ 
        "Data Source=" & strThisWorkbook & ";Mode=Read", _ 
        "SELECT * FROM `Labels$`" 
        .mailmerge.DataSource.ActiveRecord = wdFirstRecord 
        .mailmerge.DataSource.ActiveRecord = wdFirstRecord 
         ' iterate through each label:
         
         ' for each row (in excel):
         
        For r = 1 To .mailmerge.DataSource.RecordCount 
             ' for each field (in excel):
             
            For f = .mailmerge.DataSource.DataFields.Count To 1 Step -1 
                .Application.Selection = .mailmerge.DataSource.DataFields.ITEM(f).Value & vbCrLf 
            Next f 
             ' go to the next row (in excel):
             
            .mailmerge.DataSource.ActiveRecord = (r + 1) 
             ' go to the next label (in word):
            .Application.Selection.MoveRight Unit:=wdCell 
        Next r 
         ' to be sure your data is visible:
         
        .mailmerge.ViewMailMergeFieldCodes = wdToggle 
    End With 
     ' no closures for your word object needed as you close your word app later on yourself.
    lbs.Range("A2:P90").Value = "" 
    Unload Me 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


The error appears at the first With Statement:

	VB:
	
 wrdDoc.PageSetup 
    .Orientation = wdOrientPortrait 
    .TopMargin = CentimetersToPoints(1.3) 
    .BottomMargin = CentimetersToPoints(1.3) 
    .LeftMargin = CentimetersToPoints(0.5) 
    .RightMargin = CentimetersToPoints(0.5) 
    .Gutter = CentimetersToPoints(0#) 
    .HeaderDistance = CentimetersToPoints(1.25) 
    .FooterDistance = CentimetersToPoints(1.25) 
    .PageWidth = CentimetersToPoints(21) 
    .PageHeight = CentimetersToPoints(29.7) 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Could someone help me find the problem?

Thank You For Your Help!

Hi,

I'm trying to create a macro that runs from an excel data source, opens Microsoft Word and merges specified fields into a letter (the fields/data are in the excel document).

What is my best approach for attempting this?

Thanks

When using an Excel spreadsheet as the data source for mailmerge in
Office 2003 the merged numbers in the Word document are often displayed
with many decimal places. eg. if the number in excel is 2.8 it merges as
2.79999999 in Word.

I've tried rounding in Excel but it makes no difference.

Using the same spreadsheet with Office 2000 it works fine.

How do I get Excel / Office 2003 to integrate and present the correct
numbers in Word merges?

Bernie

Excel 2003
Word 2003

Am doing a mail merge from excel into a word document. All columns are visible to word except for the very first column. I have tried different documents with same result. It appears something in the file is saying ignore col 1. When I bring it up in excel - no problems. I even renamed the My Documents/My Data Sources directory thinking that something was being kept in a setup file somewhere, but it made no difference. Have even tried to unhide the col in excel, even though it wasn't hidden.

This is driving me crazy ! (OK, crazier !)

Anyone have any suggestions ?
Bob

Does someone know how you can do a mail merge with data from excel to a word document in which the word document contains a chart that is unique to each record? Once the mail merge is completed I should have a one page doc for each record that contains a chart that is unique to that particular records data.

Many thanks!

I have created mail merge in word file, and data source is in excel.
My problem is whenever I open word file, every time it ask for find data
source file. Then I will have to give data source path(i.e., excel file)
I don’t want to find data source every time…is there any permanent solution
for this.
vishu

I have a spreadsheet with all kinds of data and I want to use mail merge to write letters in Word. I am having a problem with one of the columns not merging correctly. In that column I have client numbers. Most clients only have one number and in that case the merge works fine. Some clients, though, have two or more numbers and so I have the numbers separated by a space. When I try to do the mail merge, the ones with multiple numbers display a "0". Here is an example of what I am talking about:

In an excel column:
Client Number
1234
2345
3456
8765 7654
4321 5432
2468 3579 7531

When I merge it this is what I get:
1234
2345
3456
0
0
0

All I want is for the numbers to be displayed at the bottom of the letter the way I have them typed in excel.

I have tried changing the format of the column to text and that doesn't work. I've tried separating the numbers by a "-", "/", "*" and that doesn't work either.

One last funny thing - If I delete all the rows that have single numbers in them and leave only the ones with multiple numbers, it works fine. It's almost like the mail merge feature doesn't like single numbers and multiple numbers in the same sheet.

Any help would be greatly appreciated!

hello,

I am mail merging data from excel into a 1 page word doc and I want the data to be placed in an exact places on the page.

For example at the top of the page to left is a name field and to the far right is a date of birth field. After the merge the DOB is either on the next line or too close to the left. Each client's name is a different number of letters so it is difficult where to judge where to put the DOB field. As a result I have to reformat the merged doc which is taking too long.

Thanks, any suggestions are greatly appreciated.

I am trying to write a macro that will take data from a worksheet, open an embedded MS Word mail merge document (OLEObject in a worksheet), and perform the merge. I have code that will do all this fine, however, I want the screen to not show anything it's doing until the macro is complete.

I know how to set Excel ScreenUpdating to False, and I know how to create the MS Word application object and set its Visible property to false. The problem is using an embedded OLEObject on a worksheet that is a word document. The only way I know how to "open" the document is to use the .Activate method, however, this immediatly shows the file on the screen. I can then hide it, perform the merge, and then close the embedded mail merge document, leaving only the merged data word file visible to the user. Is there any way to "open" the embedded file so other code can manipulate it (i.e. perform the merge), without displaying the original file on the screen?

Here is some of the code I've been using:

	VB:
	
 
Sub MakeRpt() 
     
    Set WDObj = Sheets("Admin").OLEObjects("TestLabels") 
     
    WDObj.Activate 
    WDObj.Object.Application.Visible = False 
     
    Call fnLinkData(WDObj) 'This function changes the mail merge data source
    Call fnMergeData(WDObj) 'This function performs the merge in a new doc
     
    Word.Documents("Document in " & ActiveWorkbook.Name).Close savechanges:=wdDoNotSaveChanges 
    Word.Application.Visible = True 
    Set WDObj = Nothing 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am using Windows 2000, Excel 2000, and Word 2000. I plan on distributing this file to several users, and it needs to be as user freindly as possible. That is why I have chosen to embed the mail merge document vice leaving it seperate or linking it, therefore eliminating 'file not found' errors. Let me know if I can provide any other information. Thanks for the help!

Ezra Bowman

Hi all,

I am trying to do a mail merge, the source data and main document works fine.

However i encounted 2 problems

Example for Problem 1:

If FieldA = "Yes" show "I will forward an invoice on "

How do i enter the Day merge field into this IF...ELSE word field

Example for Problem 2:

If FieldB = "Yes" show "I will forward an invoice number ("1234") to you"

The word field will not show the whole sentence, only "I will forward an invoice number (" will be shown. I believe the colen causes Microsoft word to take it as the end of the sentence. Is there anyway i can overwrite this, the colen must be there.

Hi!

I'm REALLY new to this programming stuff, so please bear with me.
I've created the following code to perform a mailmerge to word from my excel spreadsheet, but when I run the code I am having a couple problems, and I have no idea where to start looking to correct them. I set up the Mailmerge Main Document as a Word Template with the Mergefields already filled in. The data is coming from the first sheet in my excel workbook which has the headers for each column in Row A, and the headers match the word Mergefields I entered in my word template. When I run the following code, even though there are currently only 60 or so rows of "records" the result in word is 34 pages of my 5160 Avery Labels. After it runs out of the actual records it creates 32 pages of blank labels which appear to be printing only the current date and appear to be pulling the data from blank cells in my "ActivityDate" column of my datasource.

Also, everytime I run the macro, I get a message saying that "a table in the document has become corrupted"

Can someone please tell me where in my code I've gone wrong or what I'm missing?

I'd also like to figure out how to only select certain records from my datasource to be merged, but I suspect that might have something to do with a query or some similar function that might be better addressed in another sub-forum (however any tips on where i might look or post would also be greatly appreciated)


	VB:
	
 MaiMerge() 
    Dim oApp As Word.Application 
    Dim oMainDoc As Word.Document 
    Dim wb As Excel.Workbook 
    Dim sDBPath As String 
    Dim sTPath As String 
     
    Set wb = ActiveWorkbook 
    sDBPath = wb.Path & "CalendarStickers.xls" 
    sTPath = wb.Path & "CalendarStickers.dot" 
     
     'Create a new Word Session
    Set oApp = CreateObject("Word.Application") 
     
     'Start a new main document for the mail merge.
    Set oMainDoc = oApp.Documents.Add(sTPath) 
     
    With oMainDoc.MailMerge 
         
        .MainDocumentType = wdMailingLabels 
         
         'Set up the mail merge data source to CalendarStickers.xls.
        sDBPath = wb.Path & "CalendarStickers.xls" 
        .OpenDataSource Name:=sDBPath, _ 
        SQLStatement:="SELECT * FROM `Dates$`" 
    End With 
     
     'Perform the mail merge to a new document.
    With oMainDoc 
        .MailMerge.Destination = wdSendToNewDocument 
        .MailMerge.SuppressBlankLines = True 
        With .MailMerge.DataSource 
            .FirstRecord = wdDefaultFirstRecord 
            .LastRecord = wdDefaultLastRecord 
        End With 
        .MailMerge.Execute Pause:=False 
    End With 
     
     'Activate word and display document
    With oApp 
        .Visible = True 
        .ActiveWindow.WindowState = 0 
        .Activate 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines



No luck finding an answer? You could always try Google.