C
Curt
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
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