Results 1 to 13 of 13
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Open Word from macro problem... (Excel XP)

    Hi all,

    A client of mine sent this to me for assistance, but I cant seem to get it working myself...any ideas why the code is debugging where indicated?

    We Run a BEx report from excel but cannot paste a range of data (Material Numbers) to the bex range. If You copy it to Word, and copy it from word back it works
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Word from macro problem... (Excel XP)

    Do you have a reference to the Microsoft Word 10.0 Object Library in Tools | References...? (10.0 is the version number of Office XP)

    There is no good reason to declare WordObj as Static, since it is set to Nothing each time. Better to use Dim instead of Static, and to set WordObj to Nothing at the end of the procedure.

    Also, the use of Selection without qualification is dangerous. You should use WordObj.Selection.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Word from macro problem... (Excel XP)

    I asked the client about that referece, and he said he does have it activated. However, I will just ensure it again from him.

    Is this code a little better based on your suggersions?

    Sub OpenWordDoc()
    Dim WordObj As Word.Application
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Word from macro problem... (Excel XP)

    If you still get an error on the line

    Dim WordObj As Word.Application

    the reference to the Word library must be missing or incorrect.

    The code doesn't paste anything in Word. Frankly, I don't understand the purpose of this sequence:

    Open Word Doc
    Paste
    Select the range in Word
    Copy

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Word from macro problem... (Excel XP)

    Thx...I will inform him to check if the referece is available and correct.

    The rest of the code works apparently for the client. They just have a problem with that first line.

    Tx for the help. Will post back to let you know what happened!
    Cheers
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Word from macro problem... (Excel XP)

    Here is the code so far...

    Sub R_OpenWordDoc()
    Dim WordObj As Word.Application
    Set WordObj = CreateObject("Word.Application")
    Selection.Copy
    WordObj.Documents.Open ("C:APO.doc") ' Set my own directory to open Word File
    ' Select the range to be copied from Excel (Select any Range with no 1 to 10 down in cell range)
    'Enable viewing the Word session and its document
    WordObj.Visible = True
    ' After the Word application is activated, Paste the content from excel
    WordObj.Selection.PasteExcelTable False, False, False
    ' Copy all data in Word
    WordObj.Selection.WholeStory
    WordObj.Selection.Copy
    ' Now it is available to paste
    Set WordObj = Nothing
    End Sub

    When the bolded line runs, the macro just quits???? Why? There is no quit command. It pastes the copied items into Word, and then quits. I need it to continue and select the WholeStory and then switch back to Excel to paste the data again.

    Any Ideas?

    Tx
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Word from macro problem... (Excel XP)

    The code runs without problems on my PC (with the name of an existing document substituted for C:APO.doc), so apparently there is something wrong with the user's installation. See if Help | Detect and Repair (in Word and in Excel) helps. Also, quit Word and Excel, then select Start | Run..., type
    <code>
    regsvr32 ole32.dll
    </code>
    and press Enter or click OK.

    (BTW what is the purpose of copying a range in Excel, pasting it into a Word document, then copying and pasting back into Excel?)

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Word from macro problem... (Excel XP)

    >>> The code runs without problems on my PC Awesome...Thats great to know...then its nearly there.
    I will try your advice about detect and repair.

    >>>(BTW what is the purpose of copying a range in Excel, pasting it into a Word document, then copying and pasting back into Excel?)
    See <post:=592,433>post 592,433</post:> The client explains this: We Run a BEx report from excel but cannot paste a range of data (Material Numbers) to the bex range. If You copy it to Word, and copy it from word back it works
    Regards,
    Rudi

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Word from macro problem... (Excel XP)

    I saw that, but I don't know what a BEx report is. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Word from macro problem... (Excel XP)

    Actually I did not even ask myself?? No idea too!

    PS: BTW....the reference in VBA for Microsoft Word 11.0 was not checked....hense the original problem in the code. Office 11.0 was checked, but not Word 11.0!
    Tx for that!
    Regards,
    Rudi

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Word from macro problem... (Excel XP)

    Hi Hans,

    Could you help me with two code statements please:
    1. To get the code sorted out to also close the the word document after it copies the text
    2. To provide me the changes to the code to close the word application too (if required).

    Thx
    Regards,
    Rudi

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Word from macro problem... (Excel XP)

    Sub R_OpenWordDoc()
    Dim WordObj As Word.Application
    Dim WordDoc As Word.Document
    Set WordObj = CreateObject("Word.Application")
    Selection.Copy
    Set WordDoc = WordObj.Documents.Open("C:APO.doc")
    'Enable viewing the Word session and its document
    WordObj.Visible = True
    ' After the Word application is activated, Paste the content from excel
    WordObj.Selection.PasteExcelTable False, False, False
    ' Copy all data in Word
    WordObj.Selection.WholeStory
    WordObj.Selection.Copy
    ' Now it is available to paste
    ' Close document
    WordDoc.Close SaveChanges:=True ' or False
    ' Quit Word
    WordObj.Quit
    Set WordDoc = Nothing

    Set WordObj = Nothing
    End Sub

  13. #13
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Word from macro problem... (Excel XP)

    Tx again!

    PS:
    WOW - you really chewed through my posts this morning....it took you less than 10 min to answer all my Q's!
    Amazing!
    Regards,
    Rudi

Posting Permissions

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