Results 1 to 2 of 2

Thread: Passing variables from Child (Word) to Parent (Excel)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2008
    Location
    Berkshire, England
    Posts
    372
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    Hello, I have spent all last week 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?
    I got told to make the word code a function but I have no idea how I do that! and then pass a variable back to Excel.

    Code below:

    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
    Last edited by gruntingmonkey; Jun 18th, 2019 at 05:13 AM.
    0 0
     

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    24,006
    Post Thanks / Like
    Mentioned
    408 Post(s)
    Tagged
    43 Thread(s)

    Default Re: Passing variables from Child (Word) to Parent (Excel)

    Duplicate https://www.mrexcel.com/forum/excel-...ml#post5293674

    Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
    Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10
    0 0
     

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •