Open Excel document from same file path using Word

[lance01]

I have a Word document that Opens an Excel document for Mail Merge but the directory and path changes for different people it may be X:\File or Q:\File for e.g

The recorded macro I have calls a document called TEMPDATADUMP.xlsm

Is there a code I can place before the text below highlighted in red and bold to ensure where ever the word file is opened it can open the excel file there e.g. file.path\tempdatadump.xlsm


I don't wish to remap their network drives.

Sub MailMergeMacro()




ActiveWindow.View.Type = wdPrintView
ActiveDocument.MailMerge.MainDocumentType = wdCatalog
ActiveDocument.MailMerge.OpenDataSource Name:= _
"E:\MASTER\TEMPDATADUMP.xlsm" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=E:\MASTER\TEMPDATADUMP.xlsm;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path=""""" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
ActiveDocument.MailMerge.DataSource.ActiveRecord = wdFirstRecord
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
.LastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
End With
.Execute Pause:=False
End With




End Sub



Help anyone?

Thanks!

 

[Norie]

Same directory of the Word document?

If it is then you can use the document's Path property to get, well, the path.

 

[lance01]

Same directory of the Word document?

If it is then you can use the document's Path property to get, well, the path.

Do you have a working example code?

 

[djreiswig]

Try this

Sub MailMergeMacro()
    ActiveWindow.View.Type = wdPrintView
    With ActiveDocument
        .MailMerge.MainDocumentType = wdCatalog
        .MailMerge.OpenDataSource Name:= _
        .Path & "\TEMPDATADUMP.xlsm" _
        , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & .Path & "\TEMPDATADUMP.xlsm &;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path=""""" _
        , SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
        
        With .MailMerge
            .ViewMailMergeFieldCodes = wdToggle
            .DataSource.ActiveRecord = wdFirstRecord
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
                .LastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
            End With
            .Execute Pause:=False
        End With
    End With
End Sub

 

[lance01]

Try this

Sub MailMergeMacro()
    ActiveWindow.View.Type = wdPrintView
    With ActiveDocument
        .MailMerge.MainDocumentType = wdCatalog
        .MailMerge.OpenDataSource Name:= _
        .Path & "\TEMPDATADUMP.xlsm" _
        , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & .Path & "\TEMPDATADUMP.xlsm &;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path=""""" _
        , SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
        
        With .MailMerge
            .ViewMailMergeFieldCodes = wdToggle
            .DataSource.ActiveRecord = wdFirstRecord
            .Destination = wdSendToNewDocument
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
                .LastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
            End With
            .Execute Pause:=False
        End With
    End With
End Sub

Worked perfectly, Thanks a million!

 

[djreiswig]

Worked perfectly, Thanks a million!

No Problem. Glad to help.