30+ emtpy page output

Delcasa · #1 04-21-2015, 08:44 PM

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.

gmayor · #2 04-22-2015, 10:44 PM

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

Delcasa · #3 04-23-2015, 09:55 PM

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?

Delcasa · #4 04-30-2015, 11:46 AM

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?

gmayor · #5 04-30-2015, 10:26 PM

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.

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