Consulting

Results 1 to 4 of 4

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Joined
    Jun 2010
    Posts
    12
    Location

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

    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
    Last edited by Aussiebear; 08-21-2024 at 05:05 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •