Change Word mailmerge source with VBA?

[Gasman]

I have no difficulty at all copying & pasting such code into the VBE. There is certainly no compiler complaint about them (or about Apostrophe characters if I'd used them - but they wouldn't work for the SQL).

You can copy/oaste them from here, or input them via Alt 096 from the keyboard.

I have no idea what you mean by that. In my experience they work as intended. Perhaps you're just unfamiliar with mailmerge SQL.

No, I mean when I substituted them for the single quote, I got that table screen and F:\Temp.xls as the source?

All is working as I would like it to now. Just have to save as pdf, and then the controller just needs to issue around 20 clicks to get the output he needs, and the odd one now and again when it gets updated.

Thanks again.

[macropod]

I mean when I substituted them for the single quote, I got that table screen and F:\Temp.xls as the source?

That happened because the SQL couldn't find the datasource specified by your:

StrMMSrc = "F:\Temp\Address Trial,xlsm"

Note your use of a comma instead of a period before 'xlsm' in post #2...

Just have to save as pdf

Code for saving added to post #3.

[Gasman]

That happened because the SQL couldn't find the datasource specified by your:

StrMMSrc = "F:\Temp\Address Trial,xlsm"

Note your use of a comma instead of a period before 'xlsm' in post #2....

Yes, I spotted that a while back and just use the current workbook name as you did. In fact I mentioned that error in another forum.
However, humour me and please try it for yourself, as even with the current code, I get the table prompt with the incorrect workbook name. I just tried it again, but if I am still making some silly mistake, I would love to know what it is.

Code for saving added to post #3.

Sorry, missed that completely.

Current code.

Sub RunMerge()
' Sourced from: http://www.vbaexpress.com/forum/showthread.php?70461-Change-Word-mailmerge-source-with-VBA
' Note: this code requires a reference to the Word object model to be set, via Tools|References in the VBE.
Application.ScreenUpdating = False
Dim StrMMSrc As String, StrMMDoc As String, StrMMPath As String, StrName As String, strPDFName As String
Dim iLastRow As Integer


StrMMSrc = ThisWorkbook.FullName
StrMMPath = ThisWorkbook.Path & "\"
StrMMDoc = "F:\Users\Paul\Documents\Test Address Details 7165 MM.docx"
StrName = ActiveSheet.Name 'InputBox("Please input the name of the source worksheet")


If Trim(StrName) = "" Then Exit Sub
'Trim Filter column else we get extra records with no values
iLastRow = GetLastRow(StrName, "A") + 1
ActiveSheet.Range("A" & iLastRow & ":J1000").Delete


Dim wdApp As New Word.Application, wdDoc As Word.Document
wdApp.Visible = True
wdApp.WordBasic.DisableAutoMacros
wdApp.DisplayAlerts = wdAlertsNone
'StrMMDoc = StrMMPath & "MailMergeMainDocument.doc"
Set wdDoc = wdApp.Documents.Open(Filename:=StrMMDoc, AddToRecentFiles:=False, ReadOnly:=True, Visible:=False)
With wdDoc
  With .MailMerge
    .MainDocumentType = wdMailingLabels
    .OpenDataSource Name:=StrMMSrc, ReadOnly:=True, AddToRecentFiles:=False, _
      LinkToSource:=False, Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
      "Data Source=StrMMSrc;Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
      SQLStatement:="SELECT * FROM `" & StrName & "$`"
    .Execute Pause:=False
    .MainDocumentType = wdNotAMergeDocument
  End With
'  .Close SaveChanges:=False
'Save as PDF file
strPDFName = "GCCS Passengers - " & StrName
With wdApp.ActiveDocument
    .SaveAs Filename:=StrMMPath & strPDFName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
    '.Close SaveChanges:=False
End With


'wdApp.Documents("Labels1").ExportAsFixedFormat OutputFileName:= _
'    StrMMPath & strPDFName & ".pdf", _
'    ExportFormat:=wdExportFormatPDF, _
'    OpenAfterExport:=True, _
'    OptimizeFor:=wdExportOptimizeForPrint, _
'    Range:=wdExportAllDocument, _
'    IncludeDocProps:=True, _
'    CreateBookmarks:=wdExportCreateWordBookmarks, _
'    BitmapMissingFonts:=True


End With


wdApp.DisplayAlerts = wdAlertsAll
MsgBox "Mailmerge document created. Switching to Word application, document Labels1"
wdApp.Activate


Set wdDoc = Nothing
Set wdApp = Nothing
Application.ScreenUpdating = True


End Sub

[macropod]

Humour me and please try it for yourself, as even with the current code, I get the table prompt with the incorrect workbook name. I just tried it again, but if I am still making some silly mistake, I would love to know what it is.

If you mis-type the worksheet name, for example, you'll get the prompt.