merge search to long

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Curt - 11 Apr 2008 06:07 GMT

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;MaxBufferSize=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 to this Message

Doug Robbins - Word MVP - 11 Apr 2008 09:29 GMT

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.

Signature

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

> Recorded a macro in word for merge. Problem is when it searches excel data
> it
[quoted text clipped - 63 lines]
> CommandBars("Stop Recording").Visible = False
> End Sub

Reply to this Message

nuno_fbo - 17 Jun 2008 23:46 GMT

Hi,

PS: Look into this close situation:
http://www.experts-exchange.com/Programming/Misc/Q_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 here:

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.wdOpenFormatText;
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.WdMailMergeDestination.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.WdMailMergeDefaultRecord.wdDefaultFirstRecord;
docWord.MailMerge.DataSource.LastRecord =
(int)Microsoft.Office.Interop.Word.WdMailMergeDefaultRecord.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

> 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.
[quoted text clipped - 80 lines]
> > CommandBars("Stop Recording").Visible = False
> > End Sub

Reply to this Message