Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-21-2015, 08:44 PM
Delcasa Delcasa is offline 30+ emtpy page output Windows 7 64bit 30+ emtpy page output Office 2010 64bit
Novice
30+ emtpy page output
 
Join Date: Apr 2015
Posts: 3
Delcasa is on a distinguished road
Question 30+ emtpy page output

As I am new to the forums I will quickly introduce myself; I am Dax, 26yrs and work in a hotel in Amsterdam.



For that hotel I want to make a personalized welcome letter for certain guests using Mail Merge options. To compile the database I use an Excel plug-in program called Q&A VISION which can extract the names of the guests I need from the reservation systems Oracle database. I will generate a simple table in excel with the output fields I tell it to extract.

I fixed my issue where dates were not shown correct but I am stuck with something I couldn't really find anything on. This is my problem:

If my Excel database contains 10 rows with data, the Mail Merge will create those 10 but also add a 20-something extra pages, with no data in the fields.

Can anyone assist me in finding a solution to get rid of those useless extra pages?

I have attached both my Excel database file and the Word mailmerge file.
Attached Files
File Type: xlsx CL letters database fictive.xlsx (18.0 KB, 11 views)
File Type: docx CL Letter Dax.docx (277.1 KB, 7 views)
Reply With Quote
  #2  
Old 04-22-2015, 10:44 PM
gmayor's Avatar
gmayor gmayor is offline 30+ emtpy page output Windows 7 64bit 30+ emtpy page output Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

This sort of thing occurs when there is phantom data in cells not used for the merge (maybe a space that is difficult to spot). The following Excel macro should enable you to clean up the worksheet to avoid the problem. Certainly it fixes the problem on the example sheet you provided.

Code:
Option Explicit

Sub CleanSheet()
Dim xlSheet As Object
Dim iCols As Long, iCol As Long, c As Long
Dim iRows As Long, iRow As Long, r As Long
    Set xlSheet = ActiveSheet
    iRows = xlSheet.UsedRange.Rows.Count
    iCols = xlSheet.UsedRange.Columns.Count
    iRow = xlSheet.Cells(xlSheet.Rows.Count, 1).End(-4162).Row
    iCol = xlSheet.Cells(1, xlSheet.Columns.Count).End(-4159).Column
    If iRows > iRow Then
        For r = iRows To iRow + 1 Step -1
            xlSheet.Rows(r).EntireRow.Delete
            DoEvents
        Next r
    End If
    If iCols > iCol Then
        For c = iCols To iCol + 1 Step -1
            xlSheet.Columns(c).EntireColumn.Delete
            DoEvents
        Next c
    End If
    Set xlSheet = Nothing
lbl_Exit:
    Exit Sub
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 04-23-2015, 09:55 PM
Delcasa Delcasa is offline 30+ emtpy page output Windows 7 64bit 30+ emtpy page output Office 2010 64bit
Novice
30+ emtpy page output
 
Join Date: Apr 2015
Posts: 3
Delcasa is on a distinguished road
Default

Thanks!

I am not at work at the moment and won't be for a few days, but as soon as I'm back I will give it a go! If it worked on the example sheet I provided I see no reason why it shouldn't work on the real thing!

Guess that the Q&A VISION is the cause of this kind o phantom data?
Reply With Quote
  #4  
Old 04-30-2015, 11:46 AM
Delcasa Delcasa is offline 30+ emtpy page output Windows 7 64bit 30+ emtpy page output Office 2010 64bit
Novice
30+ emtpy page output
 
Join Date: Apr 2015
Posts: 3
Delcasa is on a distinguished road
Default

Tonight I'm back in the hotel again and will give it a go. I'll let you know how the macro worked out

I am not much of a programmer nor did I ever look into VBA so I can't quiete deceiver what the macro exactly does. I am however very much interested in how it would recognize the cell to delete the 'phantom data' from but keep the ones that are useable.

If my output is larger (more rows in the database file), will the macro still delete the rows beneath that or is writen in such way it deletes data from a fixed number of rows?
Reply With Quote
  #5  
Old 04-30-2015, 10:26 PM
gmayor's Avatar
gmayor gmayor is offline 30+ emtpy page output Windows 7 64bit 30+ emtpy page output Office 2010 32bit
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

It is very difficult to identify the unwanted information, hence the macro
The macro deletes the unused part of the worksheet. Try it on a copy of the worksheet.

If you are using a worksheet as a data source that has other random information divorced from the data part of table, then that other information is going to impinge on the merge process, which will see it as part of the data.
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I output text to a specific cell (x,y)? norwood Word VBA 2 01-31-2014 08:43 AM
Fix data output position relative to page Al See Mail Merge 1 01-16-2014 04:13 PM
mail merge – output to .pdf eNGiNe Mail Merge 3 09-29-2013 11:45 PM
30+ emtpy page output Input from excel, output to word conradin Word 3 04-20-2011 07:48 PM
Output Presentation Settings yeapurvz10 PowerPoint 0 09-17-2010 10:30 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:44 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft