I am a retired music teacher who is self-taught on Excel, and so I have some holes in my technical background. I have developed a program that is used by a non-profit group involved in sponsoring and coordinating high school competitive music events. I want to add a feature to the program that enables the user to print standard size labels from data that has been entered into Excel. To achieve this I am trying to use the Mail Merge feature in Word. I tried simply using excel, but the margins for printing labels were always a problem with many printers. By using Mail Merge, the user can adjust the labels more completely to properly fit them on their printer.
I should mention that my program is used by untrained parents who volunteer their time to prepare and help run these events. My program is complete, and seems to work in general, at least in the initial tests. I have one thing I would like to improve...
The program works like this; The basic program will be used to score the event. First the user enters the name of the participating groups and the names of the judges and some other show parameters. Excel sorts the data and generates a named range entitled _A_Label_Table on one of the worksheets. The macro I have written (after a lot a trial and error as well as reading from various sites on the internet) selects the named range and uses a mail-merge Microsoft Word template (I provide it) that the user is told to have in the same file with the Excel program.
The program seems to do everything I want it to do, but just before the labels are generated, there is a pop-up asking the user to select the table. I thought I called out the table in the vba, but I must have done something wrong, and I have not been able to figure out what it is. The pop-up lists all of the ranges in the program, and there are quite a few. I would much prefer that the pop-up never happens, and that the macro selects the pop-up automatically, as many of those using the program are totally untrained in anything technical, and are intimidated by a pop-up.
The code is taken from the Word VBA Code generator and modified to be used by Excel. I hope. I minimize things frequently to avoid frightening the untrained user, and often I display a progress graph to reassure the user that the program is still working. I have not as yet figured out how to do that here, but that is not the problem I am yet dealing with. I am first dealing with trying to figure out a way to eliminate the pop-up that asks the user to confirm the named range (which is always the first named range alphabetically).
I am including the code that I think is applicable. There are a couple of statements that I don't fully understand that were generated by the code generator or that I saw on other websites where people have solved a similar problem. Hopefully I have applied them correctly, but I am not certain. Advice always appreciated. Computer memory is always of concern to me, but I think I am ok here by clearing the buffers when the work is complete. Let me know if I am not. Here is my macro.
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. The Template form will be displayed while SureScore transfers data to Microsoft Word. Please be patient.", 0, "USER INFORMATION"
Application.WindowState = xlMinimized ' Hide Excel in total
Set WrdApp = CreateObject("Word.Application")
WrdApp.Visible = True
Set WrdDoc = WrdApp.Documents.Open(sPath & "\Label_Template.doc")
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." & vbLf & vbLf & _
"If you do not want to print labels now, your ready-to-print sheet of labels will be saved in Microsoft Word format under the name My_Show_Labels. These labels can be accessed directly from Microsoft Word, or from SureScore using the 'RESET LABELS' button on the MAIN MENU." & vbLf & vbLf & _
"If you make any changes to the judge's list, you must click the 'LABELS' button on the MAIN MENU to update the sheet of Ready-to-Print labels." & vbLf & vbLf & "When you are done viewing or printing your labels, completely close Microsoft Word to release unused computer memory and return to this program.", 0, "USER INFORMATION"
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. These labels can be accessed directly from Microsoft Word, or from SureScore using the 'PRINT LABELS' button on the MAIN MENU." & vbLf & vbLf & _
"If you make any changes to the judge's list or the list of bands, you must click the 'RESET LABELS' button on the MAIN MENU to update the sheet of Ready-to-Print labels.", 0, "USER INFORMATION"
WrdApp.Quit ' close the Word application
ActiveWindow.WindowState = xlMaximized
End If
Application.ScreenUpdating = True
Set WrdDoc = Nothing
Set WrdApp = Nothing
Exit Sub
ErrHandler:
MsgBox "We have encountered an error in processing your request to print show labels. Please check to make sure that the document titled 'Label_Template.doc' is located in the same directory as this program." & vbLf & vbLf & _
"This document provides the formatting required to print your labels, and it must be in the same folder as this program in order for the program to access it.", 0, "USER INFORMATION"
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlMaximized
End Sub
I believe that I am acting correctly within the forum guidelines, and if not, please forgive me and let me know what I should do. I would very much appreciate any help or advice.
I should mention that my program is used by untrained parents who volunteer their time to prepare and help run these events. My program is complete, and seems to work in general, at least in the initial tests. I have one thing I would like to improve...
The program works like this; The basic program will be used to score the event. First the user enters the name of the participating groups and the names of the judges and some other show parameters. Excel sorts the data and generates a named range entitled _A_Label_Table on one of the worksheets. The macro I have written (after a lot a trial and error as well as reading from various sites on the internet) selects the named range and uses a mail-merge Microsoft Word template (I provide it) that the user is told to have in the same file with the Excel program.
The program seems to do everything I want it to do, but just before the labels are generated, there is a pop-up asking the user to select the table. I thought I called out the table in the vba, but I must have done something wrong, and I have not been able to figure out what it is. The pop-up lists all of the ranges in the program, and there are quite a few. I would much prefer that the pop-up never happens, and that the macro selects the pop-up automatically, as many of those using the program are totally untrained in anything technical, and are intimidated by a pop-up.
The code is taken from the Word VBA Code generator and modified to be used by Excel. I hope. I minimize things frequently to avoid frightening the untrained user, and often I display a progress graph to reassure the user that the program is still working. I have not as yet figured out how to do that here, but that is not the problem I am yet dealing with. I am first dealing with trying to figure out a way to eliminate the pop-up that asks the user to confirm the named range (which is always the first named range alphabetically).
I am including the code that I think is applicable. There are a couple of statements that I don't fully understand that were generated by the code generator or that I saw on other websites where people have solved a similar problem. Hopefully I have applied them correctly, but I am not certain. Advice always appreciated. Computer memory is always of concern to me, but I think I am ok here by clearing the buffers when the work is complete. Let me know if I am not. Here is my macro.
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. The Template form will be displayed while SureScore transfers data to Microsoft Word. Please be patient.", 0, "USER INFORMATION"
Application.WindowState = xlMinimized ' Hide Excel in total
Set WrdApp = CreateObject("Word.Application")
WrdApp.Visible = True
Set WrdDoc = WrdApp.Documents.Open(sPath & "\Label_Template.doc")
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." & vbLf & vbLf & _
"If you do not want to print labels now, your ready-to-print sheet of labels will be saved in Microsoft Word format under the name My_Show_Labels. These labels can be accessed directly from Microsoft Word, or from SureScore using the 'RESET LABELS' button on the MAIN MENU." & vbLf & vbLf & _
"If you make any changes to the judge's list, you must click the 'LABELS' button on the MAIN MENU to update the sheet of Ready-to-Print labels." & vbLf & vbLf & "When you are done viewing or printing your labels, completely close Microsoft Word to release unused computer memory and return to this program.", 0, "USER INFORMATION"
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. These labels can be accessed directly from Microsoft Word, or from SureScore using the 'PRINT LABELS' button on the MAIN MENU." & vbLf & vbLf & _
"If you make any changes to the judge's list or the list of bands, you must click the 'RESET LABELS' button on the MAIN MENU to update the sheet of Ready-to-Print labels.", 0, "USER INFORMATION"
WrdApp.Quit ' close the Word application
ActiveWindow.WindowState = xlMaximized
End If
Application.ScreenUpdating = True
Set WrdDoc = Nothing
Set WrdApp = Nothing
Exit Sub
ErrHandler:
MsgBox "We have encountered an error in processing your request to print show labels. Please check to make sure that the document titled 'Label_Template.doc' is located in the same directory as this program." & vbLf & vbLf & _
"This document provides the formatting required to print your labels, and it must be in the same folder as this program in order for the program to access it.", 0, "USER INFORMATION"
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlMaximized
End Sub
I believe that I am acting correctly within the forum guidelines, and if not, please forgive me and let me know what I should do. I would very much appreciate any help or advice.
