Page 1 of 2 12 LastLast
Results 1 to 10 of 16

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

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

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,043
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    6 Thread(s)

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

    You could change the code in Word to a function and have the function return the boolean value.
    If posting code please use code tags.

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

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

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

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

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

    Quote Originally Posted by Norie View Post
    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?

  5. #5
    Board Regular sykes's Avatar
    Join Date
    May 2002
    Location
    Cornwall,England
    Posts
    1,490
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    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:
    Code:
    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):
    Code:
    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.
    Sykes
    Windows 10 / XL 2016

  6. #6
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,274
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

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

    You don't need your FileExists variable. Try the following. Note that I've deleted numerous unused/redundant variables:
    Code:
    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
    Code:
    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.
    Last edited by Macropod; Jun 19th, 2019 at 09:19 AM.
    Cheers
    Paul Edstein
    [MS MVP - Word]

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

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

    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.

  8. #8
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    3,274
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

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

    Code corrected - see final few lines
    Cheers
    Paul Edstein
    [MS MVP - Word]

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

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

    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?
    Last edited by gruntingmonkey; Jun 19th, 2019 at 09:38 AM.

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

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

    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.

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
  •