Create Word document and center title

[judgejustin]

I have gotten a script to work on every point I need except one. I have tried everyway I can think of to center a couple of lines of data in a new word document that is created by pulling over information out of an excel sheet where I'm running the script from.
In the title and data, which I am classifying as paragraph 1 and 2 in the code I need them centered. I can get them formatted correctly but not centered and I've been at this for days. Can someone offer any suggestions?
Thanks

Sub ExportToWord()
    Dim wdApp As Object
    Dim wdDoc As Object
    Dim summaryWs As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim userDate As String
    Dim excelFilePath As String
    Dim wordFilePath As String

    ' Prompt the user for the date
    userDate = InputBox("Enter the date for the meeting (e.g., May 31, 2024):", "Date Input")

    ' Get the file path of the current Excel workbook
    excelFilePath = ThisWorkbook.Path
    wordFilePath = excelFilePath & "\Agenda " & userDate & ".docx"

    ' Set the summary sheet
    Set summaryWs = ThisWorkbook.Sheets("Summary")
    lastRow = summaryWs.Cells(summaryWs.Rows.Count, "A").End(xlUp).Row

    ' Create a new Word application
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If wdApp Is Nothing Then
        Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0

    ' Create a new Word document
    Set wdDoc = wdApp.Documents.Add

    ' Add title and date to the Word document
    With wdDoc.Content
        .InsertAfter "The Development Group Staff Meeting Agenda" & vbCrLf & userDate & vbCrLf
    End With

    ' Ensure the title and date are centered
    With wdDoc.Paragraphs(1).Range
        .ParagraphFormat.Alignment = wdAlignParagraphCenter 'Center alingnment
    End With
    With wdDoc.Paragraphs(2).Range
        .ParagraphFormat.Alignment = wdAlignParagraphCenter
    End With

    ' Bold, format title
    With wdDoc.Paragraphs(1).Range
        .Font.Name = "Calibri"
        .Font.Size = 20
        .Font.Bold = True
        .ParagraphFormat.SpaceAfter = 0 ' Remove space after title
    End With

    ' Bold, format date
    With wdDoc.Paragraphs(2).Range
        .Font.Name = "Calibri"
        .Font.Size = 14
        .Font.Bold = True
        .ParagraphFormat.SpaceBefore = 0 ' Remove space before date
    End With

    ' Initialize loop variables
    i = 1

    ' Copy data from summary sheet to Word with specific formatting
    Do While i <= lastRow
        ' Check for header in column A
        If summaryWs.Cells(i, 1).Value <> "" Then
            ' Add header line from column A
            wdDoc.Content.InsertAfter summaryWs.Cells(i, 1).Value & vbCrLf

            ' Apply formatting to the last added paragraph (header)
            With wdDoc.Paragraphs(wdDoc.Paragraphs.Count - 1).Range
                .Font.Name = "Calibri"
                .Font.Size = 12
                .Font.Bold = True
                .Font.Underline = True
                .ParagraphFormat.SpaceBefore = 0 ' Remove space before header
                .ParagraphFormat.SpaceAfter = 0 ' Remove space after header
                .ParagraphFormat.Alignment = wdAlignParagraphLeft ' Left alignment
                .ParagraphFormat.LeftIndent = 0 ' No indent for headers
            End With
        End If

        ' Check for bullets in column B
        If summaryWs.Cells(i, 2).Value <> "" Then
            Do While summaryWs.Cells(i, 2).Value <> "" And i <= lastRow
                With wdDoc.Paragraphs.Add.Range
                    .Text = summaryWs.Cells(i, 2).Value & vbCrLf
                    .ListFormat.ApplyBulletDefault
                    With .Font
                        .Name = "Calibri"
                        .Size = 12
                        .Bold = False
                        .Underline = False
                    End With
                    .ParagraphFormat.SpaceBefore = 0 ' Remove space before bullet
                    .ParagraphFormat.SpaceAfter = 0 ' Remove space after bullet
                End With
                i = i + 1
            Loop
            i = i - 1 ' Adjust the outer loop to continue after the last bulleted item
        End If

        i = i + 1
    Loop

    ' Check for any remaining bullets in column B after the last header
    i = lastRow + 1
    Do While summaryWs.Cells(i, 2).Value <> ""
        With wdDoc.Paragraphs.Add.Range
            .Text = summaryWs.Cells(i, 2).Value & vbCrLf
            .ListFormat.ApplyBulletDefault
            With .Font
                .Name = "Calibri"
                .Size = 12
                .Bold = False
                .Underline = False
            End With
            .ParagraphFormat.SpaceBefore = 0 ' Remove space before bullet
            .ParagraphFormat.SpaceAfter = 0 ' Remove space after bullet
        End With
        i = i + 1
    Loop

    ' Save the Word document
    On Error Resume Next
    wdDoc.SaveAs2 Filename:=wordFilePath, FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
    On Error GoTo 0

    ' Show the Word application
    wdApp.Visible = True

    MsgBox "Data exported to Word and saved as '" & "Agenda " & userDate & ".docx'!", vbInformation
End Sub

 

[Trevor G]

You could try recording a macro in Word to record justifying to the centre and any additional formatting that then will give you the additional code with slight adjustment to run from Excel. Once recorded use the macro option and edit the code.

 

[judgejustin]

Unfortunatly I had tried this. All I can get it a selection and paragraphformat to center. I have tried to every combo I can of using it and I cannot get anything to center the first 2 lines of this document.

 

[Tetra201]

Try using 1 instead of wdAlignParagraphCenter -- it's a Word VBA constant.

 

[judgejustin]

That worked perfectly. I have never really done much with word, it's always excel exclusively and I did not know about the constants. Thank you very much!!!

 

[Tetra201]

Glad to have helped.