Inputbox help...

[ChrisMattock]

More posts from me... sorry!

In an excel spreadsheet that fills in bookmarks in a word template you
click a button that starts a macro to fill in the bookmarks and save
the document as a specified name from an inputbox... it all works great
except when you click on the "Cancel" button on the inputbox... then it
comes up with...

Runtime Error "5152":

This is not a valid filename.
Try one or more of the following:
* Check the path and make sure it was typed correctly
* Select a file frmo the list of files and folders

Apologies I am new to VB and rubbish, here is the code, any ideas?

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)

fname$ = InputBox("Save Letter of Acceptance")

Dim appWD As Word.Application
Set appWD = CreateObject("word.application.8")
appWD.Visible = True
appWD.Documents.Open FileName:="H:\My Documents\LOA
Stuff\TEST\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("LOADate7").Range =
Format(strLOADate, "d mmmm yyyy")

appWD.ActiveDocument.Bookmarks("Stage").Range = strStage

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

appWD.ActiveDocument.Bookmarks("ProjectName").Range =
strProjectName
appWD.ActiveDocument.Bookmarks("ProjectName2").Range =
strProjectName
appWD.ActiveDocument.Bookmarks("ProjectName3").Range =
strProjectName
appWD.ActiveDocument.Bookmarks("ProjectName4").Range =
strProjectName
appWD.ActiveDocument.Bookmarks("ProjectName5").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("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("Reference8").Range = strReference
appWD.ActiveDocument.Bookmarks("Reference9").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("PackageName4").Range =
strPackageName
appWD.ActiveDocument.Bookmarks("PackageName5").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("PMName2").Range = strPMName

appWD.ActiveDocument.Bookmarks("PMTelephone").Range =
strPMTelephone

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

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

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

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

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

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

appWD.ActiveDocument.SaveAs FileName:="H:\My Documents\LOA
Stuff\TEST\Test\" & fname$, FileFormat:=wdFormatDocument

appWD.ActiveDocument.Close
appWD.Quit

End Sub

 

[DS]

Hi Chris,

If, immediately after the InputBox you simply add:

fname$ = InputBox("Save Letter of Acceptance")' your code for the InputBox
to indicate where this goes

If fname$ = Cancel Then
End ' This will cause the sub to end if the user presses Cancel on the
InputBox
End If

' Continue rest of code

Of course, you can replace the "End" command with whatever you like (e.g. an
error message, loop back to the InputBox etc).

Hope this helps
DS

 

[aidan.heritage]

Might also be worth mentioning that you could use WITH
APPWD.ACTIVEDOCUMENT to get rid of that phrase the rest of the way
through the code (ending with an END WITH). Also, you refer to
word.application.8 - if you lose the .8 the code will work in Word 97
onwards with no further changes necessary.

 

[ChrisMattock]

Thanks a lot guys, all working wonderfully now! (For now, I'm sure I'll
need more help soon, but I really appreciate everyones time and
expertise!)