How to use the current folder as the path for the Excel source for a mail merge

[ShogunPatch]

Greetings all
My team regularly has to prepare proposals for new work using a standard word template. Typically we include a list of similar jobs (an "Expertise List") previously undertaken, as an appendix to such proposals. We currently maintain an Access database which stores all information relating to past jobs and I have set this up so that I can run a query and have it save a selected list of jobs to an excel file. Separately I have setup a mail merge document which reads from the excel file and puts the references into a Word document in the specific layout and format that I need. So far, so good.

I now want to create a macro to automate the creation of the Expertise List in Word, using my mail merge document, which can then be pasted into the proposal. Through my usual process of recording macros and the tweaking the resultant code based on advice in various posts, etc, I have cobbled together the following code which seems to work... sort of.


Sub CreateExpertiseFromBlank()
'
' CreateExpertise Macro
' Uses mail merge template to create a formatted expertise list from an Excel source table into a blank doc
'
Documents.Open ("C:\Users\ShogunPatch\Dropbox\MyDesktop\ExpertiseList.docm")
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
Selection.WholeStory
Selection.Fields.Update
' Application.DisplayAlerts = True
End Sub


My problem is that my mail merge document uses a defined source which is currently a test excel sheet in a specific location (currently the same location as the mail merge document, my desktop). In practice I want the mail merge template to be stored in a permanent location on a shared drive, and users to create a bespoke source spreadsheet, exporting the specific list of jobs suitable for that particular proposal into a new sheet to be given a standard name ("ExpertiseSourceList.xlsx") and saved into a separate, proposal-specific sub-folder elsewhere on our shared drive. My thinking being that users would draft a bespoke proposal in Word then save it to that same proposal-specific sub-folder before running the macro to produce the mail merge. The mail merge could then default to using the Excel source document in that current folder (i.e. the proposal-specific sub-folder).

Does any of this make sense? If so, can anybody please help me tweak my code to achieve this?

Many thanks.

[SamT]

From My Word v 2003 help file

PATH
Remarks

The path doesn't include a trailing character — for example, "C:\MSOffice" or "http://MyServer". Use the PathSeparator (http://www.vbaexpress.com/forum/woproPathSeparator.htm) property to add the character that separates folders and drive letters. Use the Name (http://www.vbaexpress.com/forum/woproName.htm) property to return the file name without the path and use the FullName (http://www.vbaexpress.com/forum/woproFullName.htm) property to return the file name and the path together.
Note You can use the PathSeparator property to build Web addresses even though they contain forward slashes (/) and the PathSeparator property defaults to a backslash (\).
Example

This example displays the path and file name of the active document.


MsgBox ActiveDocument.Path & Application.PathSeparator & _
ActiveDocument.Name This example changes the current folder to the path of the template attached to the active document.


ChDir ActiveDocument.AttachedTemplate.Path This example displays the path of the first add-in in the AddIns collection.


If AddIns.Count >= 1 Then MsgBox AddIns(1).Path




*Code Formatting Tags added by me

[ShogunPatch]

Sam, this is all helpful stuff - thank you - and probably information I need although I hadn't realised it at the time of my original post.

Before I even get to the stage of worrying how to point to a folder and file the address of which will change from one instance to the next, however, I need to understand what is the syntax to define a new source file as part of the mail merge process. To be clear: in my test, my original mail merge document - currently and temporarily saved at "C:\Users\ShogunPatch\Dropbox\MyDesktop\ExpertiseList.docm" - is already setup with a sample source file ("SampleExperienceList.xlsx") also saved in the same location on my desktop, i.e. "C:\Users\ShogunPatch\Dropbox\MyDesktop\SampleExperienceList.xlsx". Under the scenario outlined in my original post, let's say I wanted to run the mail merge based on the same original word document, ExpertiseList.docm, but instead of using the original source file, I want to redefine it to an alternative file saved somewhere else, say for argument's sake in "C:\Users\ShogunPatch\Dropbox\Proposal A\SampleExperienceList.xlsx".

Is this possible, or is it not possible to do this starting with a mail merge document that already has a source file defined? If not, can I 'undefine' the source file in ExpertiseList.docm, or else what steps do I need to go through to achieve what I am trying to do (e.g. rather than open an existing mail merge document, maybe create a new one from scratch in VBA though that seems overly complicated)?

Many thanks.

[SamT]

I want to redefine it to an alternative file saved somewhere else,
Use one of Word's Builtin Dialogs: wdDialogFileFind or wdDialogFileOpen.

The algorithm is something like:
FileName = = "MyUsualFolder/FileName"

Choose = MsgBox "Press Enter to use Standard File"
If Choose = yes then Show Dialog

IF Dialog returns = Any File Name then FileName = Dialog Return
Show Dialog

[macropod]

See, for example:
http://www.msofficeforums.com/mail-merge/20461-cant-merge-mail-excel-2.html#post61768
https://social.msdn.microsoft.com/Forums/en-US/ab29d50e-db84-4534-81dd-6a4ae7531b3b/mail-merge-without-opening-doc?forum=worddev

[ShogunPatch]

Thank you to you both SamT and Macropod. Lots for me to try out there, so I will have a play about and revert back.