gruntingmonkey
Active Member
- Joined
- Mar 6, 2008
- Messages
- 444
- Office Version
- 365
- Platform
- Windows
Hello, I have spent all day on this and I just can't figure it out. I have an Excel macro which passes some variables to a word sub which works great. What I am then trying to do is pass a Boolean (FileExists) back to Excel but I just have no idea how. I have completely changed loads of bits in my code, I've tried to put it as a string, changed it to public sub, option explicit.... Nothing I do... i'm lost. Can anyone help? Code below:
This is the Excel code
And then heres the code in Word:
This is the Excel code
Code:
Public FileExists As String
Option Explicit
Sub CreateSelfFundedContract()
Dim wdApp As New Word.Application, wdDoc As Word.Document, wdPropDoc As Word.Document
Dim UserNm As String, ConTemp As String, AccID As String, Dt As String, Serv As String
Dim clName As String, SvNm As String, proposal As String, strDocNm As String
Dim SalesSig As String, Condate As String, Canname As String, FileExists As String, OpenWordDoc As String, filepath As String
'''Error Handle
On Error GoTo MyErrorHandler
UserNm = Environ$("Username")
'''This is the Contract master template
ConTemp = "C:\Users\" & UserNm & "\The Jess Consultancy\Sales - Documents\Contracts\~ Contract Wizard\Templates\Master Self Funder Contract.docm"
'''Creates a document from the template
Set wdDoc = wdApp.Documents.Add(ConTemp)
''''makes it so you can see the word file
With wdApp
.Visible = True
.Activate
End With
'''Variables to pass to Word
SalesSig = Worksheets("Self Funded Mail Merge").Cells(2, 5).Value
Condate = Format(Worksheets("Self Funded Contract Data Input").Cells(10, 4).Value, "yyyymmdd")
Canname = Worksheets("Self Funded Contract Data Input").Cells(8, 4).Value
FileExists=True
'''Run Word macro
wdApp.Application.Run "Module1.LinkMergeData", SalesSig, Condate, Canname ', FileExists
OpenWordDoc = wdApp.ActiveDocument.Name
wdApp.Quit SaveChanges:=wdDoNotSaveChanges
ThisWorkbook.Activate
FileExists = FileExists
If FileExists = False Then
'' updates utilities sheet
Sheets("Utilities").Cells(7, 2).Value = Application.UserName & " " & Now
Sheets("Utilities").Cells(7, 4).Value = "Complete"
End If
Sheets("Utilities").Activate
Set wdDoc = Nothing: Set wdApp = Nothing
Exit Sub
MyErrorHandler:
wdApp.Quit SaveChanges:=wdDoNotSaveChanges
MsgBox "Uh oh - It all went wrong!!! Let Jess know and she will sort it for you....probably....Please tell her the following issue:" & vbNewLine & vbNewLine & Err.Description
Set wdDoc = Nothing: Set wdApp = Nothing
End Sub
And then heres the code in Word:
Code:
Public FileExists As String
Option Explicit
Public Sub LinkMergeData(SalesSig As String, Condate As String, Canname As String, FileExists As String)
Dim UserNm As String, Conmas As String, Answer As String
'''masterfile quicklink
Conmas = "C:\Users\" & UserNm & "\The Jess Consultancy\Sales - Documents\Contracts\~ Contract Wizard\~ Contract Creator Master File.xlsm"
'''''add signature
Selection.GoTo What:=wdGoToBookmark, Name:="Signature"
With ActiveDocument.Bookmarks
.DefaultSorting = wdSortByName
.ShowHidden = False
End With
Selection.InlineShapes.AddPicture FileName:= _
"C:\Users\" & UserNm & "\The Jess Consultancy\Sales - Documents\Contracts\~ Contract Wizard\Authorisation Signatures\" & SalesSig & ".jpg" _
, LinkToFile:=False, SaveWithDocument:=True
'''Link the open contract file to the wizard merge data info
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:=Conmas _
, 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=ConMas;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database=""""" _
, SQLStatement:="SELECT * FROM `'Self Funded Mail Merge$'`", _
SQLStatement1:="", SubType:=wdMergeSubTypeAccess
' ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
'''Check to see if File exists already
Dim TestStr As String
TestStr = ""
On Error Resume Next
TestStr = Dir("C:\Users\" & UserNm & "\The Jess Consultancy\Sales - Documents\Contracts\~ Contract Wizard\Contracts\SF " & Canname & " " & Condate & ".docx")
On Error GoTo 0
If TestStr = "" Then
FileExists = False
Else
FileExists = True
End If
If FileExists = False Then
ActiveDocument.SaveAs2 FileName:= _
"C:\Users\" & UserNm & "\The Jess Consultancy\Sales - Documents\Contracts\~ Contract Wizard\Contracts\SF " & Canname & " " & Condate & ".docx" _
, FileFormat:=wdFormatXMLDocument, LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
:=False, SaveAsAOCELetter:=False, CompatibilityMode:=15
' ActiveWindow.Close
FileExists = True
Exit Sub
End If
If FileExists = True Then
Answer = MsgBox("This file already exists, do you want to overwrite it?", vbQuestion + vbYesNo, "Overwrite Message")
If Answer = vbYes Then
ActiveDocument.SaveAs2 FileName:= _
"C:\Users\" & UserNm & "\The Jess Consultancy\Sales - Documents\Contracts\~ Contract Wizard\Contracts\SF " & Canname & " " & Condate & ".docx" _
, FileFormat:=wdFormatXMLDocument, LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
:=False, SaveAsAOCELetter:=False, CompatibilityMode:=15
' ActiveWindow.Close
Exit Sub
End If
End If
Exit Sub
End Sub