[SOLVED] Control Word MailMerge from Excel


"Excel VBA Help"

 


Play Friv Now - Play Friv Games
Go Back   The Code Cage Forums > Microsoft Topics > Microsoft Excel Forum
Our Info Pages:-
The Code Cage - Microsoft Office help

Notices

Microsoft Excel Forum Members Post questions in this forum if they are related to using Microsoft Excel, this includes Worksheet Functions, Excel VBA Programming, Simple Macro Code & Charting, Excel formula etc.

 
 
Thread Tools Rate Thread Display Modes
  #1  
Old 15th January 2014, 03:25
Rjqman's Avatar
Rjqman Rjqman is offline
Junior Member
MS Office Version: MS Office 2003, 2007
MS Office Skill Level: Proficient with Excel
 
Join Date: Dec 2013
Location: Florida, USA
Age: 72
Posts: 12
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 5
Thanked 1 Time in 1 Post
Reputation: 20 (4) Rjqman is on a distinguished road
USA
Default Control Word MailMerge from Excel
     Excel: VBA   


------ Register to get rid of these "In Post" ads! ------

Category: Excel: VBA   

I am a 70 year old self-taught programmer working with a non-profit. I have an Excel program where unskilled users enter data, and the data is used to set up a score sheet for high school events. I want to be able to print labels for score sheets from the program as well, since they already enter all applicable data into Excel, it would be nice for them to not have to re-enter it in order to print the standard size 1" x 2-5/8" labels.

I have found that the only practical way to do this is with Microsoft Word, and using the mail merge feature, as this makes it possible for them to adjust margins and settings to easily match their personal printers. The program is used by volunteer parents of high school kids who usually have little or no Excel experience.

I send the user a Word Template which I ask them to place in the same location as the Excel program on their computer. The Template is called 'Label_Template'

