Label Mailmerge To Word - Too Many Labels/blank Records

  • 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)


  • Re: Label Mailmerge To Word - Too Many Labels/blank Records


    The blank records are probably a problem with the data source. ie the Excel spreadsheet.


    Try going to it, select all the blank lines below the data and then goto Edit>Delete.


    These 'blank' rows may have had some formatting or something previously and aren't actually being regarded as 'blank'


    As for the corrupt table issue, it's possible something has corrupted the document.


    Personally I'd just delete it and rebuild it.:)


    If you only want certain records I suggest you record a macro when you do that manually using mail merge.

    Boo!:yikes:

  • Re: Label Mailmerge To Word - Too Many Labels/blank Records


    Thanks for the reply!


    Unfortunately I've already tried that. And I've checked many of the cells individually...they actually are blank...no formatting, no formulas, nothing. Any other thoughts? Could it have something to do with how I'm calling the mailmerge in my code?

  • Re: Label Mailmerge To Word - Too Many Labels/blank Records


    I'm not sure but this might be the problem.

    Code
    SQLStatement:="SELECT * FROM `Dates$`"


    It appears to say return everything from the sheet/table Dates$.


    Again I would recommend the macro recorder. (Tools>Macros>Record new macro...)


    Use it when you manually do the mail merge.


    That will generate code that you should be able to compare to the existing code and see what needs changed.

    Boo!:yikes:

  • Re: Label Mailmerge To Word - Too Many Labels/blank Records


    Unfortunately, when i tried the manual macro-record method, the part where I selected which records I wanted, didn't appear in the macro...I saw a couple posts from others noting this problem as well, which appears to be something in the Mail merge wizard which prevents the macro from recording the code for the selection of records/recipients.


    Is there any way, i can use the same VBA statement and tell it to select all the records until there's a blank row? (although it's currently not recognizing my blank rows....grrrr)

  • Re: Label Mailmerge To Word - Too Many Labels/blank Records


    You could perhaps use a named range in Excel.


    Any chance you could attach a sample document and workbook?


    It's been some time since I've coded for Word, and I'm a little rusty so something to 'play' with would be good

    Boo!:yikes:

  • Re: Label Mailmerge To Word - Too Many Labels/blank Records


    sure....assuming i can figure out how to attach stuff....it won't let me b/c it says both file sizes exceed the allowable amount....any ideas how I can shrink them without messing up the data?

  • Re: Label Mailmerge To Word - Too Many Labels/blank Records


    If they are too big you could use WinZip or some other compression application.


    You can download that for free and I believe it's actually built-in to later versions of Windows.

    Boo!:yikes:

  • Re: Label Mailmerge To Word - Too Many Labels/blank Records


    I've downloaded the files done a mail merge with the recorder on.


    Part of the code generated was this.

    Code
    ActiveDocument.MailMerge.DataSource.QueryString = _
            "SELECT * FROM C:\CalendarStickers.xls WHERE ((ActivityDesc IS NOT NULL ))" _
             & ""

    Boo!:yikes:

  • Re: Label Mailmerge To Word - Too Many Labels/blank Records


    I just added a named range DataRange and recorded this.

    Code
    ActiveDocument.MailMerge.OpenDataSource Name:="C:\CalendarStickers.xls", _
            ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
            AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
            WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
            Format:=wdOpenFormatAuto, Connection:="DataRange", SQLStatement:="", _
            SQLStatement1:=""

    Boo!:yikes:

  • Re: Label Mailmerge To Word - Too Many Labels/blank Records


    What did you set (maybe the proper word is define?) the data range as though? I need to be able to continue adding rows without having to reset any ranges....will the named range allow me to continue to add?


    I had tried the longer string of code with all the settings and ended up with continual error messages when I tried to run through the code...for some reason, it didn't like my code eventhough i had literally cut and paste it from the code generated by the mailmerge macro I had recorded. Again, I'm so new, I didn't know where to look to figure out what i had done wrong...i just tried to adapt someone elses suggestion which seemed to work a little better.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!