Formatting in Excel and exporting to Word using VBA

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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Forum statistics

Threads
1,224,586
Messages
6,179,730
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top