Linking word and excel

[Dora16]

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.

 

[AlphaFrog]

Can you post your code?

 

[Dora16]

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

 

[Dora16]

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]

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.

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