Hi i have recently created a few macros that enable me to fill in a userform and from that I can apply the input the particular bookmarks in a word doc. The macros i have created work very well so far.
Macro 1
MKDir checks for and if necessary creates a directory based on a userform entry (surname and then the customers ref) (TextBox1 & TextBox2)
This is called from a command button which then calls the word template and applies the input from the userform to the correct bookmark and save the doc to the correct directory which has been created. It then closes the doc then word and opens the created directory for the user to view the created files.
I have several documents that i would like to apply the same information to at the requisite bookmarks but i am not wanting to duplicate the code for each document. Is there away that when finished with doc 1 that i can call doc 2, doc 3 etc and apply the same code. Mu thinking is an array and a loop but unsure of the syntax. My code so far is below.
Macro 1
MKDir checks for and if necessary creates a directory based on a userform entry (surname and then the customers ref) (TextBox1 & TextBox2)
This is called from a command button which then calls the word template and applies the input from the userform to the correct bookmark and save the doc to the correct directory which has been created. It then closes the doc then word and opens the created directory for the user to view the created files.
I have several documents that i would like to apply the same information to at the requisite bookmarks but i am not wanting to duplicate the code for each document. Is there away that when finished with doc 1 that i can call doc 2, doc 3 etc and apply the same code. Mu thinking is an array and a loop but unsure of the syntax. My code so far is below.
Code:
Private Sub CommandButton1_Click()
'=====================================================================================================================
'Checks for and Creates a Directory and saves copy of template correctly
'=====================================================================================================================
Call MKDir
'Declare obj variables for the word application and document.
Dim wdApp As Object, wdDoc As Object
'Declare a String variable for the example document's name and folder path.
Dim strDocName As String
'On Error statement if Word is not already open.
Const strParent = "C:\Users\desmo\Desktop\latest\Archive"
Dim strFolderName As String
Dim strSurname As String
Dim strNino As String
Dim strFilePath As String
Dim Partner As String
Dim Clmt As String
Dim Couple As String
'Get Surname from control
strSurname = Me.Surname
' Get nino from from control
strNino = Me.Nino
strFolderName = strSurname & " " & strNino
'MsgBox strFolderName
strFilePath = strParent & strFolderName & ""
MsgBox " Creating your files in " & strFilePath
On Error Resume Next
'Activate Word if it is already open.
Set wdApp = GetObject(, "Word.Application")
If Err.Number = 429 Then
Err.Clear
'Create a Word application if Word is not already open.
Set wdApp = CreateObject("Word.Application")
End If
'Make sure the Word application is visible.
wdApp.Visible = True
'Define the strDocName String variable.
'strDocName = "\\DFZ70406.link2.gpn.gov.uk\70805003$\OPTool 191017\op.dotx"
strDocName = "C:\Users\desmo\Desktop\latest\op.dotx"
'Check the directory for the presence of the document name in the folder path.
'If it is not recognized, inform the user and exit the macro.
If Dir(strDocName) = "" Then
MsgBox "The file OP" & vbCrLf & _
"was not found in the folder path" & vbCrLf & _
"\\DFZ70406.link2.gpn.gov.uk\70805003$\OPTool 191017\op.", _
vbExclamation, _
"Sorry, that document name does not exist."
End If
'Create the Word document from the template.
Set wdDoc = wdApp.Documents.Add(strDocName)
'Variable values to determine if couple or not for decision completion
Clmt = ComboBox1.Value & " " & Surname.Value
Partner = " " & ComboBox2.Value & " " & PSurname.Value
Couple = Clmt & " " & Partner
With wdDoc
.Bookmarks("Title").Range.Text = ComboBox1.Value
.Bookmarks("Forename").Range.Text = Forename.Value
.Bookmarks("Surname").Range.Text = Surname.Value
.Bookmarks("Nino").Range.Text = Nino.Value
.Bookmarks("doc").Range.Text = claimDate.Value
.Bookmarks("apStart").Range.Text = apStart.Value
.Bookmarks("apEnd").Range.Text = apEnd.Value
.Bookmarks("iPyt").Range.Text = IPyt.Value
.Bookmarks("Title1").Range.Text = ComboBox1.Value
.Bookmarks("iDate").Range.Text = iDate.Value
.Bookmarks("CPyt").Range.Text = cPyt.Value
.Bookmarks("CPytDate").Range.Text = cPytDate.Value
.Bookmarks("iDate1").Range.Text = iDate.Value
.Bookmarks("iPyt1").Range.Text = IPyt.Value
.Bookmarks("Title2").Range.Text = ComboBox1.Value
.Bookmarks("Surname1").Range.Text = Surname.Value
.Bookmarks("Surname2").Range.Text = Surname.Value
.Bookmarks("apStart1").Range.Text = apStart.Value
.Bookmarks("apEnd1").Range.Text = apEnd.Value
.Bookmarks("iPyt2").Range.Text = IPyt.Value
.Bookmarks("Title3").Range.Text = ComboBox1.Value
.Bookmarks("Surname3").Range.Text = Surname.Value
.Bookmarks("AddressL1").Range.Text = AddressL1.Value
.Bookmarks("AddressL2").Range.Text = AddressL2.Value
.Bookmarks("City").Range.Text = City.Value
.Bookmarks("PCode").Range.Text = Pcode.Value
.Bookmarks("PTitle").Range.Text = ComboBox2.Value
.Bookmarks("PForename").Range.Text = PForename.Value
.Bookmarks("PSurname").Range.Text = PSurname.Value
.Bookmarks("PNino").Range.Text = PNino.Value
.Bookmarks("PTitle1").Range.Text = ComboBox2.Value
.Bookmarks("PForename1").Range.Text = PForename.Value
.Bookmarks("PSurname1").Range.Text = PSurname.Value
If CheckBox1.Value = True Then
.Bookmarks("Clmts").Range.Text = Couple
Else
.Bookmarks("Clmts").Range.Text = Clmt
End If
End With
wdDoc.SaveAs strFilePath & "op.doc"
wdDoc.Close True
'Release system memory that was reserved for the two Object variables.
Set wdDoc = Nothing
Set wdApp = Nothing
'Close Word Doc
Call CloseWordDocuments
'Open created Folder
Shell "explorer.exe " & strFilePath, vbNormalFocus
End Sub
Last edited by a moderator:
