I have written some code, which uses bookmarks to transfer data from an excel database to a template i have on Word.
It all works well, it takes a value from the cell "B3" which is a works order number. It then searches for this number in the database and if found it transfers that line of the database to the word template.
HOWEVER, if the works order number entered in cell B3 is not found, it runs through and does nothing (which is what i want) but still displays the messagebox at the end "Inspection Sheet Created". However i would like to return a messagebox saying "Number not found"
However I am new to coding, and I am not sure where to put the else in the IF statement to display this messagebox.
It all works well, it takes a value from the cell "B3" which is a works order number. It then searches for this number in the database and if found it transfers that line of the database to the word template.
HOWEVER, if the works order number entered in cell B3 is not found, it runs through and does nothing (which is what i want) but still displays the messagebox at the end "Inspection Sheet Created". However i would like to return a messagebox saying "Number not found"
However I am new to coding, and I am not sure where to put the else in the IF statement to display this messagebox.
VBA Code:
Dim wd As Object 'Word Application
Dim wdDOC As Object 'word document
Dim iRow As Long 'Variable to hold the starting row and loop through all records in database
Dim sh As Worksheet 'worksheet variable to refer to where database is stored
Dim myValue As Variant
Dim WorksOrder As String
'Start word as new document
Set wd = CreateObject("Word.Application")
'Get user entered WorksOrder Number
WorksOrder = ThisWorkbook.Sheets("PrintDocuments").Range("B3").Value
'Set worksheet
Set sh = ThisWorkbook.Sheets("Database")
iRow = 2 'row in which data starts from in database
Do While sh.Range("A" & iRow).Value <> "" 'loop through until no data is found (last row of database)
If WorksOrder = sh.Range("D" & iRow).Value Then
'opening word template
Set wdDOC = wd.Documents.Add("T:\mageeg\TEST DATA INSPECTION SCHEDULE Issue 3.docx")
wd.Visible = False
'code to insert values from database to bookmarks in word
wd.Selection.GoTo what:=wdGoToBookmark, Name:="PartNo"
wd.Selection.TypeText Text:=sh.Range("C" & iRow).Value
wd.Selection.GoTo what:=wdGoToBookmark, Name:="Serial"
wd.Selection.TypeText Text:=sh.Range("E" & iRow).Value
wd.Selection.GoTo what:=wdGoToBookmark, Name:="ModelNo"
wd.Selection.TypeText Text:=sh.Range("B" & iRow).Value
wd.Selection.GoTo what:=wdGoToBookmark, Name:="WorksOrderNo"
wd.Selection.TypeText Text:=sh.Range("D" & iRow).Value
wd.Selection.GoTo what:=wdGoToBookmark, Name:="MaterialNo"
wd.Selection.TypeText Text:=sh.Range("F" & iRow).Value
wd.Selection.GoTo what:=wdGoToBookmark, Name:="SerialNo"
wd.Selection.TypeText Text:=sh.Range("E" & iRow).Value
wd.Selection.GoTo what:=wdGoToBookmark, Name:="ModelNo2"
wd.Selection.TypeText Text:=sh.Range("B" & iRow).Value
wd.Selection.GoTo what:=wdGoToBookmark, Name:="Type"
wd.Selection.TypeText Text:=sh.Range("H" & iRow).Value
wd.Selection.GoTo what:=wdGoToBookmark, Name:="Size"
wd.Selection.TypeText Text:=sh.Range("I" & iRow).Value
wd.Selection.GoTo what:=wdGoToBookmark, Name:="WKPRESS"
wd.Selection.TypeText Text:=sh.Range("J" & iRow).Value
wd.Selection.GoTo what:=wdGoToBookmark, Name:="SerialNumber"
wd.Selection.TypeText Text:=sh.Range("G" & iRow).Value
wd.Selection.GoTo what:=wdGoToBookmark, Name:="CertDate"
wd.Selection.TypeText Text:=sh.Range("K" & iRow).Value
wd.Selection.GoTo what:=wdGoToBookmark, Name:="BatchNo"
wd.Selection.TypeText Text:=sh.Range("L" & iRow).Value
wd.Selection.GoTo what:=wdGoToBookmark, Name:="JobNo"
wd.Selection.TypeText Text:=sh.Range("M" & iRow).Value
wd.Selection.GoTo what:=wdGoToBookmark, Name:="DateOfManufacture"
wd.Selection.TypeText Text:=Format(sh.Range("N" & iRow).Value, "mm/yy")
'code to delete the existing bookmarks from wordfile
On Error Resume Next
wdDOC.Bookmarks("PartNo").Delete
wdDOC.Bookmarks("SerialNo").Delete
wdDOC.Bookmarks("ModelNo").Delete
wdDOC.Bookmarks("WorksOrderNo").Delete
wdDOC.Bookmarks("MaterialNo").Delete
wdDOC.Bookmarks("Serial").Delete
wdDOC.Bookmarks("ModelNo2").Delete
wdDOC.Bookmarks("Type").Delete
wdDOC.Bookmarks("Size").Delete
wdDOC.Bookmarks("WKPRESS").Delete
wdDOC.Bookmarks("SerialNumber").Delete
wdDOC.Bookmarks("CertDate").Delete
wdDOC.Bookmarks("BatchNo").Delete
wdDOC.Bookmarks("JobNo").Delete
wdDOC.Bookmarks("DateOfManufacture").Delete
'save file with new name
wdDOC.SaveAs2 (ThisWorkbook.Path & "\" & sh.Range("D" & iRow).Value & ".docx")
'Print document
wdDOC.PrintOut
'close the word file
wdDOC.Close
'release memory of word doc
Set wdDOC = Nothing
Exit Do
End If
iRow = iRow + 1
Loop
wd.Quit 'close MS Word
Set wd = Nothing 'Release memory allocated to WD
MsgBox ("Inspection Test Sheet Created")
End Sub