CODEWITHSUNDEEP
vbams-word
frankbean
frankbean

Reputation: 35

Can page size and margins be set when sending data from Excel to Word?

I have code like the following:

Dim blnNewApp As Boolean
Dim wordApp As Object
Dim wordDoc As Object
Dim j As Long

Set wordApp = GetObject(, "Word.Application")

If wordApp Is Nothing Then
    Set wordApp = CreateObject("Word.Application")
    blnNewApp = True
End If

Set wordDoc = wordApp.Documents.Add()

With ThisWorkbook.Sheets(strSHEET_NAME)
    For j = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row

        strLineOfText = .Cells(j, "A").Text
    wordApp.Selection.TypeText strLineOfText
        wordApp.Selection.TypeParagraph

    Next j
End With

wordApp.Visible = True
AppActivate wordApp.Caption

If I want to set the page size and margins in the Word doc that gets created how would I do that?

Upvotes: 1

Views: 70

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25673

Page size and margins are properties of the PageSetup object. Note that this kind of information can often be discovered by recording a macro...

Since the code in the question uses late-binding, the numerical values for paper sizes will need to be used instead of the enumeration (wdPaperLetter for example) and the margin values will need to be passed in points.

I've adapted the code in the question to demonstrate. Also, I've made additional optimizations:

  • Work with the objects (espcially Range objects), rather than Selection (similar logic as avoiding ActiveCell and Activate in Excel - it's more accurate)
  • Use the Document object that's declared and 
    Dim blnNewApp As Boolean
    Dim wordApp As Object   'Word.Application
    Dim wordDoc As Object   'Word.Document
    Dim wordRange as Object 'Word.Range
    Dim j As Long

    Set wordApp = GetObject(, "Word.Application")

    If wordApp Is Nothing Then
        Set wordApp = CreateObject("Word.Application")
        blnNewApp = True
    End If

    Set wordDoc = wordApp.Documents.Add()
    Set wordRange = wordDoc.Content

    With ThisWorkbook.Sheets(strSHEET_NAME)
        For j = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row

            strLineOfText = .Cells(j, "A").Text
            wordRange.Text = strLineOfText & vbCr  'vbCr = a new paragraph
            With wordDoc.PageSetup
              .PaperSize = 2 'wdPaperLetter
              .BottomMargin = 452 'CentimetersToPoints(1.5)
              .TopMargin = 700 'CentimetersToPoints(2)
              .LeftMargin = 680 'CentimetersToPoints(2)
              .RightMargin = 680 'CentimetersToPoints(2)
            End With
        Next j
    End With

    wordApp.Visible = True
    AppActivate wordApp.Caption

Upvotes: 1

Related Questions