Radical Dylan
New Member
- Joined
- Nov 21, 2011
- Messages
- 6
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.
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.
Code:
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