![]() The "Sheet1" in, SQLStatement:="SELECT * FROMSheet1$ ", _ can be changed to the name of the sheet containing the source data for the merge. ![]() The "P" in sSQLWhere = Replace(sSQLWhere, "T1", "P") can also be change to the value been filtered on, but in my case I want all the records containing "P" in the "Status" column. OK so with a lot of help from I finally got a working code which does exactly what I want.īTW the "Status" in sSQLModel = " Where ( Status = 'T1' ) " can be change to any other column heading, but in my case I am filtering based on a value in the column F (Status). , SQLStatement:="SELECT * FROM `Sheet1$`", _ ' changed because your tab is named Sheet1 SSQLWhere = Replace(sSQLWhere, "T1", "P") ' changed replace, possibly with some screen value SPathFileTemplate = xls.GetOpenFilename(" docx file,*.docx",, "Template file") If (sIn = "" Or sIn = "False") Then Exit SubĪdded 1/22 aft ' = added =ĭim xls As Excel.Application ' for me, because I am running in MSAccess as mdbĭim wrdApp As Word.Application ' for you, to have WORD running SIn = SelectAFile(sInitial:=sDriveSAO, sTitle:=" XLS file") In the SQL, change the tab name from Detail$ to yourTab$ (needs trailing $) "Software\Adobe\Acrobat Distiller\PrinterJobControl", _ 'Put the output filename where Acrobat could find it "Software\Adobe\Acrobat Distiller\PrinterJobControl" 'Create the Registry Key where Acrobat looks for a file nameĬreateNewRegistryKey HKEY_CURRENT_USER, _ (3) I have Adobe PDF as a Printer (the registry routines were from the web-Google them). WrdApp.Visible = True ' you can say False (2) Prior to the above, make the doc Visible (or Invisible) ' setup the template document , SQLStatement:="SELECT * FROM `Detail$`", _ "HDR=YES IMEX=1 "" Jet OLEDB:System database="""" " _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ SSQLWhere = Replace(sSQLWhere, "T1", mydatavariable)ĭoc.MailMerge.OpenDataSource Name:=sIn, _ĬonfirmConversions:=False, readOnly:=False, LinkToSource:=True, _ĪddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ SSQLWhere = sSQLModel ' never replace in the model SSQLModel = " Where ( AssignLtrType = 'T1' or AssignLtrType = 'T2' ) " (1) What I use is the WHERE clause (on the OpenDataSource, you probably don't need all those options) ' setup the SQLĭim sSQLModel As String, sSQLWhere As String I have uploaded the data I am trying to work on: I am running Office 2013 and so far I have the code in bits and pieces and had no luck when trying to run it. (3) This file should be saved in PDF format. (2) Run mailmerge without displaying Microsoft Word and only displaying the "Save As" dialog where the user can select where to save the file. (1) Select recepients by filtering the "Status" column, so if the user pressed the first button, it will run the mail merge only for records with "P" in the status column. This word template file called "MyTemplate" will be in the same directory as the excel file. I have created this work template for the fields. ![]() I want to run a mailmerge using the below buttons on the user form: Records marked as "Not Completed" will have the letter "N" in the status column. Records marked as "Completed" will have the letter "Y" in the status column. Records marked as "In Progress" will have the letter "P" in the status column. I have created a Userform where you can flag records as "In Progress", "Completed", and "Not Completed".
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |