#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
#5
|
||||
|
||||
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 |
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 |
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 |