Solved: Word 2000 merge problem

[tonyam]

Hi all,

Am wondering whether i can set up a merge with an open excel document in word 2000. Have set up a macro to do this but always tries to open the same version of my excel document causing it to fail.

Any idea how i might alter the code to make this work?

Sub merge()
'
' merge Macro
' Macro recorded 17/10/2008 by AA74701/BST/P&A
'
CommandBars("Mail Merge").Visible = False
CommandBars("Mail Merge").Visible = True
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:="C:\D & N excel 2000.xls", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"DSN=Excel Files;DBQ=C:\D & N excel 2000.xls;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeOther
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0
ActiveWindow.Close
ActiveWindow.Close
Application.Quit
End Sub

Thanks

[macropod]

Hi tonyam,

The reason you keep getting the same source file is that it's name had been hardcoded into your macro. Try something along the lines of:
Sub Merge()
Dim SourceFile As String
SourceFile = InputBox("Pease input the sourcefile path & name", , "C:\D & N excel 2000.xls")
CommandBars("Mail Merge").Visible = True
With ActiveDocument.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=SourceFile, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"DSN=Excel Files;DBQ=SourceFile;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:=wdMergeSubTypeOther
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0
ActiveWindow.Close
Application.Quit
End SubNote that I only need to make minor changes to your code.

[tonyam]

Thanks for your reply.

It works fine on my machine, running excel 2003, but not on a 2000 machine, even changed the coding on the 2000 machine to make sure the code would potentially work. Keeps breaking at the sub type = wdmergetype part.

Any ideas?

[macropod]

Hi tonyam,

It hadn't occurred to me to test your code, since you hadn't mentioned anything other than the need to be able to select a different file. If you take a look at the OpenDataSource Method in Word 2000's help file, you'll see that SubType is not listed amongst the parameters (ie it wasn't introduced until Word 2002/2003). Try deleting that parameter and see how you go.