Consulting

Results 1 to 6 of 6

Thread: Solved: Macro wont run except...

  1. #1
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,032
    Location

    Solved: Macro wont run except...

    This is a strange problem. I am trying to run the code below from the menu on the worksheet. If I do it that way it errors out on the .Documents.Add line. However, if you stay in the VBE and step through it using F8 it works.

    [VBA] Option Explicit
    Sub CopyTableToAnyWordDocument()
    Dim wdApp As Word.Application
    ThisWorkbook.Sheets("Table").Range("A1:B6").Copy
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If wdApp Is Nothing Then
    Set wdApp = GetObject(" ", "Word.Application")
    End If
    On Error GoTo 0
    With wdApp
    .Documents.Add
    .Visible = True
    End With
    With wdApp.Selection
    .EndKey Unit:=wdStory
    .TypeParagraph
    .Paste
    End With

    Set wdApp = Nothing

    End Sub
    [/VBA]

    I have attached the workbook also.
    Peace of mind is found in some of the strangest places.

  2. #2
    Site Admin
    Jedi Master
    VBAX Guru Jacob Hilderbrand's Avatar
    Joined
    Jun 2004
    Location
    Roseville, CA
    Posts
    3,712
    Location
    Looks like you are confusing Early and Late binding here. This should work for you.

    [vba]
    Option Explicit

    Sub CopyTableToAnyWordDocument()

    Dim wdApp As Word.Application

    ThisWorkbook.Sheets("Table").Range("A1:B6").Copy
    Set wdApp = New Word.Application
    With wdApp
    .Documents.Add
    .Visible = True
    End With
    With wdApp.Selection
    .EndKey Unit:=wdStory
    .TypeParagraph
    .Paste
    End With

    Set wdApp = Nothing

    End Sub
    [/vba]

  3. #3
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Try
    [VBA]
    If wdApp Is Nothing Then
    Set wdApp = CreateObject("Word.Application")
    End If
    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  4. #4
    Moderator VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,032
    Location
    Both work..Thanks DRJ, MD Solved. Although I never could really grasp the Early and Late Binding concept. Can anyone briefly expalin. Thanks
    Peace of mind is found in some of the strangest places.

  5. #5
    VBAX Mentor Justinlabenne's Avatar
    Joined
    Jul 2004
    Location
    Clyde, Ohio
    Posts
    408
    Location
    How about here & here
    Justin Labenne

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    Quote Originally Posted by austenr
    Both work..Thanks DRJ, MD Solved. Although I never could really grasp the Early and Late Binding concept. Can anyone briefly expalin. Thanks
    It is not necessarily anything to do with early/late-binding. CreateObject and GetObject work equally well with both. Binding referes to when the type library is bound to the application, early - before compilation, or late - at run time.

Posting Permissions

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