Linking word and excel

Dora16

New Member
Joined
Jul 30, 2010
Messages
22
what I'm trying to do is I have a 2 column table in Excel that does all the math for me. What I want is for that table to automatically fill in into the table I have in Word. I did find one code that worked, but I need it to fill into a table that is in a three page letter. The code wiped out all that and placed just the Excel table. Not what I wanted.

Can anyone point me in the right direction?

Thanks.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Dora16

New Member
Joined
Jul 30, 2010
Messages
22
I tried this code first and did nothing. Maybe I'm just doing it wrong?

Sub Export_Data_Word_Table()
Dim wdApp as Word.Application
Dim wdDoc as word.Document
Dim wdCell as Word.Cell
Dim i As Long
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnData As Range
Dim vaData As Variant

Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Sheet1")

With wsSheet
Set rnData = .Range("A1:A10")
End With

'Add the values in the range to a one-dimensional variant-array.
vaData = rnData.Value

'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(ThisWorkbook.Path & "\Test.docx")

'Import data to the first table and in the first column of a ten-row table.
For Each wsCell In wdDoc.Tables(1).Columns(1).Cells
i = i + 1
wdCell.Range/Text = vaData(i, 1)
Next wdCell

'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 Test.docx", vbInformation

End Sub
 
Last edited:

Dora16

New Member
Joined
Jul 30, 2010
Messages
22
Here's the code I used that wipe out my entire letter:

Sub QuoteCommaExport()
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Integer
Dim RowCount As Integer

' Prompt user for destination file name.
DestFile = InputBox("Enter the destination filename" & _
Chr(10) & "(with complete path and extension):", _
"Quote-Comma Exporter")
' Obtain next free file handle number.
FileNum = FreeFile()

' Turn error checking off.
On Error Resume Next

' Attempt to open destination file for output.
Open DestFile For Output As #FileNum
' If an error occurs report it and end.
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
End
End If

' Turn error checking on.
On Error GoTo 0

' Loop for each row in selection.
For RowCount = 1 To Selection.Rows.Count
' Loop for each column in selection.
For ColumnCount = 1 To Selection.Columns.Count

' Write current cell's text to file with quotation marks.
Print #FileNum, """" & Selection.Cells(RowCount, _
ColumnCount).Text & """";
' Check if cell is in last column.
If ColumnCount = Selection.Columns.Count Then
' If so, then write a blank line.
Print #FileNum,
Else
' Otherwise, write a comma.
Print #FileNum, ",";
End If
' Start next iteration of ColumnCount loop.
Next ColumnCount
' Start next iteration of RowCount loop.
Next RowCount

' Close destination file.
Close #FileNum
End Sub


Thanks for any help - I'm flying blind in this. I have no idea what I'm doing, haha! I got asked to do this for work!
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,377
Your first code is pretty good. The one thing missing was you have to specify that you want the Word.App visible. I added that line of code (in red) below and made a few other minor changes.

The code assumes you have a "Table" in your word doc already. The Excel data will then be inserted in the existing Word Table.

Code:
Sub Export_Data_Word_Table()

    Dim FileToOpen As String
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim wdCell As Word.Cell
    Dim i As Long
    Dim rnData As Range
    Dim vaData As Variant
    
    Set rnData = Worksheets("Sheet1").Range("A1:A10")

    'Add the values in the range to a one-dimensional variant-array.
    vaData = rnData.Value
    
    FileToOpen = Application.GetOpenFilename _
                (Title:="Please select a Word file to open", _
                FileFilter:="MS-Word Files *.doc* (*.doc),")
    If FileToOpen = "False" Then Exit Sub 'User canceled
    
    'Open selected Word doc
    Set wdApp = New Word.Application
    [COLOR="Red"]wdApp.Visible = True[/COLOR]
    Set wdDoc = wdApp.Documents.Open(FileToOpen)
    
    'Import data to the first table and in the first column of a ten-row table.
    For Each wdCell In wdDoc.Tables(1).Columns(1).Cells
    i = i + 1
    wdCell.Range.Text = vaData(i, 1)
    Next wdCell
    
    MsgBox "The data has been transferred to " & wdDoc.Name, vbInformation
    
    '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
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

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
Top