Mailmerge data source is in %homepath% subfolder

In the Select Data Source dialog, click in the address bar at the top and enter the folder path (%homepath%\TMP). Click the file type dropdown to the right of the file name box, and select either Web Pages (to limit the file list to .html and similar extensions) or All Files. Click on Tmp.html and click the Open button.

You can't just type the full file path into the file name box when using an environment variable such as %homepath%; if you do that, you just get a message that the path is invalid.

Ah! Thank you! You correctly guessed what I had been doing...attempting to type it in the filename box.

However, I marked it helpful instead of answer, because...my point of needing to do this is to be able to save the environment variable with the form file, so that it works regardless of the machine that it is on:

In copying this form file and data.html file to another workstation that uses a different login/user name, the data source doesn't read properly. Unfortunately, it does not understand the homepath of the new workstation...when I open the form file, it does not automatically find the data source. So I guess maybe it is "hard coding" the user name in the form file instead of saving it with "%homepath%"?  How can I make the form file understand the homepath, regardless of the user name?

It's true that Word doesn't understand environment variables such as %homepath%, nor is there any way to convince it to accept them.

The full path of the data source is indeed hard-coded in the document file -- if you change the extension of a .docx file to .zip and extract its contents, you'll find the path in two places in the word\settings.xml file. However, it must also be stored somewhere else, because changing the value in the settings.xml file and re-zipping the document doesn't change the place where Word looks for the data source. As for using a macro to make the change, in VBA the document's MailMerge.DataSource property is read-only, so trying to change the path there won't work.

This thread implies that the only reliable way to change the path to the data source is to use Word to remove the original source (click Mailings > Start Mail Merge > Normal Word Document) and then select the new source.

Here you go.

With ActiveDocument.MailMerge
    If .MainDocumentType <> wdNotAMergeDocument Then
        .OpenDataSource "C:" & Environ("homepath") & "\TMP\Tmp.html"
    End If
End With

Thinking about this now, using:

MsgBox Mid(Environ("homepath"), 8)

is a much easier way to get the name of the logged on user than that in the article "How to get the username of the current user” at:

http://www.word.mvps.org/FAQs/MacrosVBA/GetCurUserName.htm

Awesome! I had to remove the "c:" when I switched the environment to appdata, and I'm on the way, but hitting a slight snag...

the data source is no longer an html file, its an mdb file, thus, I changed your line to this:

 .OpenDataSource Environ("APPDATA") & "\TMP\Tmp.MDB"

I follow it with:

        .Destination = 0
        .Execute
    End With
    wd.Windows(Mrgform).Activate

    wd.Visible = True

HOWEVER, the doc is in an invisible state. I can click on Mailings, Edit Recipient List, and I can see the tmp.mdb data. I click Finish and Merge, it does, but stays invisible. I can save it, close word, and reopen word, then see the finished product, all correct, with correct data merged in.

I have stepped it through by putting msgboxes after every line, and it's not getting past the .opendatasource line.

Tmp.mdb has more than one table. I don't know if that is the culprit. I looked for a way to reference the exact table name and couldn't find anything. But again, when I manually finish the task, I can see it has populated the finished document with the data from the correct table.

The command:

.OpenDataSource Environ("APPDATA") & "\TMP\Tmp.MDB"

needs to be augmented to:

.OpenDataSource Environ("APPDATA") & "\TMP\Tmp.MDB", Connection:="TABLE [tablename]"

where you replace [tablename] with the name of the table that contains the data.

Harumpfh! Thank you, but no change with your added Connection statement. Maybe it will help if I show my surrounding code:

Set wdActiveDoc = wd.Documents.Open(TmpPath & Mrgform, False, False, _
                        False, "", "", False, "", "", 0)

 'change next 2 lines back to false when opendatasource is worked out
                       wd.Visible = True
                       wd.Application.ScreenUpdating = True

    'Doug: It gets to this prompt:
    MsgBox "getting ready to OPEN DATA SOURCE"

    With wdActiveDoc.MailMerge
        .OpenDataSource Environ("APPDATA") & "\TMP\Tmp.MDB", Connection:="TABLE [MergeData]"

'Doug: It doesn't get to this next prompt:

     MsgBox "OPEN DATA SOURCE"

'Doug: At this point, I switch to Word and see the form file, and can View the recipient list and see

'that the recipient list is correct, but its just sitting there

        .Destination = 0
        .Execute
    End With
    wd.Windows(Mrgform).Activate