bwmustang13
New Member
- Joined
- Aug 22, 2014
- Messages
- 9
I am trying to export an excel table into a new word document. The excel data I am trying to export is dynamic inputs that each user can select and VBA will export as string values. I have three columns B,C and D. The header row is on row two and the cells go down to row 80. I tried using a conditional statement to format the way the each cell is exported but instead the macro just keeps applying whatever the last conditional statement says. For example if “None” is selected in column D I want the fields to be exported with a 12pt text but without a bold font. Instead it currently applies 16pt text with bold font. I know it has something to do with how I defined my document I just can’t figure out what it is. Please let me know what I am doing wrong or if there is a more efficient way of writing the code.</SPAN>
Code:
Sub main()
Dim objWord As Object
Dim objDoc As Object
Dim cell As Object
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objDoc = objWord.Documents.Add
'Optimize Code
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim i As Integer
Dim strValue As String
For i = 2 To 51
objDoc.Activate
strValue = Cells(i + 1, 2) & " " & Cells(i + 1, 3)
objWord.Selection.TypeText Text:=strValue
If Cells(i + 1, 4) = "None" Then
With objDoc
.Content.Font.Name = "Times New Roman"
.Content.Font.Size = 12
.Content.Font.Bold = True
.Content.InsertParagraphAfter
.Content.InsertParagraphAfter
End With
ElseIf Cells(i + 1, 4) = "Bold" Then
With objDoc
.Content.Font.Name = "Times New Roman"
.Content.Font.Size = 12
.Content.Font.Bold = True
.Content.InsertParagraphAfter
.Content.InsertParagraphAfter
End With
Else: Cells(i + 1, 4) = "Heading Style 1"
With objDoc
.Content.Font.Name = "Times New Roman"
.Content.Font.Size = 16
.Content.Font.Bold = True
.Content.InsertParagraphAfter
.Content.InsertParagraphAfter
End With
End If
objWord.Selection.TypeParagraph
Next i
'Optimize Code
Application.ScreenUpdating = True
Application.EnableEvents = True
'Clear The Clipboard
Application.CutCopyMode = False
End Sub