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.