Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Need to tweak macro to export from Excel to Word (Excel 2002 XP SP2)

    Hi there. I found a macro on the web which almost does what I need, I think just one command needs tweaking.

    It copies the contents of a specified range in Excel and pastes the contents into a new Word document. It's great except that it copies these contents into Word as a formatted table; I need the contents copied into Word as plain text.

    Here is the macro. How do I modify it? I'd be grateful for any help.

    Regards,
    Lingyai

    --------------------------

    Sub ExportToWord()

    Dim wdApp As Object
    Dim wd As Object

    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err.Number <> 0 Then
    Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0

    Set wd = wdApp.Documents.Open("C:test.doc")

    wdApp.Visible = True

    Range("A1:a19").Copy
    'My guess is that here is where I need to make the chnage...
    wd.Range.Paste

    End Sub

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

    Re: Need to tweak macro to export from Excel to Word (Excel 2002 XP SP2)

    Change
    <code>
    wd.Range.Paste
    </code>
    to
    <code>
    wd.Range.PasteSpecial DataType:=2
    </code>
    (2 is the numeric value of the symbolic constant wdPasteText)

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need to tweak macro to export from Excel to Wo

    Hi Lingyai

    How about this snippet:

    wd.Range.PasteSpecial DataType:=wdpastetext
    Jerry

  4. #4
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Re: Need to tweak macro to export from Excel to Word (Excel 2002 XP SP2)

    Before posting this, I knew two things with certainty -- that there would be a simple, elegant answer, and that it would come from HansV. I raise my glass to you, sir (though just about any excuse for glass raising will do). Best wishes for 2008!

    Lingyai

  5. #5
    2 Star Lounger
    Join Date
    Jan 2007
    Posts
    123
    Thanks
    13
    Thanked 1 Time in 1 Post

    Re: Need to tweak macro to export from Excel to Wo

    Thanks, and holiday wishes to you too Mr. J.

Posting Permissions

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