asked on

debugging error message 5174

hi ive got some code that MrBullWinkle and GrahamSkan helped me with. Ive put it below. It opens up a word doc from an excel workbook in Mailings on step 5. It does that successfully but when i return to excel there is a Run-Time error 5174. This file could not be found: C:\...\peter's eagle\MailMerge7.txt).
id like to resolve the debugging (naming issue)
ive done what i can to understand the reason for the bug and i think that the route isnt named correctly (but i dont know) ive messed about trying this line of code:
DocPath = "C:\Documents and Settings\peter\My Documents\Mailings\Yr 7 English Report.doc" but it doesnt seem to help or hinder.
the code in yellow to debug is:
.OpenDataSource Name:=ThisWorkbook.Path + "\C:\peter's eagle\MailMerge7.txt", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="", SQLStatement1:=""
and because of the error message im thinking the first line of the above code needs addressing (but i dont know) help on this would be appreciated. pete

Private Sub CommandButton4_Click()
        
    PlayFile "c:\windows\media\Chimes.wav"
    
    Dim WordObj As Object
    Dim myPath As String
    'Start Microsoft Word.
    myPath = ThisWorkbook.Path
    myPath = myPath + "\" + "Yr 7 English Report.doc"
    DocPath = "C:\Documents and Settings\peter\My Documents\Mailings\Yr 7 English Report.doc"
   
    'Start Microsoft Word.
    Set WordObj = GetObject(myPath)
    If Err.Number <> 0 Then ' Word not running
        Set WordObj = CreateObject("Word.Application")
    End If
    WordObj.Application.Visible = True
    WordObj.Application.Documents("Yr 7 English Report.doc").Activate
    
    With WordObj.Application.Documents("Yr 7 English Report.doc").MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource Name:=ThisWorkbook.Path + "\C:\peter's eagle\MailMerge7.txt", _
         ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
         AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
         WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
         Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="", SQLStatement1:=""
         .ViewMailMergeFieldCodes = wdToggle
    End With
    
   
End Sub

Open in new window

Iced-evil

Hi,

could you tell me where the MailMerge7.txt file is located?

Iced-evil

If you don't have a mailmerge7.txt file and you get the mailmerge data from your excel file you will need to modify this code
Name:=ThisWorkbook.Path + "\C:\peter's eagle\MailMerge7.txt",_
to
Name:=ThisWorkbook.FullName,_

pete47

ASKER

hi Iced Evil: oh yeah, i see what you mean, but should the "full name" be something already declared? or something like
Name:=ThisWorkbook/EagleEerie(workbooks name)/Merge7(datasourceworksheetfor merge)? anything else

ASKER CERTIFIED SOLUTION

Iced-evil

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pete47

ASKER

thanx for the comeback Iced evil but whilst your code removed the debugging it also removed the fire to mailings step 5. ive placed some code below with an attempted change that you suggested. It removed the 5174 error but replaced it with error 13 Type Mismatch. so im hoping theres one more step of making two statements match re mege source...i cant find it...all spellings of files and sheets are right...hope you can help pete
ps this seems to be the source of the error.
.OpenDataSource Name:=ThisWorkbook.Path = "\C:\Excel\Copy of Eagle Eerie\MailMerge7", _

Private Sub CommandButton4_Click()
        
    PlayFile "c:\windows\media\Chimes.wav"
    
    Dim WordObj As Object
    Dim myPath As String
    'Start Microsoft Word.
    myPath = ThisWorkbook.Path
    myPath = myPath + "\" + "Yr 7 English Report.doc"
   
   
    'Start Microsoft Word.
    Set WordObj = GetObject(myPath)
    If Err.Number <> 0 Then ' Word not running
        Set WordObj = CreateObject("Word.Application")
    End If
    WordObj.Application.Visible = True
    WordObj.Application.Documents("Yr 7 English Report.doc").Activate
    
    With WordObj.Application.Documents("Yr 7 English Report.doc").MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource Name:=ThisWorkbook.Path = "\C:\Excel\Copy of Eagle Eerie\MailMerge7", _
         ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
         AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
         WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
         Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="", SQLStatement1:=""
         .ViewMailMergeFieldCodes = wdToggle
    End With
    
   
End Sub

Open in new window

pete47

ASKER

hi Evil ice, ive just realised your code works perfectly. what confused was that your code also brought up the mailmerge option box (if thats the right word to use) so i thought i had to select a data choice. sorry. brain dead. is there any way to modify the code so that the box doesnt appear? amyway, humble thanx pete

Iced-evil

Hi,

I believe you will need to the the .execute command to the MailMerge object. (as below)
The code below actually can contain all the settings for the mailmerge. It all just depends on what you want to be in there and what exactly you want this to do.

With WordObj.Application.Documents("Yr 7 English Report.doc").MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=ThisWorkbook.Path + "\C:\peter's eagle\MailMerge7.txt", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="", SQLStatement1:=""
.ViewMailMergeFieldCodes = wdToggle
.execute
End With