Catching an error

Hey I have a spreadsheet that has a macro to populate a word document, its all a bit complicated but if a certain cell isnt filled out a runtime error occurs when the macro is run. Is there a way of instead of this error coming up a dialogue box appears and the macro is exited? if there is not a simple way of doing this then I will post more details...

You need to include "error handling" code into the Macro;
Probably best if you copy and paste the macro into this thread,
then someone can review it, and modify it for you << to do so will
affect
2,3 or 4 lines of new code..

Jim



"ChrisMattock"
<ChrisMattock.2ae23z_1151962203.0641@excelforum-nospam.com> wrote in
message news:ChrisMattock.2ae23z_1151962203.0641@excelforum-nospam.com:

> Hey I have a spreadsheet that has a macro to populate a word document,
> its all a bit complicated but if a certain cell isnt filled out a
> runtime error occurs when the macro is run. Is there a way of instead
> of this error coming up a dialogue box appears and the macro is exited?
> if there is not a simple way of doing this then I will post more
> details...
>
>
> --
> ChrisMattock
> ------------------------------------------------------------------------
> ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
> View this thread: http://www.excelforum.com/showthread...hreadid=557935

OK thanks, here goes...

Sub main()
strLOADate = Cells(16, 2)
strProjectNumber = Cells(6, 2)
strProjectname = Cells(7, 2)
strFAO = Cells(8, 2)
strContractorName = Cells(9, 2)
strContractorAddress1 = Cells(10, 2)
strContractorAddress2 = Cells(11, 2)
strContractorAddress3 = Cells(12, 2)
strContractorAddress4 = Cells(13, 2)
strContractorAddress5 = Cells(14, 2)
strContractorAddress6 = Cells(15, 2)
strReference = Cells(17, 2)
strPackageName = Cells(18, 2)
strWorksServices = Cells(19, 2)
strValueNumber = Cells(20, 2)
strValueWord = Cells(21, 2)
strContractType = Cells(22, 2)
strPMName = Cells(23, 2)
strPMTelephone = Cells(24, 2)
strCostIntegrator = Cells(25, 2)
strCommencementStatement = Cells(26, 2)
strCommencementDate = Cells(27, 2)
strCompletionStatement = Cells(28, 2)
strCompletionDate = Cells(29, 2)
strSecondaryOptions = Cells(30, 2)
strStage = Cells(31, 2)
strSignature = Cells(64, 1)
strTitle = Cells(65, 1)
strBAAAddress1 = Cells(67, 1)
strBAAAddress2 = Cells(68, 1)
strBAAAddress3 = Cells(69, 1)
strBAAAddress4 = Cells(70, 1)
strBAAAddress5 = Cells(71, 1)
strBAAAddress6 = Cells(72, 1)
strBAAAddress7 = Cells(73, 1)


Fname$ = InputBox("Save Letter of Acceptance as PROJECTNUMBER_PROJECTNAME_SUPPLIER_LOA:")
If Fname$ = Cancel Then
End
End If

Dim Fnum As Integer
Fnum = FreeFile
Open ThisWorkbook.Path & "\Templates\log.txt" For Append As #Fnum
Print #Fnum, Fname$, Format(Now, "dd-mmm-yyy hh:mm"), "LOA", Environ("username")
Close #Fnum

Dim appWD As Word.Application
Set appWD = CreateObject("word.application.8")
appWD.Visible = True
appWD.Documents.Open FileName:="\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport Team\SE Airports TSA Team Folder\LOA_Generator\Templates\LOA_Template.doc"

appWD.ActiveDocument.Bookmarks("LOADate").Range = Format(strLOADate, "d mmmm yyyy")
appWD.ActiveDocument.Bookmarks("LOADate2").Range = Format(strLOADate, "d mmmm yyyy")
appWD.ActiveDocument.Bookmarks("LOADate3").Range = Format(strLOADate, "d mmmm yyyy")
appWD.ActiveDocument.Bookmarks("LOADate4").Range = Format(strLOADate, "d mmmm yyyy")
appWD.ActiveDocument.Bookmarks("LOADate5").Range = Format(strLOADate, "d mmmm yyyy")
appWD.ActiveDocument.Bookmarks("LOADate6").Range = Format(strLOADate, "d mmmm yyyy")

appWD.ActiveDocument.Bookmarks("ProjectNumber").Range = strProjectNumber

appWD.ActiveDocument.Bookmarks("ProjectName").Range = strProjectname
appWD.ActiveDocument.Bookmarks("ProjectName2").Range = strProjectname
appWD.ActiveDocument.Bookmarks("ProjectName3").Range = strProjectname

appWD.ActiveDocument.Bookmarks("FAO").Range = strFAO

appWD.ActiveDocument.Bookmarks("ContractorName").Range = strContractorName

