CODEWITHSUNDEEP
powershellmailmergemethod-parameters
Gero Krüger
Gero Krüger

Reputation: 21

Call MailMerge.OpenDataSource() Set Connection

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()

Upvotes: 2

Views: 1996

Answers (2)

user27032023
user27032023

Reputation: 1

This variant works for me:

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

Upvotes: 0

user1379931
user1379931

Reputation:

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.

Upvotes: 1

Related Questions