Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
merge search to long
Recorded a macro in word for merge. Problem is when it searches excel data it
wants to cover the full spread sheet. Have tried first and last also nbrs 1 & 50 still goes all the way. Is it possible to stop the search at a given point befor the end of excel sheet. row 200 etc? Posted this a while back had problems and could not acces this web site Thanks to all Following is my macro code Sub xy() ' xy Macro ' Macro recorded April 4, 2008 by Curtiss A. Greer ' ActiveDocument.MailMerge.EditMainDocument Selection.TypeParagraph Selection.InsertDateTime DateTimeFormat:="MMMM d, yyyy", InsertAsField:= _ True, DateLanguage:=wdEnglishUS, CalendarType:=wdCalendarWestern, _ InsertAsFullWidth:=False Selection.TypeParagraph ActiveDocument.MailMerge.OpenDataSource Name:="C:\Parade\Parade.xls", _ ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "DSN=Excel Files;DBQ=C:\Parade\Parade.xls;DriverId=790;MaxBuf ferSize=2048;PageTimeout=5;" _ , SQLStatement:="SELECT * FROM `Data$`", SQLStatement1:="" ActiveDocument.MailMerge.EditMainDocument ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _ "Contact_Person" Selection.TypeParagraph ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _ "Address" Selection.TypeParagraph ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _ "CityState" Selection.TypeParagraph ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Zip_" Selection.TypeParagraph ActiveDocument.MailMerge.DataSource.QueryString = _ "SELECT * FROM `Data$` WHERE ((`Contact Person` IS NOT NULL ) AND (` Address` IS NOT NULL ) AND (`City&State` IS NOT NULL ) AND (`Zip ` IS NOT NULL ) AND (`Amount` = 0))" _ & "" With ActiveDocument.MailMerge .Destination = wdSendToPrinter .MailAsAttachment = False .MailAddressFieldName = "" .MailSubject = "" .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord 1 no luck here .LastRecord = wdDefaultLastRecord 50 End With .Execute Pause:=True End With CommandBars("Stop Recording").Visible = False End Sub |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
merge search to long
I shudder at the first part of your code. Using a macro to build a
mailmerge main document is a completely daft idea in my books. However, with respect to what is troubling you, using With ActiveDocument.MailMerge .Destination = wdSendToNewDocument With .DataSource .FirstRecord = 1 .LastRecord = 3 End With .Execute End With results in the merge being executed for only the first 3 records from a data source for me. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Curt" wrote in message ... Recorded a macro in word for merge. Problem is when it searches excel data it wants to cover the full spread sheet. Have tried first and last also nbrs 1 & 50 still goes all the way. Is it possible to stop the search at a given point befor the end of excel sheet. row 200 etc? Posted this a while back had problems and could not acces this web site Thanks to all Following is my macro code Sub xy() ' xy Macro ' Macro recorded April 4, 2008 by Curtiss A. Greer ' ActiveDocument.MailMerge.EditMainDocument Selection.TypeParagraph Selection.InsertDateTime DateTimeFormat:="MMMM d, yyyy", InsertAsField:= _ True, DateLanguage:=wdEnglishUS, CalendarType:=wdCalendarWestern, _ InsertAsFullWidth:=False Selection.TypeParagraph ActiveDocument.MailMerge.OpenDataSource Name:="C:\Parade\Parade.xls", _ ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "DSN=Excel Files;DBQ=C:\Parade\Parade.xls;DriverId=790;MaxBuf ferSize=2048;PageTimeout=5;" _ , SQLStatement:="SELECT * FROM `Data$`", SQLStatement1:="" ActiveDocument.MailMerge.EditMainDocument ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _ "Contact_Person" Selection.TypeParagraph ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _ "Address" Selection.TypeParagraph ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _ "CityState" Selection.TypeParagraph ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Zip_" Selection.TypeParagraph ActiveDocument.MailMerge.DataSource.QueryString = _ "SELECT * FROM `Data$` WHERE ((`Contact Person` IS NOT NULL ) AND (` Address` IS NOT NULL ) AND (`City&State` IS NOT NULL ) AND (`Zip ` IS NOT NULL ) AND (`Amount` = 0))" _ & "" With ActiveDocument.MailMerge .Destination = wdSendToPrinter .MailAsAttachment = False .MailAddressFieldName = "" .MailSubject = "" .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord 1 no luck here .LastRecord = wdDefaultLastRecord 50 End With .Execute Pause:=True End With CommandBars("Stop Recording").Visible = False End Sub |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
merge search to long I HAVE A PROBLEM :)
Hi,
PS: Look into this close situation: http://www.experts-exchange.com/Prog..._21767403.html I was looking for information to resolve a little problem that I'm having and I saw this topic hear and I hope if you can help me I have a event im my page page.aspx.cs I tried to run the code that I writed and I have an Error in he docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM [intervenientes] WHERE [intid] = 2"; The message error is: {"Command failed"} If I comment this line I don't have any problem with my process.. It merge a new .doc, but with all data from the table intervinientes... and I want to get specic data... I hope to get any information about this question... Anyone who did something like this before... Did a process in C# and merge a document with specific data from a SQL Server data source... How can I do a SQL command only to merge specific .doc? That is all my event code: void testeWord() { String data = DateTime.Today.ToShortDateString(); string utilizador = HttpContext.Current.User.Identity.Name.ToString(); object objMiss = Type.Missing; object ficheiroOrigem = "C:\\Documents and Settings\\Nuno Oliveira\\My Documents\\Nuno Oliveira\\CV\\mailmerge.doc"; object ficheiroDest = "C:\\Documents and Settings\\Nuno Oliveira\\My Documents\\Nuno Oliveira\\CV\\MailMerge" + utilizador + "." + data + ".doc"; object objTrue = true; object objFalse = false; Microsoft.Office.Interop.Word.Document docWord; //open Microsoft Word Microsoft.Office.Interop.Word.ApplicationClass appWord = new Microsoft.Office.Interop.Word.ApplicationClass(); appWord.Visible = false; //open Template file docWord = appWord.Documents.Open(ref ficheiroOrigem, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss); docWord.Select(); //open datasource object format = Microsoft.Office.Interop.Word.WdOpenFormat.wdOpenF ormatText; string source = "C:\\Documents and Settings\\Nuno Oliveira\\My Documents\\My Data Sources\\localhost mba_testes intervenientes.odc"; docWord.MailMerge.OpenDataSource(source, ref format, ref objFalse, ref objMiss, ref objTrue, ref objFalse, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss); docWord.MailMerge.Destination = Microsoft.Office.Interop.Word.WdMailMergeDestinati on.wdSendToNewDocument; docWord.MailMerge.SuppressBlankLines = true; docWord.MailMerge.DataSource.QueryString = "SELECT [intnome] FROM [intervenientes] WHERE [intid] = 2"; docWord.MailMerge.DataSource.FirstRecord = (int)Microsoft.Office.Interop.Word.WdMailMergeDefa ultRecord.wdDefaultFirstRecord; docWord.MailMerge.DataSource.LastRecord = (int)Microsoft.Office.Interop.Word.WdMailMergeDefa ultRecord.wdDefaultLastRecord; docWord.MailMerge.Execute(ref objFalse); appWord.ActiveDocument.SaveAs(ref ficheiroDest, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss, ref objMiss); docWord.Close(ref objFalse, ref objMiss, ref objMiss); appWord.Visible = true; appWord.ShowMe(); //appWord.Quit(null, null, null); //Server.Transfer(@"\\localhost\C:\Documents and Settings\Nuno Oliveira\My Documents\Nuno Oliveira\CV\rESULTADOmAILmERGE.DOC"); } Canyou help me Thank you for your attencion... Regards, Nuno Oliveira "Doug Robbins - Word MVP" wrote: I shudder at the first part of your code. Using a macro to build a mailmerge main document is a completely daft idea in my books. However, with respect to what is troubling you, using With ActiveDocument.MailMerge .Destination = wdSendToNewDocument With .DataSource .FirstRecord = 1 .LastRecord = 3 End With .Execute End With results in the merge being executed for only the first 3 records from a data source for me. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP "Curt" wrote in message ... Recorded a macro in word for merge. Problem is when it searches excel data it wants to cover the full spread sheet. Have tried first and last also nbrs 1 & 50 still goes all the way. Is it possible to stop the search at a given point befor the end of excel sheet. row 200 etc? Posted this a while back had problems and could not acces this web site Thanks to all Following is my macro code Sub xy() ' xy Macro ' Macro recorded April 4, 2008 by Curtiss A. Greer ' ActiveDocument.MailMerge.EditMainDocument Selection.TypeParagraph Selection.InsertDateTime DateTimeFormat:="MMMM d, yyyy", InsertAsField:= _ True, DateLanguage:=wdEnglishUS, CalendarType:=wdCalendarWestern, _ InsertAsFullWidth:=False Selection.TypeParagraph ActiveDocument.MailMerge.OpenDataSource Name:="C:\Parade\Parade.xls", _ ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "DSN=Excel Files;DBQ=C:\Parade\Parade.xls;DriverId=790;MaxBuf ferSize=2048;PageTimeout=5;" _ , SQLStatement:="SELECT * FROM `Data$`", SQLStatement1:="" ActiveDocument.MailMerge.EditMainDocument ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _ "Contact_Person" Selection.TypeParagraph ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _ "Address" Selection.TypeParagraph ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:= _ "CityState" Selection.TypeParagraph ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range, Name:="Zip_" Selection.TypeParagraph ActiveDocument.MailMerge.DataSource.QueryString = _ "SELECT * FROM `Data$` WHERE ((`Contact Person` IS NOT NULL ) AND (` Address` IS NOT NULL ) AND (`City&State` IS NOT NULL ) AND (`Zip ` IS NOT NULL ) AND (`Amount` = 0))" _ & "" With ActiveDocument.MailMerge .Destination = wdSendToPrinter .MailAsAttachment = False .MailAddressFieldName = "" .MailSubject = "" .SuppressBlankLines = True With .DataSource .FirstRecord = wdDefaultFirstRecord 1 no luck here .LastRecord = wdDefaultLastRecord 50 End With .Execute Pause:=True End With CommandBars("Stop Recording").Visible = False End Sub |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search and replace merge codes? | Microsoft Word Help | |||
Mail merge and long tables | Mailmerge | |||
In mail merge from an excel source the number fields are too long | Mailmerge | |||
long mail merge list in the tools menu? | Mailmerge | |||
How do I merge info into a table (several pages long)? | Tables |