ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Inputbox help... (https://www.excelbanter.com/excel-programming/361311-inputbox-help.html)

ChrisMattock[_11_]
Inputbox help...
 

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").Ra nge =
strProjectNumber
appWD.ActiveDocument.Bookmarks("ProjectNumber2").R ange =
strProjectNumber
appWD.ActiveDocument.Bookmarks("ProjectNumber3").R ange =
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("ProjectName4").Ran ge =
strProjectName
appWD.ActiveDocument.Bookmarks("ProjectName5").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("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").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("PackageName4").Ran ge =
strPackageName
appWD.ActiveDocument.Bookmarks("PackageName5").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("PMName2").Range = strPMName

appWD.ActiveDocument.Bookmarks("PMTelephone").Rang e =
strPMTelephone

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

appWD.ActiveDocument.Bookmarks("CommencementStatem ent").Range =
strCommencementStatement
appWD.ActiveDocument.Bookmarks("CommencementStatem ent2").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("CompletionStatemen t").Range =
strCompletionStatement
appWD.ActiveDocument.Bookmarks("CompletionStatemen t2").Range =
strCompletionStatement

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


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

DS
Inputbox help...
 
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

"ChrisMattock" wrote:


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").Ra nge =
strProjectNumber
appWD.ActiveDocument.Bookmarks("ProjectNumber2").R ange =
strProjectNumber
appWD.ActiveDocument.Bookmarks("ProjectNumber3").R ange =
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("ProjectName4").Ran ge =
strProjectName
appWD.ActiveDocument.Bookmarks("ProjectName5").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("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").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("PackageName4").Ran ge =
strPackageName
appWD.ActiveDocument.Bookmarks("PackageName5").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("PMName2").Range = strPMName

appWD.ActiveDocument.Bookmarks("PMTelephone").Rang e =
strPMTelephone

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

appWD.ActiveDocument.Bookmarks("CommencementStatem ent").Range =
strCommencementStatement
appWD.ActiveDocument.Bookmarks("CommencementStatem ent2").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("CompletionStatemen t").Range =
strCompletionStatement
appWD.ActiveDocument.Bookmarks("CompletionStatemen t2").Range =
strCompletionStatement

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


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

[email protected]
Inputbox help...
 
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[_12_]
Inputbox help...
 

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!)


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


All times are GMT +1. The time now is 02:17 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com