VBA Export from Excel to Word

Fanel

New Member
Joined
Nov 8, 2018
Messages
27
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 a moderator:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
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?
 
Upvote 0
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 & "[COLOR=#ff0000][B]\[/B][/COLOR]" & stWordDocument)
 
Upvote 0
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 & "[COLOR=#ff0000][B]\[/B][/COLOR]" & stWordDocument)
Hard to say - the board software tends to swallow those when pasted into a post.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top