Originally Posted by
Frosty
Your replaced the appWord variable... But then left Word.Documents.Add.
Use appWord instead, and also dim a new variable... A document variable.
Dim oDoc As Wird.Document.
Set oDoc = appWord.Documents.Add (Template:= yada yada)
then use that document object variable to run the mail merge. You'll probably need to pass as a parameter. I can't easily give you your code edited back at the moment, since I'm still typing on a phone.
Means yes, we're very helpful-- to someone with a great attitude, like you've brought. So thank you!
Frosty, I've made those changes but it still just opens the word document, and then does nothing. My code is now as follows:
'encapsulates getting the word application, using GetObject first, and Create obectPublic Function fGetApp(Optional bCreated As Boolean) As Object
Dim oRet As Object
'attempt to get it, ignoring any error if it isn't launched
On Error Resume Next
Set oRet = GetObject(, "Word.Application")
'if we didn't get it, then attempt to create it, but reset error trapping
On Error GoTo 0
If oRet Is Nothing Then
Set oRet = CreateObject("Word.Application")
bCreated = True
End If
Set fGetApp = oRet
End Function
Sub MailMerge()
Dim appWord As Word.Application
MSG1 = MsgBox("Do you want to continue with the mail merge?", vbYesNo, "Confirm")
If MSG1 = vbYes Then
ElseIf MSG1 = vbNo Then
Exit Sub
End If
'Ensures workbook saved
ThisWorkbook.Save
'Create new quotation for template
Set appWord = fGetApp
appWord.Documents.Add Template:="Q:\AirMaster\AirMaster Quotation1.dotm", NewTemplate:=True, DocumentType:=0
appWord.Visible = True
With appWord
.Visible = True
.ActiveDocument.SaveAs2 Filename:="Prod Quote_xxAMxHRV_ProjName " & Format(Date, "ddmmyyyy") & ".docx"
End With
Application.ScreenUpdating = False
Application.CutCopyMode = False
Application.DisplayAlerts = True
End Sub
I don't understand what you mean about passing a parameter...
Also, do I need to dump this code?
Sub MailMergeFromExcel()
Dim sConnection As String
Dim strSourcePath As String
strSourcePath = fGetFilePath
If strSourcePath = "" Then
Exit Sub
End If
'your connection string, also more easily separated with line breaks and arguments
sConnection = "Provider=Microsoft.ACE.OLEDB.14.0;" & _
"User ID=Admin;" & _
"Data Source=" & strSourcePath & ";" & _
"Mode=Read;" & _
"Extended Properties=""HDR=YES;IMEX=1;"";" & _
"Jet OLEDB:System database="""";" & _
"Jet OLEDB:Registry Path="""";" & _
"Jet OLEDB:Engine Type=35;" & _
"Jet OLEDB:"
'using your conection string... with the parameter names and the passed values separated nicely
ActiveDocument.MailMerge.OpenDataSource _
Name:=strSourcePath, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
SQLStatement:="SELECT * FROM `MailMerge`", _
Connection:=sConnection, _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:=""
End Sub
Originally Posted by
SamT
What is this supposed to be doing?
If .Show <> 0 Then
SelectedItems(1) = SourceDoc
because it looks like you are trying to set the filepicker output (read only) to be the same as an uninitialized variable (Value = "").
If .Show <> 0 Then
SourceDoc = SelectedItems(1)
Hi Sam, thanks but that code isn't in use any more. You are right though...I didn't quite understand what was going on with that code at the time.
Originally Posted by
Aussiebear
The internet make the earth a very small world particularly when people join multiple forums to gain a wider experience in subject such as Excel and VBA etc. Please take the time to read the link provided by Paul.
I did read the link, thank you. As I said before, I'd requested that the post on that forum be removed before I even joined this one.