Creating MS Word Document with VBA from Excel and Setting Page Orientation

[pklocke]

Hello! I guess I thought this would be easier so I must be missing something right in front of my nose (and am pretty nooby, so bear with me).

I have an excel file (think address list, like Name, Address Street, Phone, etc) that I want to move to a two-column MS Word document, landscape format (so it can be folded like a book). I have the code (below), that I thought should do it, but it does not. It does create the Word doc, set the columns to 2, setting line color, etc, but the Landscape code is not working. What am I missing? Note: the VBA Code is in the excel side, not word)

Thanks for any suggestions!

Sub generate_word_doc()
    Dim objWord
    Dim objDoc
    Dim objSelection
    Dim i, j As Integer
    Dim ws As Worksheet
    Dim row_count, col_count As Integer
    Set ws = ThisWorkbook.Sheets("Roster")
    ws.Activate
    row_count = WorksheetFunction.CountA(Range("C1", Range("C1").End(xlDown)))
    col_count = WorksheetFunction.CountA(Range("C1", Range("C1").End(xlToRight)))
    Set objWord = CreateObject("Word.Application")
    Set objDoc = objWord.Documents.Add
    Set objSelection = objWord.Selection
    objWord.Visible = True
    objWord.Activate
    With objDoc
         .PageSetup.Orientation = wdOrientLandscape                           '<---This is not working
         .PageSetup.TextColumns.SetCount NumColumns:=2                 '<---this DOES work
         .PageSetup.LeftMargin = 18
         .PageSetup.MirrorMargins = True
    End With
    'Ignore below this line...still WIP
    Set RosterTable = objDoc.Tables.Add(objSelection.Range, row_count, col_count)
    With RosterTable
         With .Borders
             .enable = True
             .outsidecolor = RGB(0, 0, 0)
             .insidecolor = RGB(0, 0, 0)
         End With
         .Rows(1).shading.backgroundpatterncolor = RGB(221, 221, 221)
        For i = 0 To row_count
            For j = 1 To col_count
                .cell(i, j).Range.InsertAfter ws.Cells(i + 2, j + 2).Text
            Next j
        Next i
    End With
End Sub

[Paul_Hossler]

Try this. My guess is that Excel doesn't know the MS Word enumerations, i.e. wdOrientLandscape

    .PageSetup.Orientation = 1

[pklocke]

using the approach to

    .PageSetup.Orientation = 1

Did not work. BUT, I tried this...in upper code i created:

Const wdOrientLandscape = 1

I then used the constant instead:

    .PageSetup.Orientation = wdOrientLandscape

And that did the trick. Thank you for the pointer. I do not understand, still, why simply using your suggestion is "different" than setting the CONST.

[Aflatoon]

There is absolutely no difference between the two (though I prefer the Const approach anyway as it's more readable). There must have been something else going on.