Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: VBA Export from Excel to Word

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Export from Excel to Word

    Hello I am trying to use this code to export data from excel to a pre-existing word table. I am a novice in this area. Pls help! Regards, Fane


    I tried to write the code, but I have a Compile error (user-defined type...):


    Code:
    Sub ExportDataWordTable()
    
    
    Const stWordDocument As String = "Template fisa de esantionare var.4.docm"
    
    
    Dim wdApp As Word.Application
    
    
    Dim wdDoc As Word.Document
    
    
    Dim wdCell As Word.Cell
    
    
    Dim i As Long
    Dim j As Long
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim vaData As Variant
    
    
    Set wbBook = ThisWorkbook
    Set wsSheet = wbBook.Worksheets("Sheet1")
    
    
    ReDim vaData(1 To 10, 1 To 8)
    
    
    With wsSheet
        vaData = .Range("A2:H11")
    End With
    
    
    'Here we instantiate the new object.
    Set wdApp = New Word.Application
    
    
    'Here the target document resides in the same folder as the workbook.
    Set wdDoc = wdApp.Documents.Open(wbBook.Path & "" & stWordDocument)
    
    
    'Import data to the first table and in the first column of a table in Microsoft Word.
    For j = 1 To 8
    i = 0
    
    
    For Each wdCell In wdDoc.Tables(1).Columns(j).Cells
        i = i + 1
        wdCell.Range.Text = vaData(i, j)
        Next wdCell
    
    
    Next j
    
    
    'Save and close the document.
    With wdDoc
    .Save
    .Close
    End With
    
    
    'Close the hidden instance of Microsoft Word.
    wdApp.Quit
    
    
    'Release the external variables from the memory
    Set wdDoc = Nothing
    Set wdApp = Nothing
    MsgBox "The data has been transferred to Template fisa de esantionare var.4", vbInformation
    End Sub
    Last edited by Macropod; Nov 8th, 2018 at 04:21 PM. Reason: Added code tags

  2. #2
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    2,947
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Export from Excel to Word

    Your code uses early binding. Have you set a VBA reference to the Word Object Library - via Tools|References in the VBE?

    PS: Please don't hijack existing threads with unrelated questions; I've moved your post to a thread of its own.

    PPS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Member
    Join Date
    Nov 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Export from Excel to Word

    Quote Originally Posted by Macropod View Post
    Your code uses early binding. Have you set a VBA reference to the Word Object Library - via Tools|References in the VBE?

    PS: Please don't hijack existing threads with unrelated questions; I've moved your post to a thread of its own.

    PPS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had.
    Hi Paul,
    Thanks for you reply!
    Yes, I made the changes to VBA Reference.
    PS/PPS: Sorry for my mistakes, I'm new at this😊

  4. #4
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    2,947
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Export from Excel to Word

    Quote Originally Posted by Fanel View Post
    Yes, I made the changes to VBA Reference.
    So, is the problem solved> If not, at which line is the code reporting that error?
    Cheers
    Paul Edstein
    [MS MVP - Word]

  5. #5
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,393
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA Export from Excel to Word

    Maybe it's just a typo in the posting, but the backslash is missing after the path, should be:
    Code:
    Set wdDoc = wdApp.Documents.Open(wbBook.Path & "\" & stWordDocument)
    Vladimir Zakharov
    Microsoft MVP Office Apps & Services

  6. #6
    Moderator Macropod's Avatar
    Join Date
    Aug 2007
    Location
    Canberra, Australia
    Posts
    2,947
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Export from Excel to Word

    Quote Originally Posted by ZVI View Post
    Maybe it's just a typo in the posting, but the backslash is missing after the path, should be:
    Code:
    Set wdDoc = wdApp.Documents.Open(wbBook.Path & "\" & stWordDocument)
    Hard to say - the board software tends to swallow those when pasted into a post.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  7. #7
    MrExcel MVP ZVI's Avatar
    Join Date
    Apr 2008
    Location
    Sevastopol
    Posts
    3,393
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA Export from Excel to Word

    Quote Originally Posted by Macropod View Post
    Hard to say - the board software tends to swallow those when pasted into a post.
    Hi Paul,

    Yes, it seems that case.
    Strange behavior indeed as it does not look like the html tag.
    Even posted formulas prepared by HTML maker tool has similar problems.

    Vlad
    Vladimir Zakharov
    Microsoft MVP Office Apps & Services

  8. #8
    New Member
    Join Date
    Nov 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Export from Excel to Word

    Hi, the problem is not solved
    I will come back with more details...Tks

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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