larryjfoster
New Member
- Joined
- Jul 19, 2017
- Messages
- 20
I have an excel workbook where the first sheet is a summary of all accounts and each sheet after is a summary per account. I'd like to automate a mail merge with existing templates already established for the merge. The code below successfully opens the appropriate Word template(s) but says that Word could not open the data source. I suspect the error lies in the (SQLStatement:="SELECT * FROM `'` & account & `$'`",) portion of the code but I'm not sure how to correct it.
Code:
*I'm building this code as I go and resolving issues along the way so you may see other inconsistencies that I have yet to consider but I can't get passed this issue so any help will be much appreciated.
Code:
Code:
Sub mbrMailMerge()
Dim Sheet As Worksheet, wsName$, N&
Dim dataSrc As String
Dim hDir As String 'main folder housing all templates
Dim account As String
dataSrc = ActiveWorkbook.FullName
hDir = "C:\folder\subFolder01"
'Open the mail merge letter template
Dim wdDoc As Object
For N = 2 To Sheets.Count
Sheets(N).Activate
wsName = ActiveSheet.Name
account = ActiveSheet.Name
If wsName = "account01" Then
Set wdDoc = GetObject(hDir & "subFolder02\subFolder03\account01.docx", "Word.document")
wdDoc.Application.Visible = True
ElseIf wsName = "account02" Then
Set wdDoc = GetObject(hDir & "subFolder02\subFolder03\account02docx", "Word.document")
wdDoc.Application.Visible = True
'ElseIf wsName = "account03 Then
'Set wdDoc = GetObject(hDir & "subFolder02\subFolder03\account03docx", "Word.document")
'wdDoc.Application.Visible = True
Else
MsgBox "Could not find " & wsName & " Member Word Doc for Mail Merge. Please complete manually"
'Go to next iteration of N in For loop
End If
' select datasource and complete mail merge
With wdDoc.MailMerge
.OpenDataSource Name:=dataSrc, _
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=dataSrc;Mode=Read;Extended Properties=""HDR=YES;IME", _
SQLStatement:="SELECT * FROM `'` & account & `$'`", _
SQLStatement1:="", SubType:=wdMergeSubTypeAccess
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
.Destination = wdSendToNewDocument
.MainDocumentType = wdNotAMergeDocument
End With
' show and save output file
' cleanup
wdDoc.Close SaveChanges:=False
Next
Set wdDoc = Nothing
End Sub
*I'm building this code as I go and resolving issues along the way so you may see other inconsistencies that I have yet to consider but I can't get passed this issue so any help will be much appreciated.
Last edited by a moderator: