Passing variables from Child (Word) to Parent (Excel)

[gruntingmonkey]

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

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:

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

 

[Norie]

You could change the code in Word to a function and have the function return the boolean value.

 

[gruntingmonkey]

Hi Norie, I'm not sure how to do that, can you point me in the right direction please?

 

[gruntingmonkey]

You could change the code in Word to a function and have the function return the boolean value.

Hi Norie, still struggling with this, can you point me in the right direction?

 

[sykes]

I see that you've duplicate this thread, but I'm responding to this original post - as per forum rules.

I can't get into detail with this, but, just to answer your question - as to what a Function is (as per Norie's reply)...

Functions all reside at the very top of a code module. They start "Public Function" and end "End Function".
The Functions are called by other procedures, which want a task performed.
A function normally contains at least one argument, which it then uses to perform a task, before it passes the result back to the original calling procedure.
Using one is what Norie suggested, and it would seem that it may well set you on the right path.

As a very basic example, I've written a function to multiply an integer by 5, and return the result to the calling procedure (which is what you're after, I think).
I've then written a simple calling procedure, asking the user to enter an integer- to be multiplied by 5. Their input is then sent to the function, which performs the calculation, and returns the result to the calling procedure, which then displays the result in a messagebox.

This goes at the very top of a code module:

Public Function example_multiply(intgr As Integer) As String

example_multiply = intgr * 5

End Function

This sub can go anywhere in the VBA project (normally into a code module, beneath any Functions etc):

Sub multiply()
Dim integ As Integer

integ = InputBox("Please enter an integer to be multiplied by 5.")
MsgBox (example_multiply(integ))
End Sub

Hopefully, this will get you on your way.

 

[Macropod]

You don't need your FileExists variable. Try the following. Note that I've deleted numerous unused/redundant variables:

Option Explicit

Sub CreateSelfFundedContract()
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim UserNm As String, ConTemp As String, SalesSig As String, Condate As String, Canname As String

'''Error Handle
On Error GoTo MyErrorHandler

UserNm = Environ$("Username")

'''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

'''This is the Contract master
ConTemp = "C:\Users\" & UserNm & "\The Jess Consultancy\Sales - Documents\Contracts\~ Contract Wizard\Templates\Master Self Funder Contract.docm"

If Dir(ConTemp) <> "" Then
  '''Creates a new document from the Contract master
  Set wdDoc = wdApp.Documents.Add(ConTemp)

  With wdApp
    ''' Prevent errors caused by someone saving the Contract master as a mailmerge main document
    .DisplayAlerts = wdAlertsNone
    ''''makes it so you can see the word file
    .Visible = True

    '''Run Word macro
    .Application.Run "Module1.LinkMergeData", SalesSig, Condate, Canname, UserNm
    
    '''Check for any new errors
    .DisplayAlerts = wdAlertsAll
    ''' Exit Word without saving changes to the Contract master
    .Quit SaveChanges:=wdDoNotSaveChanges
  End With
  Set wdDoc = Nothing: Set wdApp = Nothing
  
  '' updates utilities sheet
    With Sheets("Utilities")
      .Cells(7, 2).Value = Application.UserName & " " & Now
      .Cells(7, 4).Value = "Complete"
    End With
End If
ThisWorkbook.Sheets("Utilities").Activate
Exit Sub

MyErrorHandler:
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
End Sub

Option Explicit

Public Sub LinkMergeData(SalesSig As String, Condate As String, Canname As String, UserNm As String)
Dim UserNm As String, StrPath As String, MMSrc As String, MMOut As String
StrPath = "C:\Users\" & UserNm & "\The Jess Consultancy\Sales - Documents\Contracts\~ Contract Wizard\"
'''masterfile quicklink
MMSrc = StrPath & "~ Contract Creator Master File.xlsm"
MMOut = StrPath & "Contracts\SF " & Canname & " " & Condate & ".docx"

With ActiveDocument
  .InlineShapes.AddPicture FileName:=StrPath & "Authorisation Signatures\" & SalesSig & ".jpg", Range:=.Bookmarks("Signature").Range
        
  '''Link the open contract file to the wizard merge data info
  With .MailMerge
    .MainDocumentType = wdFormLetters
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    .OpenDataSource Name:=MMSrc, ConfirmConversions:=False, ReadOnly:=True, LinkToSource:=True, _
      AddToRecentFiles:=False, Format:=wdOpenFormatAuto, Connection:= _
      "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=MMSrc;" & _
      "Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database=""""", _
      SQLStatement:="SELECT * FROM `'Self Funded Mail Merge$'`", SQLStatement1:="", SubType:=wdMergeSubTypeAccess

    With .DataSource
      .FirstRecord = wdDefaultFirstRecord
      .LastRecord = wdDefaultLastRecord
    End With
    .Execute Pause:=False
  End With
     

'''Check to see if File exists already
If Dir(MMOut) <> "" Then
  If MsgBox("This file already exists:" & vbCr & MMOut & vbCr & "Overwrite it?", vbQuestion + vbYesNo, "Overwrite Query") = vbNo Then Exit Sub
End If
ActiveDocument.SaveAs2 FileName:=MMOut, FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=True, CompatibilityMode:=15
End Sub

Two further observations:
1. Your own code comments refer to your Contract master as a template. It isn't - it's merely a macro-enabled document.
2. The entire process could be run from Excel, without needing to invoke a separate macro in Word.

 

[gruntingmonkey]

Thanks for this Macropod but it doesnt seem to create any files at the end. And I still dont know how to pass the boolean/variable back to Excel.

 

[Macropod]

Code corrected - see final few lines

 

[gruntingmonkey]

Excellent, that works now. However.... It doesnt bring up the VB question to the front of the screen if its already created. How do I do this?

And also, how can I tell what the answer is to that question in Excel?

 

[gruntingmonkey]

And another question, I'm sorry but this happens when you find someone helpful!!!

How would I have all the code in Excel? This would negate my need to pass a variable between applications. If its going to take you ages - dont worry about it. I was just thinking about future use.