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

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"
wrote in
message news:ChrisMattock.2ae23z_1151962203.0641@excelforu m-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").Ra nge =
strProjectNumber

appWD.ActiveDocument.Bookmarks("ProjectName").Rang e =
strProjectname
appWD.ActiveDocument.Bookmarks("ProjectName2").Ran ge =
strProjectname
appWD.ActiveDocument.Bookmarks("ProjectName3").Ran ge =
strProjectname

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

appWD.ActiveDocument.Bookmarks("ContractorName").R ange =
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").Rang e =
strBAAAddress1
appWD.ActiveDocument.Bookmarks("BAAAddress2").Rang e =
strBAAAddress2
appWD.ActiveDocument.Bookmarks("BAAAddress3").Rang e =
strBAAAddress3
appWD.ActiveDocument.Bookmarks("BAAAddress4").Rang e =
strBAAAddress4
appWD.ActiveDocument.Bookmarks("BAAAddress5").Rang e =
strBAAAddress5
appWD.ActiveDocument.Bookmarks("BAAAddress6").Rang e =
strBAAAddress6
appWD.ActiveDocument.Bookmarks("BAAAddress7").Rang e =
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").Rang e = strReference

appWD.ActiveDocument.Bookmarks("PackageName").Rang e =
strPackageName
appWD.ActiveDocument.Bookmarks("PackageName2").Ran ge =
strPackageName
appWD.ActiveDocument.Bookmarks("PackageName3").Ran ge =
strPackageName

appWD.ActiveDocument.Bookmarks("WorksServices").Ra nge =
strWorksServices
appWD.ActiveDocument.Bookmarks("WorksServices2").R ange =
strWorksServices
appWD.ActiveDocument.Bookmarks("WorksServices3").R ange =
strWorksServices
appWD.ActiveDocument.Bookmarks("WorksServices4").R ange =
strWorksServices

appWD.ActiveDocument.Bookmarks("ValueNumber").Rang e =
Format(strValueNumber, "£#,##0.00")
appWD.ActiveDocument.Bookmarks("ValueNumber2").Ran ge =
Format(strValueNumber, "£#,##0.00")

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

appWD.ActiveDocument.Bookmarks("ContractType").Ran ge =
strContractType

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

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

appWD.ActiveDocument.Bookmarks("CostIntegrator").R ange =
strCostIntegrator

appWD.ActiveDocument.Bookmarks("CommencementStatem ent").Range =
strCommencementStatement

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

appWD.ActiveDocument.Bookmarks("CompletionStatemen t").Range =
strCompletionStatement

appWD.ActiveDocument.Bookmarks("CompletionDate").R ange =
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.


--
ChrisMattock
------------------------------------------------------------------------
ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
View this thread: http://www.excelforum.com/showthread...hreadid=557935

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"
wrote in message
news:ChrisMattock.2aesyn_1151997003.2503@excelforu m-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. :)


--
ChrisMattock
------------------------------------------------------------------------
ChrisMattock's Profile: http://www.excelforum.com/member.php...o&userid=33912
View this thread: http://www.excelforum.com/showthread...hreadid=557935

Yes that all worked brilliantly in terms of Error Handling, any chanc
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 clas
out
of this to handle its own reading from XL WS, validation and writin
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) A
String
'Return the address of the first cell missing dat

--
ChrisMattoc
-----------------------------------------------------------------------
ChrisMattock's Profile: http://www.excelforum.com/member.php...fo&userid=3391
View this thread: http://www.excelforum.com/showthread.php?threadid=55793

Yes that all worked brilliantly in terms of Error Handling, any chanc
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 clas
out
of this to handle its own reading from XL WS, validation and writin
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) A
String
'Return the address of the first cell missing dat

--
ChrisMattoc
-----------------------------------------------------------------------
ChrisMattock's Profile: http://www.excelforum.com/member.php...fo&userid=3391
View this thread: http://www.excelforum.com/showthread.php?threadid=55793