Call MailMerge.OpenDataSource() Set Connection

Question

I want to open a Word file and connect it to the 3rd table in an Excel document as data source for mailing. My main problem: how can I set the "Table3" to avoid opening this dialog box?

Here my trials (all as comments behind #):

$Word = New-Object -Com Word.Application
$Word.Visible = 'TRUE'
$Doc = $Word.Documents.Open( "D:\GoogleDrive\_POWERSHELL\Template.doc" )
$Doc.Activate()

$DataPath = "D:\GoogleDrive\_POWERSHELL\DataSource.xls"
$DataTable = "Table3"
$default = [Type]::Missing

#1 $Doc.MailMerge.OpenDataSource( "D:\GoogleDrive\_POWERSHELL    \DataSource.xls" ) #this works fine but opens a box to choose the table

#2 $Doc.MailMerge.OpenDataSource( $DataPath ) #this works fine too but opens a box to choose the table

#3 $Doc.MailMerge.OpenDataSource( Name:="D:\GoogleDrive\_POWERSHELL\DataSource.xls", Connection:="Table3" ) #SYNTAX ERROR: Fehlende ")" im Methodenaufruf / Missing ")" in Method Call

#4 $Doc.MailMerge.OpenDataSource -Name "D:\GoogleDrive\_POWERSHELL\DataSource.xls" -Connection "Table3" #SYNTAX ERROR: Sie müssen auf der rechten Seite des Operators "-" einen Wertausdruck angeben.

#5 $Doc.MailMerge.OpenDataSource( "D:\GoogleDrive\_POWERSHELL\DataSource.xls", 'TRUE', 'TRUE', 'TRUE', 'TRUE', '', '', 'FALSE', '', '', '',"'Table3'", '', '', '', '' )

$Doc.MailMerge.OpenDataSource( "D:\GoogleDrive\_POWERSHELL\DataSource.xls", $default, $default, $default, $default, $default,  $default,  $default,  $default,  $default,  $default, "Table3",  $default,  $default,  $default,  $wdMergeSubTypeWord2000 )

#7 $Doc.MailMerge.OpenDataSource( $DataPath, $default, $default, $default, $default, $default,  $default,  $default,  $default,  $default,  $default, $DataTable,  $default,  $default,  $default,  $default )

Write-Host $Doc.MailMerge.DataSource.Name
#$Doc.MailMerge.Execute()
#$Doc.MailMerge.Destination = "D:\GoogleDrive\_POWERSHELL\MailMergeTest.doc"
Start-Sleep 2 #Pause von 2 Sekunden
$Doc.Close()
$Word.Quit()

#6 (active) seems to come near, but opens the box for choosing the table anyway.
#3 and #4 - why these syntax errors? what's the correct syntax?

#3 doesn't work, because PowerShell doesn't support named parameters (`param:=value`). #4 doesn't work, because `OpenDataSource()` is a method, not a PowerShell function, so parameters must be passed as a list of values in parentheses (`.OpenDataSource(v1, v2, ...)`). — Ansgar Wiechers, Jan 24 '15 at 09:39
In VB it seems to be possible - thank you anyway. Remains the question why #6 (active) and #7 don't work (Box opens, Table choice hasn't worked). — Gero Krüger, Jan 24 '15 at 19:52

score 1 · Answer 1 · answered Feb 02 '15 at 18:40

Recent versions of Word do not care about the connection string that you provide for Excel files - if you need a special string, you have to use a .odc file (or ODBC).

What you need is to find the SQLStatement parameter and set it to the correct SQL - e.g.

"SELECT * FROM [Table3$]"

But the precise syntax depends on what exactly Table3 is. If it is a worksheet, called "Table3", you will need

"SELECT * FROM [Table3$]"

If it is a named range, you will need

"SELECT * FROM [Table3]"

If it is a named Table, I would have to check whether it works at all.

If it isn't a name but really the third sheet in the Excel workbook, or the 3rd "something else" in the workbook, then I think you will first have to automate Excel to find the name of the object you want to use.

score 0 · Answer 2 · edited Mar 27 '23 at 13:53

0

This variant works for me:

$Doc.MailMerge.OpenDataSource( $DataPath,$default,$default,$default, $default, $default,$default,$default,$default,$default,$default,$default, "SELECT * FROM [Sheet1$]" )

edited Mar 27 '23 at 13:53

Adriaan

17,741
7
42
75

answered Mar 27 '23 at 13:50

user27032023

1

Welcome to Stack Overflow! Please read [answer] and [edit] your answer to contain an explanation as to why this code would actually solve the problem at hand. Always remember that you're not only solving the problem, but are also educating the OP and any future readers of this post. – Adriaan Mar 27 '23 at 13:53

Call MailMerge.OpenDataSource() Set Connection

2 Answers2

Linked