Excel VBA邮件合并：替换SQL语句中固定表名为活动工作表名

你需要把SQL语句里的固定工作表名Certificates$替换为已获取的sheetname变量，同时还要修正Connection字符串里的一个小错误（直接写了变量名而非变量值），具体修改如下：

关键修改点 #

• 动态生成SQL语句：将SQLStatement:="SELECT * FROM Certificates$"改为SQLStatement:="SELECT * FROM " & sheetname & "$"，通过字符串拼接把当前活动工作表名插入到SQL语句中。
• 修正Connection字符串的数据源引用：原代码中"Data Source=strWorkbookName;"是直接把变量名作为字符串内容，需要改为"Data Source=" & strWorkbookName & ";"，让VBA读取变量的实际路径值。

修正后的完整代码 #

Sub DoMailMerge()
'Note: A VBA Reference to the Word Object Model is required, via Tools|References
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim strWorkbookName As String: strWorkbookName = ThisWorkbook.FullName
    Dim r As Range
    Dim nLastRow As Long, nLastColumn As Long
    Dim nFirstRow As Long, nFirstColumn As Long
    Set r = Selection
    nLastRow = r.Rows.Count + r.Row - 2
    nFirstRow = r.Row - 1
    Dim WFile As String
    WFile = Range("A2").Value
    Dim sheetname As String
    sheetname = ActiveSheet.Name
    
With wdApp
  'Disable alerts to prevent an SQL prompt
  .DisplayAlerts = wdAlertsNone
  'Open the mailmerge main document
  Set wdDoc = .Documents.Open("C:\Users\Todd\Desktop\" & WFile, _
    ConfirmConversions:=False, ReadOnly:=True, AddToRecentfiles:=False)
  With wdDoc
    With .MailMerge
      'Define the mailmerge type
      .MainDocumentType = wdFormLetters
      'Define the output
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      'Connect to the data source
      .OpenDataSource Name:=strWorkbookName, ReadOnly:=True, _
        LinkToSource:=False, AddToRecentfiles:=False, _
        Format:=wdOpenFormatAuto, _
        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "User ID=Admin;Data Source=" & strWorkbookName & ";" & _
        "Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";" , _
        SQLStatement:="SELECT * FROM `" & sheetname & "$`", _
        SubType:=wdMergeSubTypeAccess
      With .DataSource
        .FirstRecord = nFirstRow
        .LastRecord = nLastRow
      End With
      'Execute the merge
      .Execute
      'Disconnect from the data source
      .MainDocumentType = wdNotAMergeDocument
    End With
    'Close the mailmerge main document
    .Close False
  End With
  'Restore the Word alerts
  .DisplayAlerts = wdAlertsAll
  'Display Word and the document
  .Visible = True
End With
End Sub

说明：修改后宏会自动读取当前活动工作表的名称，替换到SQL查询语句中，实现基于当前工作表数据的邮件合并；同时修正了Connection字符串中数据源路径的错误引用，确保能正确连接到当前工作簿。

内容的提问来源于stack exchange，提问作者Todd Harris