Consulting

Results 1 to 4 of 4

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

  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.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,787
    Location
    Try this. My guess is that Excel doesn't know the MS Word enumerations, i.e. wdOrientLandscape



        .PageSetup.Orientation = 1
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Jun 2010
    Posts
    12
    Location
    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.

  4. #4
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,779
    Location
    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.
    Be as you wish to seem

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
  •