appWD.ActiveDocument.Bookmarks("ContractorAddress1").Range = strContractorAddress1
appWD.ActiveDocument.Bookmarks("ContractorAddress2").Range = strContractorAddress2
appWD.ActiveDocument.Bookmarks("ContractorAddress3").Range = strContractorAddress3
appWD.ActiveDocument.Bookmarks("ContractorAddress4").Range = strContractorAddress4
appWD.ActiveDocument.Bookmarks("ContractorAddress5").Range = strContractorAddress5
appWD.ActiveDocument.Bookmarks("ContractorAddress6").Range = strContractorAddress6

appWD.ActiveDocument.Bookmarks("BAAAddress1").Range = strBAAAddress1
appWD.ActiveDocument.Bookmarks("BAAAddress2").Range = strBAAAddress2
appWD.ActiveDocument.Bookmarks("BAAAddress3").Range = strBAAAddress3
appWD.ActiveDocument.Bookmarks("BAAAddress4").Range = strBAAAddress4
appWD.ActiveDocument.Bookmarks("BAAAddress5").Range = strBAAAddress5
appWD.ActiveDocument.Bookmarks("BAAAddress6").Range = strBAAAddress6
appWD.ActiveDocument.Bookmarks("BAAAddress7").Range = strBAAAddress7

appWD.ActiveDocument.Bookmarks("Title").Range = strTitle

appWD.ActiveDocument.Bookmarks("Signature").Range = strSignature

appWD.ActiveDocument.Bookmarks("Reference").Range = strReference
appWD.ActiveDocument.Bookmarks("Reference2").Range = strReference
appWD.ActiveDocument.Bookmarks("Reference3").Range = strReference
appWD.ActiveDocument.Bookmarks("Reference4").Range = strReference
appWD.ActiveDocument.Bookmarks("Reference5").Range = strReference
appWD.ActiveDocument.Bookmarks("Reference6").Range = strReference
appWD.ActiveDocument.Bookmarks("Reference7").Range = strReference
appWD.ActiveDocument.Bookmarks("Reference10").Range = strReference

appWD.ActiveDocument.Bookmarks("PackageName").Range = strPackageName
appWD.ActiveDocument.Bookmarks("PackageName2").Range = strPackageName
appWD.ActiveDocument.Bookmarks("PackageName3").Range = strPackageName

appWD.ActiveDocument.Bookmarks("WorksServices").Range = strWorksServices
appWD.ActiveDocument.Bookmarks("WorksServices2").Range = strWorksServices
appWD.ActiveDocument.Bookmarks("WorksServices3").Range = strWorksServices
appWD.ActiveDocument.Bookmarks("WorksServices4").Range = strWorksServices

appWD.ActiveDocument.Bookmarks("ValueNumber").Range = Format(strValueNumber, "£#,##0.00")
appWD.ActiveDocument.Bookmarks("ValueNumber2").Range = Format(strValueNumber, "£#,##0.00")

appWD.ActiveDocument.Bookmarks("ValueWord").Range = strValueWord

appWD.ActiveDocument.Bookmarks("ContractType").Range = strContractType

appWD.ActiveDocument.Bookmarks("PMName").Range = strPMName

appWD.ActiveDocument.Bookmarks("PMTelephone").Range = Format(strPMTelephone, "0#### ######")

appWD.ActiveDocument.Bookmarks("CostIntegrator").Range = strCostIntegrator

appWD.ActiveDocument.Bookmarks("CommencementStatement").Range = strCommencementStatement

appWD.ActiveDocument.Bookmarks("CommencementDate").Range = Format(strCommencementDate, "d mmmm yyyy")

appWD.ActiveDocument.Bookmarks("CompletionStatement").Range = strCompletionStatement

appWD.ActiveDocument.Bookmarks("CompletionDate").Range = Format(strCompletionDate, "d mmmm yyyy")

appWD.ActiveDocument.Bookmarks("SecondaryOptions").Range = strSecondaryOptions