After weeks of reading and experimentation, I came up with a subroutine that works, but it has one problem. When it has almost completed its task, the user gets a pop-up inquiry asking where to find the data. I have already told the program where to find the data (it is in a table called _A_Label_Table, the first alphameric defined range in the Excel list of ranges.

The pop-up seems to occur after most of the work has been done, and just before the final label document is generated. The title of the pop-up is "Select Table." It then lists all named ranges, of which the first is the table that will be used, and the Workbook Name block displays correctly. However I do not know why the program asks the user to select the range again. I want to eliminate this pop-up, as the casual user will be confused by the pop-up.

The program is entitled 'SureScore.' The minimizing of the screen is intended to make it less 'scary' to the user as the program works. I have shortened the message box messages, but left everything else. Here is the subroutine that I have written to generate the labels;



VBA CODE:
Sub CreateLabels() 
     
    Dim i As Integer 
    Dim sPath As String 
    Dim WrdApp As Word.Application 
    Dim WrdDoc As Object 
    Dim wbName As String 
    Dim LabelName As String 
On Error Goto ErrHandler: 
    ActiveWindow.WindowState = xlMinimized 
     '
    sPath = ActiveWorkbook.Path 
    wbName = sPath & "\" & ActiveWorkbook.Name 
    LabelName = sPath & "\Show_Labels" 
     
    MsgBox "The next operation may take a few moments. Please be patient." 
    Application.WindowState = xlMinimized ' Hide Excel in total
    Set WrdApp = CreateObject("Word.Application") 
    WrdApp.Visible = True 
     
    Set WrdDoc = WrdApp.Documents.Open(sPath & "\Label_Template.dot") 
     
    With WrdApp 
        .DisplayAlerts = 0 
        .Application.WindowState = wdWindowStateMaximize 
        .ScreenUpdating = False 
        .ActiveDocument.MailMerge.MainDocumentType = 3 
        .ActiveDocument.MailMerge.OpenDataSource Name:= _ 
        wbName, ConfirmConversions _ 
        :=False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _ 
        PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _ 
        WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _ 
        Connection:= _ 
        "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=wbName;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password=" _ 
        , SQLStatement:="SELECT * FROM `_A_Label_Table`", SQLStatement1:="", SubType:= _ 
        wdMergeSubTypeAccess 
        .ActiveDocument.MailMerge.Destination = wdSendToNewDocument 
        .ActiveDocument.MailMerge.SuppressBlankLines = True 
         '
        With WrdApp.ActiveDocument.MailMerge 
            With .DataSource 
                .FirstRecord = wdDefaultFirstRecord 
                .LastRecord = wdDefaultLastRecord 
            End With 
            .Execute Pause:=False 
        End With 
        .ActiveDocument.SaveAs Filename:=sPath & "\My_Show_Labels.doc", FileFormat _ 
        :=wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _ 
        True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _ 
        False, SaveNativePictureFormat:=False, SaveFormsData:=False, _ 
        SaveAsAOCELetter:=False 
        .ActiveDocument.Close (wdSaveChanges) 
        .ActiveDocument.Close (wdDoNotSaveChanges) 
        .ScreenUpdating = True 
        .Application.WindowState = wdWindowStateMinimize 
    End With 
     '
    Application.WindowState = xlMaximized 
     '
    If MsgBox("SureScore has generated a Ready-to-Print sheet of Labels for your show. Do you want to view and/or print the labels at this time?", vbYesNo, "SURESCORE USER INFORMATION") = vbYes Then 
        Set WrdDoc = WrdApp.Documents.Open(sPath & "\My_Show_Labels.doc") 
        MsgBox "To print your labels now, insert a sufficient number of blank sheets of standard 1 inch by 2-5/8 inch mailing labels into your printer and use the Microsoft Word Print commands to print your labels." 
        Application.WindowState = xlMinimized 
        Application.ScreenUpdating = False 
        Application.WindowState = xlMaximized 
        ActiveWindow.WindowState = xlMaximized 
        WrdApp.Application.WindowState = wdWindowStateMaximize 
    Else 
        MsgBox "Your ready-to-print sheet of labels will be saved in Microsoft Word format under the name My_Show_Labels." 
        WrdApp.Quit ' close the Word application
        ActiveWindow.WindowState = xlMaximized 
    End If 
     
    Application.ScreenUpdating = True 
    Set WrdDoc = Nothing 
    Set WrdApp = Nothing 
    Exit Sub 
ErrHandler: 
    MsgBox "SureScore has encountered an error" 
    Application.WindowState = xlMaximized 
    ActiveWindow.WindowState = xlMaximized 
End Sub 


What can I do to eliminate the pop-up? Thanks very much.


Last edited by Simon Lloyd; 15th January 2014 at 03:51.


The Code Cage Advertisment
Advertisement
  #2  
Old 15th January 2014, 03:56
Simon Lloyd's Avatar
Simon Lloyd Simon Lloyd is offline
Administrator
MS Office Version: MS Office 97 - 2013
MS Office Skill Level: Can use most of MS Office
 
Join Date: Aug 2008
Location: Manchester, England
Age: 53
Posts: 11,884
Posts Per Day: 3.51
My Top Tip Count: 11
Thanks to others: 49
Thanked 441 Times in 425 Posts
Reputation: 6660 (194) Simon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond repute
11 Top Tips
Wales
Default Re: Control Word MailMerge from Excel

------ Register to get rid of these "In Post" ads! ------


Welcome to the forum have you proved the .LastRecord? it may be a point for testing purposes to count the number of records at the start and then the number of records it's trying to print.
__________________
Regards,
Simon Lloyd
Microsoft Office Help

Want to say thanks? then hit this How to say thanks! button in any post you want to thank
If you find one of my posts helpful then hit this one Helpful Answer in the post that helped most!





  #3  
Old 15th January 2014, 04:27
Rjqman's Avatar
Rjqman Rjqman is offline
Junior Member
MS Office Version: MS Office 2003, 2007
MS Office Skill Level: Proficient with Excel
 
Join Date: Dec 2013
Location: Florida, USA
Age: 72
Posts: 12
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 5
Thanked 1 Time in 1 Post
Reputation: 20 (4) Rjqman is on a distinguished road
USA
Default Re: Control Word MailMerge from Excel

------ Register to get rid of these "In Post" ads! ------


No. I do not know how to do that. I know that the table prints correctly when I respond to the inquiry in the drop-down box though, if that helps. If you could guide me as to the correct coding to add or change, I would be very willing to try.



The Code Cage Advertisment
Advertisement
  #4  
Old 15th January 2014, 06:01
Simon Lloyd's Avatar
Simon Lloyd Simon Lloyd is offline
Administrator
MS Office Version: MS Office 97 - 2013
MS Office Skill Level: Can use most of MS Office
 
Join Date: Aug 2008
Location: Manchester, England
Age: 53
Posts: 11,884
Posts Per Day: 3.51
My Top Tip Count: 11
Thanks to others: 49
Thanked 441 Times in 425 Posts
Reputation: 6660 (194) Simon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond reputeSimon Lloyd has a reputation beyond repute
11 Top Tips
Wales
Default Re: Control Word MailMerge from Excel

------ Register to get rid of these "In Post" ads! ------


Just as a matter of interest, have you tried the routine with a much smaller label file?, it could be that printer buffer size is an issue...etc

It would be helpful if you could supply a sample workbook and a sample label list, it will save us trying to recreate what you have there.

Attachments.
Information

Information

To upload a file, click reply then add your few words, to the left you will see the Manage Attachments button, this is where you get to add files for upload, scroll down to the see submit button, if you have any trouble please use this Contact Us link or the one at the bottom of the any page.





If you have a workbook that has sensitive data you can ask the person helping to click this checkbox I will accept sensitive upload
it's found in quick reply at the bottom or in go advanced, this will then give you a "Sensitive upload" browse button when you next reply by clicking go advanced, your chosen file will be uploaded but not public, only you, the person who clicked the checkbox and staff here will be able to see the download link for your file.

The file(s) gets automatically deleted after 1 week.

Help on your file will be posted publicly in the forum but without releasing anything of a sensitive nature, this is to help other people with a similar problem!
__________________
Regards,
Simon Lloyd
Microsoft Office Help

Want to say thanks? then hit this How to say thanks! button in any post you want to thank
If you find one of my posts helpful then hit this one Helpful Answer in the post that helped most!





The Following User Says Thank You to Simon Lloyd For This Useful Post:
Rjqman (25th January 2014)
  #5  
Old 15th January 2014, 10:33
snb's Avatar
snb snb is offline
Forum VIP
MS Office Version: > Office 97
MS Office Skill Level: Interested
 
Join Date: Feb 2011
Location: Europe
Posts: 1,393
Posts Per Day: 0.56
My Top Tip Count: 0
Thanks to others: 0
Thanked 314 Times in 307 Posts
Reputation: 5076 (70) snb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond repute
Default Re: Control Word MailMerge from Excel

------ Register to get rid of these "In Post" ads! ------


1. - Do not use a word .dot (template) file but a .doc file instead
2. - design the maindocument manually, link it to the Excel table mauallly and save it. The link to the Excel fille will be stored in the maindocument too. (eg. "example.doc")
3. - The only code you need in Excel is:

VBA CODE:
Sub M_snb() 
    With getobject("G:\OF\example.doc") 
        .mailmerge.execute 
        .application.activedocument.printout 
        .application.activedocument.close 0 
        .close 0 
    End With 
End Sub 



  #6  
Old 15th January 2014, 17:06
Rjqman's Avatar
Rjqman Rjqman is offline
Junior Member
MS Office Version: MS Office 2003, 2007
MS Office Skill Level: Proficient with Excel
 
Join Date: Dec 2013
Location: Florida, USA
Age: 72
Posts: 12
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 5
Thanked 1 Time in 1 Post
Reputation: 20 (4) Rjqman is on a distinguished road
USA
Default Re: Control Word MailMerge from Excel

------ Register to get rid of these "In Post" ads! ------


to SNB -

Thanks very much for responding. My problem is that the Excel file name is always different. In fact, the end user has historically been told to save the Excel file under a different name before using it in case the file crashes during their show (it is a large file using lots of memory, and although I have been working on it for almost 12 years, improving and updating and trying to get the memory requirements down, we still have had a couple of crashes every year). I have just finished a rewrite designed to reduce memory requirements and increase processing speed. (Note that the file is written in Excel 2003 as some school districts are still using that revision in my area of the country).

Also note that the end user is what we affectionaly call a 'soccer mom,' meaning that she is usually a parent with little or no Excel skills, volunteering to help her son or daughter's organization, so I want the program to be as user-friendly as possible.

As an example the file sent by me and received by the end user will be named something like 'South HS Show.xls and the user may save it as 'South Revision 1.xls' or something like that, and the original file (which is no longer current) will still be on the computer. I don't think it is possible to link the word file back under these circumstances - if I could, I could test and perhaps use your solution, as I could easily send out the Word file in 'rough' form.

To Simon Lloyd -

I am willing to send it to you if that would be helpful. However, it is very, very large, with 16 worksheets and thousands of lines of code. It is about 6300 kb before data is added. I am not sure that sending the whole workbook via this forum would be practical, but if you think that it would, I will send it. But honestly, I think I have a coding problem with this specific subroutine, not a problem with the whole file, as the darn thing runs fine - even the subroutine runs fine - it just pops up the window asking the user to define the source table after the source table has already been defined.

Of course, if I knew what I was doing, I wouldn't be wasting your time in the first place. Let me know what you would like me to do, and I will gladly do it.



The Code Cage Advertisment
Advertisement
  #7  
Old 15th January 2014, 17:27
snb's Avatar
snb snb is offline
Forum VIP
MS Office Version: > Office 97
MS Office Skill Level: Interested
 
Join Date: Feb 2011
Location: Europe
Posts: 1,393
Posts Per Day: 0.56
My Top Tip Count: 0
Thanks to others: 0
Thanked 314 Times in 307 Posts
Reputation: 5076 (70) snb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond repute
Default Re: Control Word MailMerge from Excel

------ Register to get rid of these "In Post" ads! ------


Do not post that huge workbook in this forum.

To answer your 'objections'

You can always save a copy of the latest workbook as e.g. G:\OF\data.xls
If the maindocument Word contains a link (like I described, entered manually) to that copy (G:\OF\data.xls), it will update that link automatically on opening.
Simpler than that I can't imagine.

The only thing htsat has to be done is merging the maindocument and printing the result. You can use VBA to do so, but even manually it's futile.


Last edited by snb; 15th January 2014 at 17:29.


  #8  
Old 15th January 2014, 17:51
Rjqman's Avatar
Rjqman Rjqman is offline
Junior Member
MS Office Version: MS Office 2003, 2007
MS Office Skill Level: Proficient with Excel
 
Join Date: Dec 2013
Location: Florida, USA
Age: 72
Posts: 12
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 5
Thanked 1 Time in 1 Post
Reputation: 20 (4) Rjqman is on a distinguished road
USA
Default Re: Control Word MailMerge from Excel

------ Register to get rid of these "In Post" ads! ------


To Simon Lloyd -

You inquired if I had tried with a smaller label file. The short answer is yes, but the more complete answer is as follows;

The Excel program picks out the variables needed to prepare the labels and the organizes them. The file could theoretically be very large, but in reality it is always fairly small, as the shows are rarely if ever as large as the program would allow them to be. If there are 5 participating units with 6 judges, there will be 5 x 6, or 30 sheet labels created for use on the judging sheets, along with about 5 extra labels per group for other uses. So in this example there would be 55 labels actually created. The program is designed to have a maximum of 50 participating units with 12 judges, but that number is never reached in real life.

Also, the participating units are in one of 10 size classification groups, although this does not matter much with respect to the question.

- The list of actual judges is copied and pasted into the table with VBA first.
- Then the list of actual participating units and the name of their group is copied and pasted into the table next.
- The parameters of the contest are then pasted into the table (date, name of event)
- Note that there are five columns of items in the table - Name of the show, name of the group, name of the group size class, the date,and the name of the judge.
- Using sort commands, a 'line' in the table is created for each unit with each judge. This provides the information for each of the labels that will be pasted on the judge's score sheet for that unit.
- The table is on one of the 12 worksheets as a named range. The macro accesses this table as well as a word template that I provide, and (hopefully) will generate a sheet of ready-to-print labels using Word's Mail Merge feature.

In response to your other question, I may not have properly 'terminated' the table by defining the '.last record'. I don't know. However, I think I did ok with this because I defined the label range by counting the number of final entries, which I think accomplishes the same goal. I just did not use the term .LastRecord when I did so. However, if I should have used this term, and this would solve my problem of eliminating the pop-up, please let me know how to do this, and I will do so.

I tested with three units and six judges. I hope this answers your question. If not, please let me know.

------------------------------ posted after 10 minutes of original post ------------------------------

To SNB -

I think that could work if I have Excel save a copy of the workbook under a name that I choose, no matter what the end user had named it. I had not considered that possibility until your last response made that more clear to me. I will experiment with that option, but it will take me a little while, as I am not the world's quickest programmer.

Thank you so much for the guidance. I will let you know how that works.


Last edited by Simon Lloyd; 15th January 2014 at 20:18.


  #9  
Old 16th January 2014, 09:18
snb's Avatar
snb snb is offline
Forum VIP
MS Office Version: > Office 97
MS Office Skill Level: Interested
 
Join Date: Feb 2011
Location: Europe
Posts: 1,393
Posts Per Day: 0.56
My Top Tip Count: 0
Thanks to others: 0
Thanked 314 Times in 307 Posts
Reputation: 5076 (70) snb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond reputesnb has a reputation beyond repute
Default Re: Control Word MailMerge from Excel

------ Register to get rid of these "In Post" ads! ------


In the attachment you'll find 2 files.
Unzip them in the same directory.
Open the Word file and the data from the Excel file will be merged into the Wordfile automatically for the first page.

You can use this Wordfile as merge 'template' for future use.

If you replace all data in the Excelfile (but not the columheaders) and save/close it, the new data will be merged into the Word file the next time you open it.

So there is very little to do with VBA.
Attached Files To view attachments in this forum your post count must be 2 or greater. You currently have 0 posts.

like this post: + 10 Reputation


1 members found this post helpful.
The Following 2 Users Say Thank You to snb For This Useful Post:
mancubus (5th February 2014), Rjqman (12th February 2014)
The Code Cage Advertisment
Advertisement
  #10  
Old 17th January 2014, 04:39
Rjqman's Avatar
Rjqman Rjqman is offline
Junior Member
MS Office Version: MS Office 2003, 2007
MS Office Skill Level: Proficient with Excel
 
Join Date: Dec 2013
Location: Florida, USA
Age: 72
Posts: 12
Posts Per Day: 0.01
My Top Tip Count: 0
Thanks to others: 5
Thanked 1 Time in 1 Post
Reputation: 20 (4) Rjqman is on a distinguished road
USA
Default Re: Control Word MailMerge from Excel

------ Register to get rid of these "In Post" ads! ------


Thanks to both of you. I have downloaded your files, snb, and I am working on trying to understand what both of you have recommended and done and apply it to my project - I will respond again when I figure it all out.

------------------------------ posted after 11 Hours 57 minutes of original post ------------------------------

I think I have something that works for me. I have not tested it on other computers, but I will do so. The version requires the user to go to Word to print the labels, which is fine for my needs. I used bits and pieces from the information you provided, and your help is greatly appreciated. The code is in Excel, and the user knows the program under the name 'SureScore'.

NOTE: I wish I had a way to save the Word Template and then to restore it in the event the user does not accept the Microsoft Word Inquiry about loading the data, because I have found that if the inquiry is not accepted, the link between Word and the Excel Data is lost forever. Perhaps you know a way to do that without opening the Word document - just save the original word document and then replace it at the end of the macro in case the user has damaged the link?

Here is the code I ended up with. Please advise if you see a problem. Thank you very, very much for the assistance. This is so much simpler than my original approach.

VBA CODE:
Sub CreateLabels() 
     '
    Dim sPath As String 
    Dim wbName As String 
     '
On Error Goto ErrHandler: 
     ''
    sPath = ActiveWorkbook.Path 
    wbName = sPath & "\" & ActiveWorkbook.Name 
     '
     '   ==== CHECK TO MAKE CERTAIN LABEL_TEMPLATE.DOC =====
     '   ============== IS IN THIS DIRECTORY ===============
     '
    If Dir(sPath & "\" & "Label_Template.doc") <> "" Then 
    Else 
        MsgBox "SureScore has encountered an error in processing your request to generate a sheet of show labels.  Please check to make sure that the Microsoft Word Document titled 'Label_Template.doc' is located in the same directory (or folder) as this copy of SureScore on your computer." & vbLf & vbLf & _ 
        "This document provides the formatting required for your labels, and it must be in the same folder as SureScore in order for SureScore to access it.", 0, "SURESCORE USER INFORMATION" 
        Exit Sub 
    End If 
     '
     '   ======== DELETE ANY EXISTING COPIES OF SURESCORE_DATA =======
     '   ======== TO AVOID NUISANCE USER ABOUT REPLACING FILE =========
     '
    If Dir(sPath & "\" & "SureScore_Data.*") <> "" Then Kill sPath & "\" & "SureScore_Data.*" 
     '
     '   ======== SAVE THE PRESENT WORKBOOK AS 'SURESCORE_DATA' ======
     '   ======== SO WORD CAN ACCESS IT A FILE WITH A FIXED NAME ======
     '
    ActiveWorkbook.SaveAs Filename:= _ 
    sPath & "\" & "SureScore_Data", FileFormat:=xlNormal, _ 
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ 
    CreateBackup:=False 
     '
     '   ==== DELETE THE USER'S WORKBOOK TO AVOID NUISANCE ======
     '   ========QUESTION ABOUT REPLACING IT FROM EXCEL ========
     '
    Kill wbName 
     '
     '   =====  RELOAD THE ORIGINAL WORKBOOK UNDER THE ORGINAL =====
     '   ======  NAME AS ASSIGNED BY THE USER (NOT BY ME) ==========
     '
    ActiveWorkbook.SaveAs Filename:= _ 
    wbName, FileFormat:=xlNormal, _ 
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ 
    CreateBackup:=False 
     '
     '   =========== ADVISE USER TO CLICK 'YES 'WHEN ASKED ==========
     '   ================ ABOUT LOADING LABEL DATA ===============
     '   ==== (HERE IS WHERE I AM AFRAID THE USER MIGHT CLICK NO) =====
     '
    MsgBox "Microsoft Word will now generate an inquiry asking if you want to update your information.  The inquiry will read" & vbLf & vbLf & _ 
    " --------------------------------------------------------------------------- " & vbLf & _ 
    "'Opening this document will run the following SQL command:'" & vbLf & _ 
    "SELECT FROM * '_A_Label_Table'" & vbLf & _ 
    "Data from your database will be placed in the document.  Do you want to continue?" & vbLf & _ 
    " --------------------------------------------------------------------------- " & vbLf & vbLf & _ 
    "When this form is displayed, please click 'Yes' to proceed.", 0, "SURESCORE USER INFORMATION" 
     '
     '     ======== ACCESS THE LABEL TEMPLATE AND CREATE LABELS =======
     '     ======== AND SAVE IT UNDER THEN NAME 'MY_SHOW_LABELS' ======
     '
    With GetObject(sPath & "\Label_Template.doc") 
        .MailMerge.Execute 
        .Application.ActiveDocument.SaveAs Filename:=sPath & "\My_Show_Labels.doc", FileFormat _ 
        :=wdFormatDocument, LockComments:=False, Password:="", AddToRecentFiles:= _ 
        True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:= _ 
        False, SaveNativePictureFormat:=False, SaveFormsData:=False, _ 
        SaveAsAOCELetter:=False 
        .Application.ActiveDocument.Close (wdSaveChanges) 
        .Close 0 
    End With 
     '
    MsgBox "SureScore has generated a Ready-to-Print document entitled 'My_Show_Labels' in this folder on your computer." & vbLf & vbLf & _ 
    "To print your show labels, open Microsoft Word and access this file.  Use blank 1'' x 2-5/8'' Avery or equivalent labels (30 to a sheet).", 0, "SURESCORE USER INFORMATION" 
     '
    Exit Sub 
     '
ErrHandler: 
    MsgBox "SureScore has encountered an unspecified error in processing your request to generate show labels." & vbLf & vbLf & _ 
    "Please make certain that the Microsoft Word Object Library is activated in Excel's Visual Basic Editor.", 0, "SURESCORE USER INFORMATION" 
     '
     '
End Sub 


Last edited by Rjqman; 17th January 2014 at 04:39.


The Code Cage Advertisment
Advertisement
 
The Code Cage at Facebookhe Code Cage at Twitter


Tags
control, excel, mailmerge, word

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

The Code Cage Affilliates



http://www.thecodecage.com/forumz tested by McAfee Security http://www.thecodecage.com/forumz tested by McAfee Security

All times are GMT +1. The time now is 07:48.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Template-Modifications by TMS
No part of this board, functionality or creatives may be copied or reproduced either in part or full without the express permission of The Code Cage Team.
Copyright ©2008 - , Simon lloyd.
We are not associated with nor employed by Microsoft in any way, we simply provide resources!
All MS office icons are registered trademarks of the application they represent and are used with permission from Microsoft
UA-8135493-1
Cultural Forum | Study at Malaysian University
X vBulletin 3.8.7 Debug Information
  • Page Generation 0.31410 seconds
  • Memory Usage 6,785KB
  • Queries Executed 16 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footerEnd
  • (1)ad_footerStart
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_headerEnd
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_leftcolumn_code
  • (1)ad_navbar_below
  • (5)ad_postbit_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (3)bbcode_code
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (10)helpful_answers
  • (1)helpfulanswers_javascript
  • (1)navbar
  • (3)navbar_link
  • (3)navbar_noticebit
  • (52)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (1)pagenav_pagelink
  • (10)post_thanks_box
  • (3)post_thanks_box_bit
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (2)post_thanks_postbit
  • (10)post_thanks_postbit_info
  • (10)postbit
  • (1)postbit_attachment
  • (10)postbit_ctoptip
  • (10)postbit_onlinestatus
  • (60)postbit_reputation
  • (10)postbit_wrapper
  • (1)showthread_changepostsowner_link
  • (1)showthread_extra_fields
  • (1)showthread_quickreply_mrgdp
  • (1)showthread_reason_popup
  • (1)spacer_close
  • (1)spacer_open
  • (1)su_form
  • (4)tagbit
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_datastore.php
  • ./includes/class_hook.php
  • ./awcoding/plugins/awc.php
  • ./awcoding/plugins/ds.php
  • ./awcoding/plugins/ss.php
  • ./includes/functions_notice.php
  • ./mobiquo/smartbanner.php
  • ./mobiquo/include/classTTConnection.php
  • ./mobiquo/smartbanner/head.inc.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/vbatags.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php
  • ./includes/functions_misc.php 

Hooks Called:
  • init_startup
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_check_criteria
  • notices_noticebit
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • helpfulanswers_showthreadorpost
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • helpfulanswers_show_normal
  • reputation_power
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • error_fetch
  • post_thanks_function_fetch_thanks_bit_start
  • post_thanks_function_show_thanks_date_start
  • post_thanks_function_fetch_thanks_bit_end
  • post_thanks_function_fetch_post_thanks_template_start
  • post_thanks_function_fetch_post_thanks_template_end
  • postbit_attachment
  • pagenav_page
  • pagenav_complete
  • tag_fetchbit
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete