Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

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

  1. #11
    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)

    I have answered the first part of this by using Application.Activate if the msgbox appears at all.

    Still havent worked out how to tell from Excel what the result of the Yes/No question is.

  2. #12
    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)

    Quote Originally Posted by gruntingmonkey View Post
    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?
    The 'If Dir(MMOut) <> "" Then' test should find the file if it exists and display the message box. Maybe the message box is being hidden behind the Excel window?
    Quote Originally Posted by gruntingmonkey View Post
    And also, how can I tell what the answer is to that question in Excel?
    Where in Excel would the answer go?
    Cheers
    Paul Edstein
    [MS MVP - Word]

  3. #13
    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)

    Quote Originally Posted by gruntingmonkey View Post
    How would I have all the code in Excel? This would negate my need to pass a variable between applications.
    It's pretty much just a case of moving your existing Word code into Excel. For example:
    Code:
    Option Explicit
    
    Sub CreateSelfFundedContract()
    Dim wdApp As New Word.Application, wdDoc As Word.Document
    Dim ConTemp As String, SalesSig As String, Condate As String, Canname As String
    Dim UserNm As String, StrPath As String, MMSrc As String, MMOut As String
    
    '''Error Handler
    On Error GoTo MyErrorHandler
    
    '''File Path & name variables
    UserNm = Environ$("Username")
    StrPath = "C:\Users\" & UserNm & "\The Jess Consultancy\Sales - Documents\Contracts\~ Contract Wizard\"
    ConTemp = StrPath & "Templates\Master Self Funder Contract.docx"
    MMSrc = StrPath & "~ Contract Creator Master File.xlsm"
    MMOut = StrPath & "Contracts\SF " & Canname & " " & Condate & ".docx"
    
    '''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
    
    
    If Dir(ConTemp) <> "" Then
      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
        '''Creates a new document from the Contract master
        Set wdDoc = wdApp.Documents.Add(ConTemp)
    
        With wdDoc
          .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
          .Close 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
      
            '' updates utilities sheet
            With Sheets("Utilities")
              .Cells(7, 2).Value = ""
              .Cells(7, 4).Value = "InComplete"
            End With
            .ActiveDocument.Close False
            GoTo wdExit
          End If
        End If
        With .ActiveDocument
          .SaveAs2 Filename:=MMOut, FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=True, CompatibilityMode:=15
          .Close False
        End With
        
      '' updates utilities sheet
        With Sheets("Utilities")
          .Cells(7, 2).Value = Application.UserName & " " & Now
          .Cells(7, 4).Value = "Complete"
        End With
    wdExit:
    
        '''Check for any new errors
        .DisplayAlerts = wdAlertsAll
        ''' Exit Word without saving changes to the Contract master
        .Quit
      End With
      Set wdDoc = Nothing: Set wdApp = Nothing
    End If
    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
    Note that the above code assumes your contract master will now be a docx file.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  4. #14
    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 Macropod View Post
    Where in Excel would the answer go?
    If it was not overwritten, then the following should say "Incomplete"

    Code:
      '' updates utilities sheet
        With Sheets("Utilities")
          .Cells(7, 2).Value = Application.UserName & " " & Now
          .Cells(7, 4).Value = "Complete"
        End With

  5. #15
    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)

    Quote Originally Posted by gruntingmonkey View Post
    If it was not overwritten, then the following should say "Incomplete"
    Which is what the code in post # 13 should do. To make it stand out more though, the username & time are not output - but it's easy to add that back in if you want.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  6. #16
    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)

    This is just excellent. I've now transferred all of the code to Excel which makes it much easier to work with. I have made a few adaptions to your code but thank you soooo much for setting me on the right path.

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
  •