If Dir("\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport Team\SE Airports TSA Team Folder\LOA_Generator\LOA\" & strProjectNumber, vbDirectory) = "" Then MkDir "\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport Team\SE Airports TSA Team Folder\LOA_Generator\LOA\" & strProjectNumber
appWD.ActiveDocument.SaveAs FileName:="\\lgwsvr011\group9\Projects-Everyone\51 Gatwick Airport Team\SE Airports TSA Team Folder\LOA_Generator\LOA\" & strProjectNumber & "\" & Fname$
appWD.ActiveDocument.Close
appWD.Quit

End Sub


IT IS RUNTIME ERROR 13 THAT I GET.

Chris,
I guess it is one of the date fields that errors with the Format function.
To simplify the code why not reverse your procedure.

Sub main()
On Error GoTo Handler

'Code to create Word etc
With appWD.ActiveDocument.
.Bookmarks("LOADate").Range =Format(Cells(16, 2), "d mmmm yyyy")
'Other code

'Success so log results etc

Exit Sub
Handler: 'Get here means failure
Select case Err.Number
Case 13
MsgBox "Missing data"
Case Else
'Any others ?
End Select
appWD.ActiveDocument.Close false
'Clean Up objects etc
End Sub

Given that you have a lot of related info, I would probably make a class out
of this to handle its own reading from XL WS, validation and writing to
Word.
e.g. <cLOAProjectReport>
Public Property Let SourceWS (vData as Excel.Worksheet)

Public Property Let WordTemplateName (vData as String)

Public Function Generate (Optional LogResults As Boolean=True) As String
'Return the address of the first cell missing data

NickHK

"ChrisMattock" <ChrisMattock.2aesyn_1151997003.2503@excelforum-nospam.com>
wrote in message
news:ChrisMattock.2aesyn_1151997003.2503@excelforum-nospam.com...
>
> OK thanks, here goes...
>
> Sub main()
> strLOADate = Cells(16, 2)
> strProjectNumber = Cells(6, 2)
> strProjectname = Cells(7, 2)
> strFAO = Cells(8, 2)
> strContractorName = Cells(9, 2)
---------------------------- CUT

Wow Nick, that's great I will give it all a go, thanks for your time.

Yes that all worked brilliantly in terms of Error Handling, any chance you could explain this a little more? Not sure I understand :S

Given that you have a lot of related info, I would probably make a class out
of this to handle its own reading from XL WS, validation and writing to
Word.
e.g. <cLOAProjectReport>
Public Property Let SourceWS (vData as Excel.Worksheet)

Public Property Let WordTemplateName (vData as String)

Public Function Generate (Optional LogResults As Boolean=True) As String
'Return the address of the first cell missing data

Part of the concept of using classes is to encapsulate the logic. As such,
only the class need to know how to check for valid data and generate the
report.
I would guess you are likely to have various report of different
format/requirements, so each could have it's own class.
So in the VBE, select Insert>Class Module
'Declarations
Dim appWD As Word.Application
Private WS as Worksheet
Private WordFile As String

Private Sub Class_Initialize()
Set appWD = New Word.Application
End Sub

Private Sub Class_Terminate()
appWD.Quit
Set appWD=nothing
End Sub
'Set a reference to WS to use as source
Public Property Let SourceWS (vData as Excel.Worksheet)
Set WS=vData
End Property
'Tell the class which Word file to use
Public Property Let WordTemplateName (vData as String)
WordFile=vData
End Property

Public Function Generate (Optional LogResults As Boolean=True) As String
Dim ErrStr As String
On Error GoTo Handler

With appWD.Open WordFile
'write all the formatted bookmark data
'...& log data
'...& close/save
End With
Generate=""

Exit Function
Handler:
Select Case Err.Number
Case xx 'WS invalid
ErrStr="WS not set"
Case yy 'Word file invalid
ErrStr="Word file not valid"
Case zz 'data mising
ErrStr="Missing data"
Case Else
ErrStr=Err.description
End Select
Generate = ErrStr
'Clean up
End Function

Then in your WS all you need to do is you can do the whole thing in a couple
of lines:
Dim MyReport as cLOAProjectReport
Dim Retstr As String
With MyReport
Set .SourceWS = Me 'Or Worksheet("NewData") or whatever
.WordFile="\\lgwsvr011\<fullpath>\Templates\LOA_Template.doc"
RetStr=.Generate(False)
If RetStr<>"" Then
'Error so decide what to do
End If
End With

NickHK

"ChrisMattock" <ChrisMattock.2aew7c_1152001202.9742@excelforum-nospam.com>
wrote in message
news:ChrisMattock.2aew7c_1152001202.9742@excelforum-nospam.com...
>
> Yes that all worked brilliantly in terms of Error Handling, any chance
> you could explain this a little more? Not sure I understand :S
>
> > Given that you have a lot of related info, I would probably make a class
> > out
> > of this to handle its own reading from XL WS, validation and writing
> > to
> > Word.
> > e.g. <cLOAProjectReport>
> > Public Property Let SourceWS (vData as Excel.Worksheet)
> >
> > Public Property Let WordTemplateName (vData as String)
> >
> > Public Function Generate (Optional LogResults As Boolean=True) As
> > String
> > 'Return the address of the first cell missing data
>
>
> --
> ChrisMattock
> ------------------------------------------------------------------------
> ChrisMattock's Profile:
http://www.excelforum.com/member.php...o&userid=33912
> View this thread: http://www.excelforum.com/showthread...hreadid=557935
>