Blanchetdb
Board Regular
- Joined
- Jul 31, 2018
- Messages
- 161
- Office Version
- 2016
- Platform
- Windows
Hi,
I presently have coding that will create the word document but I was wondering if it is possible to have coding that will help with the formatting of the Word document and the removal of bookmarks that are no used (no information in the corresponding cell in excel)
the person fills information in specific cells in excel and the information is then moved to a word document at specific locations identified by bookmarks....
all cells in excel are not mandatory to be filled with information so that create gaps in the word document (unused bookmarks). I there a way to eliminate those gaps?
any help would be appreciated
thank you
I presently have coding that will create the word document but I was wondering if it is possible to have coding that will help with the formatting of the Word document and the removal of bookmarks that are no used (no information in the corresponding cell in excel)
the person fills information in specific cells in excel and the information is then moved to a word document at specific locations identified by bookmarks....
all cells in excel are not mandatory to be filled with information so that create gaps in the word document (unused bookmarks). I there a way to eliminate those gaps?
Code:
Private Sub CommandButton1_Click()
Dim wdApp As Word.Application
Dim theString As String
Dim theObject As Object
Set wdApp = New Word.Application
Set theObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
With wdApp
.Visible = True
.Activate
.Documents.Add "M:\HR\National Transfer Inventory\Indeterminate_Appointment_Template_.DOCX"
Sheet3.Activate
theString = Range("C5").Value 'cast to string
theObject.SetText theString
theObject.PutInClipboard 'copy from cell B2 to clipboard.
.Selection.Goto what:=-1, Name:="Incumbent"
.Selection.Paste 'paste from the clipboard to the Word Doc.
' several other lines of coding the same as above....then
If Range("A23") = "x" Then
theString = Worksheets("Sheet4").Cells(1, "A").Value 'cast to string
theObject.SetText theString
theObject.PutInClipboard 'copy from cell B2 to clipboard.
With .Selection
.Goto what:=-1, Name:="ManTraining"
.BoldRun
.Font.Size = 12
.Font.Name = "Arial"
.TypeText "MANDATORY TRAINING"
.BoldRun
.TypeParagraph
.Font.Name = "Arial"
.Font.Size = 12
.Paste 'paste from the clipboard to the Word Doc.
.TypeParagraph
End With
End If
If Range("A24") = "x" Then
theString = Worksheets("Sheet4").Cells(2, "A").Value 'cast to string
theObject.SetText theString
theObject.PutInClipboard 'copy from cell B2 to clipboard.
With .Selection
.Goto what:=-1, Name:="ManTraining"
.BoldRun
.Font.Size = 12
.Font.Name = "Arial"
.TypeText "MANDATORY TRAINING"
.BoldRun
.TypeParagraph
.Font.Name = "Arial"
.Font.Size = 12
.Paste 'paste from the clipboard to the Word Doc.
.TypeParagraph
End With
End If
' Hours of Work
If Range("A27") = "x" Then
theString = Worksheets("Sheet4").Cells(3, "A").Value 'cast to string
theObject.SetText theString
theObject.PutInClipboard 'copy from cell B2 to clipboard.
With .Selection
.Goto what:=-1, Name:="HoursWork"
.BoldRun
.Font.Size = 12
.Font.Name = "Arial"
.TypeText "HOURS OF WORK"
.BoldRun
.TypeParagraph
.Font.Name = "Arial"
.Font.Size = 12
.Paste 'paste from the clipboard to the Word Doc.
.TypeParagraph
End With
End If
If Range("A28") = "x" Then
theString = Worksheets("Sheet4").Cells(4, "A").Value 'cast to string
theObject.SetText theString
theObject.PutInClipboard 'copy from cell B2 to clipboard.
With .Selection
.Goto what:=-1, Name:="HoursWork"
.BoldRun
.Font.Size = 12
.Font.Name = "Arial"
.TypeText "HOURS OF WORK"
.BoldRun
.TypeParagraph
.Font.Name = "Arial"
.Font.Size = 12
.Paste 'paste from the clipboard to the Word Doc.
.TypeParagraph
End With
End If
If Range("A29") = "x" Then
theString = Worksheets("Sheet4").Cells(5, "A").Value 'cast to string
theObject.SetText theString
theObject.PutInClipboard 'copy from cell B2 to clipboard.
With .Selection
.Goto what:=-1, Name:="HoursWork"
.BoldRun
.Font.Size = 12
.Font.Name = "Arial"
.TypeText "HOURS OF WORK"
.BoldRun
.TypeParagraph
.Font.Name = "Arial"
.Font.Size = 12
.Paste 'paste from the clipboard to the Word Doc.
.TypeParagraph
End With
End If
If Range("A30") = "x" Then
theString = Worksheets("Sheet4").Cells(6, "A").Value 'cast to string
theObject.SetText theString
theObject.PutInClipboard 'copy from cell B2 to clipboard.
With .Selection
.Goto what:=-1, Name:="HoursWork"
.BoldRun
.Font.Size = 12
.Font.Name = "Arial"
.TypeText "HOURS OF WORK"
.BoldRun
.TypeParagraph
.Font.Name = "Arial"
.Font.Size = 12
.Paste 'paste from the clipboard to the Word Doc.
.TypeParagraph
End With
End If
If Range("A31") = "x" Then
theString = Worksheets("Sheet4").Cells(7, "A").Value 'cast to string
theObject.SetText theString
theObject.PutInClipboard 'copy from cell B2 to clipboard.
With .Selection
.Goto what:=-1, Name:="HoursWork"
.BoldRun
.Font.Size = 12
.Font.Name = "Arial"
.TypeText "HOURS OF WORK"
.BoldRun
.TypeParagraph
.Font.Name = "Arial"
.Font.Size = 12
.Paste 'paste from the clipboard to the Word Doc.
.TypeParagraph
End With
End If
If Range("A32") = "x" Then
theString = Worksheets("Sheet4").Cells(8, "A").Value 'cast to string
theObject.SetText theString
theObject.PutInClipboard 'copy from cell B2 to clipboard.
With .Selection
.Goto what:=-1, Name:="HoursWork"
.BoldRun
.Font.Size = 12
.Font.Name = "Arial"
.TypeText "HOURS OF WORK"
.BoldRun
.TypeParagraph
.Font.Name = "Arial"
.Font.Size = 12
.Paste 'paste from the clipboard to the Word Doc.
.TypeParagraph
End With
End If
If Range("A33") = "x" Then
theString = Worksheets("Sheet4").Cells(9, "A").Value 'cast to string
theObject.SetText theString
theObject.PutInClipboard 'copy from cell B2 to clipboard.
With .Selection
.Goto what:=-1, Name:="HoursWork"
.BoldRun
.Font.Size = 12
.Font.Name = "Arial"
.TypeText "HOURS OF WORK"
.BoldRun
.TypeParagraph
.Font.Name = "Arial"
.Font.Size = 12
.Paste 'paste from the clipboard to the Word Doc.
.TypeParagraph
End With
End If
End With
Set wdApp = Nothing
Set theObject = Nothing
End Sub
any help would be appreciated
thank you