Document Template Already In Use by Excel Macro

[Radical Dylan]

Hello Everyone,

I have a macro that opens a word document template that I have saved on the C:\ Drive, changes a few things in it, saves the file as a new name, and then leaves it open to let the user manually edit the rest of the data needed.

This works fine, but when I change some data in my excel spreadsheet and then run the macro again a message box pops up saying that the template file is already in use, do I want to open as read only, make a local copy, or wait until it is no longer in use.

This message box is slightly annoying and not user friendly, is there anyway to avoid it? Or is there a specific reason my excel spreadsheet doesn't stop using the template?

I was thinking that I could copy the template file in the macro and then open the copy. I could then simply rename the copy and save; however, I could not figure out how to do this. All of this wouldn't even be necessary if my macro wasn't still using my template; so maybe I'm forgetting something in my code to stop the use of the template file completely when finished. Any suggestions? Thanks.

Sub ReportPrep()
    Application.DisplayAlerts = False
    Dim wrdApp As Word.Application
    Dim wrdDoc As Word.Document
 
 
    'declare variables
    Dim project As String
    Dim residence As String
    Dim address As String
    Dim city As String
    Dim state As String
    Dim claim As String
    Dim policy As String
 
    Dim file As String
    Dim insCompany As String
    Dim insContact As String
    Dim insAddress As String
    Dim insCity As String
    Dim insState As String
    Dim insCompany2 As String
    Dim DOL As String
 
    Dim JobType As String
    Dim Blank As String
 
 
 
    Dim FileName As Variant
    Dim Filt As String, Title As String
    Dim FilterIndex As Integer, Response As Integer
 
    Dim FolderName As String
    Dim FolderPhotos As String
    Dim FolderReports As String
    Dim FolderBilling As String
    Dim FolderNotes As String
    Dim FolderEmails As String
    Dim FolderFeeAgreement As String
    Dim Drive As String
 
    Dim ReportSave As String
 
 
 
''''''   --------------
''''''   Pull Data
    'pull data from excell spreadsheet that code is connected too
    'and put the data in the corresponding variables.
    project = Range("C9").Text
    residence = Range("C10").Text
    address = Range("C11").Text
    city = Range("C12").Text
    state = Range("C13").Text
    claim = Range("C14").Text
    policy = Range("C15").Text
 
    file = Range("C16").Text
    insCompany = Range("C17").Text
    insContact = Range("C18").Text
    insAddress = Range("C19").Text
    insCity = Range("C20").Text
    insState = Range("C21").Text
    DOL = Range("C22").Text
 
    JobType = Range("C23").Text
 
    Dim Purpose1 As String
    Dim Purpose2 As String
    Dim Report As String
 
 
'''''''   --------------
'''''''   Check to see if State is NY
'''''''   And Check Type of Report
    Dim NY As Boolean
    Dim NC As Boolean
    'A = Structural
    Dim A As Boolean
    'B = Flood
    Dim B As Boolean
    'C = Fire
    Dim C As Boolean
    'D = Tile
    Dim D As Boolean
    'E = Floor
    Dim E As Boolean
    'F = Well
    Dim F As Boolean
    'G = Pool
    Dim G As Boolean
    'H = Litigation
    Dim H As Boolean
    'I = Building Science
    Dim I As Boolean
 
    NY = state Like "NY*"
    Debug.Print NY
 
    NC = state Like "NC*"
    Debug.Print NC
 
    A = JobType Like "A*"
    B = JobType Like "B*"
    C = JobType Like "C*"
    D = JobType Like "D*"
    E = JobType Like "E*"
    F = JobType Like "F*"
    G = JobType Like "G*"
    H = JobType Like "H*"
    I = JobType Like "I*"
 
    If A = True Then
        Report = "Structural Damage Report"
        Purpose1 = "The purpose of this investigation was to observe damage that " & _
                  "was reported at the residence and to determine the cause and" & _
                  "the extent of the reported damage. "
        Purpose2 = "This report provides a summary " & _
                  "of the findings, observations, and recommendations of the damages at <<2>>."
    ElseIf G = True Then
        Report = "Pool Damage Report"
        Purpose1 = "The purpose of this investigation was to observe damage that was reported at the pool and to determine the cause and the extent of the reported damage."
        Purpose2 = "This report provides a summary of the findings, observations, and recommendations of the damage of the pool at <<2>>."
    Else
        Report = "Structural Damage Report"
        Purpose1 = "The purpose of this investigation was to observe damage that " & _
                  "was reported at the residence and to determine the cause and " & _
                  "the extent of the reported damage."
        Purpose2 = "This report provides a summary " & _
                  "of the findings, observations, and recommendations of the damages at <<2>>."
 
    End If
 
