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 <= 8 Then
MsgBox ("You entered a number less than or equal to eight. Showing the first eight rows and hiding everything else.")
txtNumCellsToShow.Text = 8
varNumCellsToShow = 10
Range("B1").Select
End If
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 MsgBox ("There are 8 tags to a page and you have elected to print " & FinalRow - 1 & " tags. This will print to 1 page.")
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 <= 9 And LastFilledRow <= 2 Then MsgBox ("There are 8 student aide tags to a page and you have elected to print " & LastFilledRow - 1 & " tag. This will print to 1 page.")
If FinalRow <= 9 And LastFilledRow > 2 Then MsgBox ("There are 8 student aide tags to a page and you have elected to print " & LastFilledRow - 1 & " tags. This will print to 1 page.")
If FinalRow > 9 And LastFilledRow <= 2 Then
NumberofPages = WorksheetFunction.RoundUp((FinalRow - 1) / 8, 0)
MsgBox ("There are 8 tags to a page, you have elected to print " & LastFilledRow - 1 & " tag. 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
If FinalRow > 9 And LastFilledRow > 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 & "\Desktop\Student 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
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 <= 8 Then
MsgBox ("You entered a number less than or equal to eight. Showing the first eight rows and hiding everything else.")
txtNumCellsToShow.Text = 8
varNumCellsToShow = 10
Range("B1").Select
End If
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 MsgBox ("There are 8 tags to a page and you have elected to print " & FinalRow - 1 & " tags. This will print to 1 page.")
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 <= 9 And LastFilledRow <= 2 Then MsgBox ("There are 8 student aide tags to a page and you have elected to print " & LastFilledRow - 1 & " tag. This will print to 1 page.")
If FinalRow <= 9 And LastFilledRow > 2 Then MsgBox ("There are 8 student aide tags to a page and you have elected to print " & LastFilledRow - 1 & " tags. This will print to 1 page.")
If FinalRow > 9 And LastFilledRow <= 2 Then
NumberofPages = WorksheetFunction.RoundUp((FinalRow - 1) / 8, 0)
MsgBox ("There are 8 tags to a page, you have elected to print " & LastFilledRow - 1 & " tag. 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
If FinalRow > 9 And LastFilledRow > 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 & "\Desktop\Student 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
