Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    Australia
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro to Paste excel data to Word doc. (Office 2000)

    I am trying to develop a Macro to Paste excel data ( Table) to the end of a Word document. I am using the code "Selection. EndKey Unit:=wdstory", but it does not accept the code. Can anyone help? Full VBA code attached.
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Macro to Paste excel data to Word doc. (Office 2000)

    Forgive the obvious question, but: where are you trying to run this code from - Excel or Word? (It should be run from Excel, but you've attached a Word document.)
    Assuming you've got it in Excel, have you set a reference to the Word type library?
    Do you get an error message when you try to compile the code, or at runtime?

    Gary

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Location
    Australia
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Paste excel data to Word doc. (Office 2000)

    Hi Gary,
    I am running the Macro from Excel, I don't think I have set a reference to the Word type Library, how do I do this?
    The error message occurs when I try to run the program.
    PS. I have copied the code to a Word document so that I could attach it to my posted query, could I have done this another way?

  4. #4
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Macro to Paste excel data to Word doc. (Office 2000)

    Hi again,

    I've just gotten your code to run fine by making a few minor fixes.
    First off, to set a reference to Word from Excel: in the VB Editor while in Excel, click on Tools > References, and scroll down the list until you locate "Microsoft Word 9.0 Object Library" (the number will be 8.0 if you're using Office 97 and will be 10.0 if you're using Office 2002). Put a checkmark next to that entry and click on the OK button.

    The other things that need fixing in the code are:
    If you want to see the Word app onscreen, add "wdApp.Visible = True"
    Remove the comment mark before "With wdApp" and "Documents.Open...etc."
    Add an "End With" to correspond to the "With wdApp".
    Lastly: almost certainly, "C:MyDocuments" is not a valid path - at least not on Word 2000 or later - I've substituted "C:Temp" here, but you need to confirm that you are providing the full path for the document to open.

    The code would then look like this (and should work):

    <pre>Sub CopyChartToWordDocument()
    Dim wdApp As Word.Application
    'Copy embedded array
    ThisWorkbook.Sheets(1).Range("A1:G12").Copy
    'Establish link to Word
    Set wdApp = New Word.Application
    wdApp.Visible = True
    With wdApp
    'open Word Document
    Documents.Open Filename:="C:TempTime.doc"
    With wdApp.Selection
    'Go to end of document and insert paragraph
    .EndKey Unit:=wdStory
    .TypeParagraph
    .PasteSpecial Link:=False, DataType:=wdPasteOLEObject, Placement:=wdInLine, DisplayAsIcon:=False

    End With
    End With
    'Release object variable
    Set wdApp = Nothing
    End Sub
    </pre>

    Gary

  5. #5
    New Lounger
    Join Date
    Nov 2001
    Location
    Australia
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Paste excel data to Word doc. (Office 2000)

    Hi Gary, Quick work, much appreciated, I will give it a try.
    Many thanks
    Brian S. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  6. #6
    New Lounger
    Join Date
    Nov 2001
    Location
    Australia
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Paste excel data to Word doc. (Office 2000)

    Hi Gary,
    Your solution worked fine, many thanks. Just to refine this a little more, can you tell me how I can add code to de-select the range copied from the excel worksheet?

  7. #7
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Macro to Paste excel data to Word doc. (Office 2000)

    C'mon Ozzie Brian - thats a question for the Excel lounge. Those guys love that kind of mental exercise.

    But since you are already here how does this sound
    Range("A1").Select

    You will notice that Gary's code did not specifically select any cells. The question we have to ask is when the code deselects the selection, where do you want it to go?
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  8. #8
    New Lounger
    Join Date
    Nov 2001
    Location
    Australia
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Paste excel data to Word doc. (Office 2000)

    Hi Andrew,
    I am quite embarrassed by the fact that I inadvertently broke the rules and multi posted this query. The fact is, I did not receive any comments from the Excel Forum, so I thought I would try the other loungers as my query involved interaction between Office applications. I have had my wrist slapped for my ill-doings <img src=/S/hiding.gif border=0 alt=hiding width=70 height=24> !!
    Back to my problem. I would like to finish with the Word doc. active, but I would like the Excel Table to be deselected. As you can see, I am a bit of a novice about VBA, but I am trying!
    Thanks for your input, if you can provide any further advice, it would be most appreciated.

  9. #9
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Macro to Paste excel data to Word doc. (Office 2000)

    <hr>I would like the Excel Table to be deselected<hr>
    After the .PasteSpecial include the line
    <pre> .Selection.Collapse wdCollapseEnd
    </pre>


    StuartR

  10. #10
    New Lounger
    Join Date
    Nov 2001
    Location
    Australia
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Paste excel data to Word doc. (Office 2000)

    Thanks Stuart, a simple solution to a simple problem!!

  11. #11
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to Paste excel data to Word doc. (Office 2000)

    Ozzie,

    No wrist-slapping necessary.

    If anyone REALLY not sure where to post, I'd suggest they try a Private Mail to a mod first. But if you're running out of time, I'd suggest that anyone who is not sure of where to post, post in the two places, but mention in both posts that it's cross-posted elsewhere. That way, we Mods can keep an eye on where responses are posted, and lock the one which isn't getting a response.

    But we have a good tolerance for newbies. Now, if you were a Silver Lounger doing the same, that might not ge treated the same <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

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