[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

Need help with Excel VBA code to close a Word document.

I'm using 2010 versions of Excel and Word.
When my Excel workbook, TT.xlsm, is opened, Workbook_Open() calls a local Sub, OpenAWordDocument, which, in turn, opens a Word document.

Sub OpenAWordDocument(Optional ByVal DocName As String = "", Optional ByVal DocPath As String = "")
Dim objWord As Object
  If Len(Trim(DocName)) = 0 Then
    DocName = "Word Document.docx"
  End If
  If Len(Trim(DocPath)) = 0 Then
    DocPath = "D:\Documentation"
  End If
  If Right(DocPath, 1) <> Application.PathSeparator Then
    DocPath = DocPath & Application.PathSeparator
  End If
'Open an existing Word Document from Excel
  Set objWord = CreateObject("Word.Application")
  With objWord
    .Visible = True
    .Documents.Open DocPath & DocName
  End With
  Set objWord = Nothing
End Sub[/indent][/indent][/indent]

Open in new window


I would appreciate some help developing a procedure to call from Workbook_BeforeClose() which will Save and Close the Word document without requiring operator intervention.

Thanks,
Tonkawa Jingles
0
J2F
Asked:
J2F
1 Solution
 
dsackerContract ERP Admin/ConsultantCommented:
You'll need module-level variables for the object, so to check it has been set later. I tested the following, and it worked:
Option Explicit
Public mobjDoc As Object
Public mobjWord As Object
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not mobjWord Is Nothing Then
        If mobjDoc.Saved = False Then
            mobjDoc.Save
        End If
        mobjWord.Quit
    End If
    Set mobjDoc = Nothing
    Set mobjWord = Nothing
End Sub
Private Sub Workbook_Open()
    Set mobjDoc = Nothing
    Set mobjWord = Nothing
    OpenAWordDocument "your.doc", "N:\YourPath"
End Sub
Public Sub OpenAWordDocument(Optional ByVal DocName As String = "", Optional ByVal DocPath As String = "")
    If Len(Trim(DocName)) = 0 Then
        DocName = "Word Document.docx"
    End If
    If Len(Trim(DocPath)) = 0 Then
        DocPath = "D:\Documentation"
    End If
    If Right(DocPath, 1) <> Application.PathSeparator Then
        DocPath = DocPath & Application.PathSeparator
    End If
    'Open an existing Word Document from Excel
    Set mobjWord = CreateObject("Word.Application")
    With mobjWord
        .Visible = True
        Set mobjDoc = .Documents.Open(DocPath & DocName)
    End With
End Sub

Open in new window

0
 
J2FAuthor Commented:
Works like a charm!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now