''''   ------------------
''''   Prep Report
 
    Filt = "Word Files (*.do*), *.do*"
'   Set *.* to Default
    FilterIndex = 5
'   Set Dialogue Box Caption
    Title = "Please select a different File"
'   Get FileName
    If NY = True Then
        FileName = "C:\Templates\ReportTemplates\NYReportTemplate"
    ElseIf NC = True Then
        FileName = "C:\Templates\ReportTemplates\NCReportTemplate"
    ElseIf I = True Then
        FileName = "C:\Templates\ReportTemplates\BuildingScienceReportTemplate"
    Else: FileName = "C:\Templates\ReportTemplates\ReportTemplate"
    End If
    'create a word document based off a template and replace data
    Set wrdApp = CreateObject("Word.Application")
 
    wrdApp.Documents.Open FileName:=FileName, ConfirmConversions:=False _
        , ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
        PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
        WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""
 
    ReportSave = FolderReports & "\" & project & " " & residence & "_Report_Draft" & ".doc"
    wrdApp.ActiveDocument.SaveAs FileName:=ReportSave, _
        FileFormat:=wdOpenFormatAuto
    wrdApp.Visible = True
    wrdApp.Selection.HomeKey Unit:=wdLine
    wrdApp.Selection.Find.ClearFormatting
    wrdApp.Selection.Find.Replacement.ClearFormatting
    wrdApp.Activate
 
    ReportSave = FolderReports & "\" & project & " " & residence & "_Report_Draft" & ".doc"
    wrdApp.ActiveDocument.SaveAs FileName:=ReportSave, _
        FileFormat:=wdOpenFormatAuto
 
    Dim pRange As Word.Range
    Dim iLink As Long
    iLink = wrdApp.ActiveDocument.Sections(1).Headers(1).Range.StoryType
    For Each pRange In wrdApp.ActiveDocument.StoryRanges
        Do
            With pRange.Find
                .Text = "<<19>>"
                .Replacement.Text = Purpose1
                .Execute Replace:=wdReplaceAll
                    wrdApp.Selection.HomeKey Unit:=wdLine
    wrdApp.Selection.Find.ClearFormatting
    wrdApp.Selection.Find.Replacement.ClearFormatting
            End With
            With pRange.Find
                .Text = "<<20>>"
                .Replacement.Text = Purpose2
                .Execute Replace:=wdReplaceAll
                    wrdApp.Selection.HomeKey Unit:=wdLine
    wrdApp.Selection.Find.ClearFormatting
    wrdApp.Selection.Find.Replacement.ClearFormatting
            End With
            With pRange.Find
                .Text = "<<1>>"
                .Replacement.Text = project
                .Execute Replace:=wdReplaceAll
                    wrdApp.Selection.HomeKey Unit:=wdLine
    wrdApp.Selection.Find.ClearFormatting
    wrdApp.Selection.Find.Replacement.ClearFormatting
            End With
'''''''''' Additional With statements removed from this message board copy to
'''''''''' elimate unecessary code.            
            Set pRange = pRange.NextStoryRange
        Loop Until pRange Is Nothing
    Next
 
 
Application.DisplayAlerts = True
End Sub 'end

 

[Worf]

Hi
Try adding this at the end of your code and see if it helps:

set wrdApp = Nothing

 

[Radical Dylan]

Hi Worf,
I tried your suggestion of settinng wrdApp to nothing, but that sadly didn't work.

However, I found that if I force the file into readonly mode when I open the document and then immediately save the document as the new name; it can then edit this newly named document freely and I can always reopen the template because it is only ever being opened in readonly mode.

Probaby not the most elegent solution out there, but it does seem to solve the issue at hand. Sadly, it still doesn't solve the reason why the document is remaining in use by the macro after the macro is finished.

 

[Worf]

Hi Dylan

I recently participated in a similar thread (Excel-Word automation).

http://www.mrexcel.com/forum/showthread.php?t=594712

You may find something useful there. One topic discussed was early x late binding, maybe you could explore this to see if it makes a difference.