Word freezes after executing Excel VBA to create document - Stack Overflow

admin管理员组

文章数量:1287584

I have a large document template in Word (450+ pages 20+mb), which has over 100 bookmarked sections. I use an Excel spreadsheet to compile information about the document that a user is trying to create, and then delete bookmarked sections from the Word template to create a document which is more specific to what the user wants (typically around 150 pages, rather than the full 450+ pages). The full code is below (I've removed a heap of the bookmark deletions to cut the size down).

It all seems to work . . . sometimes! Sometimes (maybe 50% or more), it gets to the end of the code, or very close to the end, and even if it all appears to have worked OK, clicking back into the saved document in Word, Word stops responding. Stepping through the macro seems more reliable(?) but still doesn't always work. Sometimes it causes Excel to freeze, sometimes it seems to get to the end of the macro and Excel still functions. Sometimes, after 10 minutes or so, Word comes back. Sometimes, after an hour, it doesn't recover. If I cut my original report template in half, then it seems to work every time . . . which obviously suggests that the issue it due to the size of the Word template. So, my problem seems to relate to having a very large document . . . but that's really something I can't easily avoid, so I'm trying to figure out how to make my code work?

Sub Report()
    Dim ProjNo As String
    Dim ProjName As String
    Dim Subject As String
    Dim ClientName As String
    Dim PMEmail As String
    Dim LGA As String
    Dim oWord As Object
    Dim oWDoc As Object
    Dim FileSaveDialog As Variant
    Dim FileName As Variant
    Dim FilePath As Variant
    Dim DocType As String

    'Licence Check
    If Range("Licence") = False Then
        MsgBox "Licence has expired"
        Exit Sub
    End If

    'Project number check
    If Range("ProjNo") = "[Enter project number]" Then
        MsgBox ("Please enter a project number")
        Exit Sub
    End If

    'Define Info
    StaffInit = Range("DefStaffInit")
    PMEmail = Range("PMEmail")
    ProjNo = Range("ProjNo")
    ProjName = Range("ProjAddr") & ", " & Range("ProjSubu")
    Subject = Range("ReportType")
    ClientName = Range("ClientName")
    LGA = Range("LGA")
    DocType = Range("DocType")

    'Goto or Open Word
    On Error Resume Next
    Set oWord = GetObject(, "Word.Application")
    If Err <> 0 Then
    Set oWord = CreateObject("Word.Application")
    End If
    oWord.Visible = True
    oWord.ScreenUpdating = False

    'Open Report
    Set oWDoc = oWord.Documents.Add("N:\Data\Templates\Word\Report.dotm")

    'Bookmarks
    If Range("BKExecSum") < 1 And oWDoc.Bookmarks.Exists("ExecSum") Then oWDoc.Bookmarks("ExecSum").Range.Delete
    If Range("BKExecTIA") < 1 And oWDoc.Bookmarks.Exists("ExecTIA") Then oWDoc.Bookmarks("ExecTIA").Range.Delete
    If Range("BKExecSSA") < 1 And oWDoc.Bookmarks.Exists("ExecSSA") Then oWDoc.Bookmarks("ExecSSA").Range.Delete

    'Copy Info to Report
    oWDoc.BuiltInDocumentProperties("Title").Value = ProjName
    oWDoc.BuiltInDocumentProperties("Subject").Value = Subject
    oWDoc.BuiltInDocumentProperties("Company").Value = ClientName
    oWDoc.BuiltInDocumentProperties("Author").Value = StaffInit
    oWDoc.BuiltInDocumentProperties("Comments").Value = LGA
    oWDoc.BuiltInDocumentProperties("Keywords").Value = PMEmail

    'General
    oWord.ActiveDocument.Fields.Update
    oWord.ScreenUpdating = True

    'Set File name and path
    If DocType = "FEE" Then
        FileName = ProjNo & DocType & "001A-F.docx"
    Else
        FileName = ProjNo & DocType & "001A.docx"
    End If
    FilePath = "N:\Projects\20" & Left(ProjNo, 2) & "\" & ProjNo & "\Docs\"

    'Open Save As Dialog Box
    Set FileSaveDialog = oWord.Application.Dialogs(wdDialogFileSaveAs)
    FileSaveDialog.Name = FilePath & FileName
    FileSaveDialog.Show

    'Clear Objects
    Set oWord = Nothing
    Set oWDoc = Nothing

End Sub

The above code has been copied from a number of different examples I've found, rather than me creating it, so please don't assume I know what I am doing!!

Thanks in advance for any help!

本文标签： Word freezes after executing Excel VBA to create documentStack Overflow