Copy and Paste table from word to excel code???

[jon321]

Hi i need a code to open a specific word document, copy table from word into excel with a link so when the word document is changed so is the excel doc
.. have tried some bits but have had no luck !!

please help

 

[Andrew Poulsom]

What have you tried? My approach to get started would be to record macros in Word and Excel while doing it manually.

 

[Andrew Poulsom]

Here is an example:

Sub Test()
    Const wdFileName As String = "P:\TEMP\MrExcel\TableToExcel.doc"
    Dim wdApp As Object
    Dim wdDoc As Object
    Dim Sh As Worksheet
    Set wdApp = CreateObject("Word.Application")
    Set wdDoc = wdApp.Documents.Open(wdFileName)
    Set Sh = Worksheets("Sheet1")
    Sh.Activate
    Sh.Range("A1").Select
    wdDoc.Tables(1).Select
    wdApp.Selection.Copy
    ActiveSheet.Paste Link:=True
    wdApp.Quit
End Sub

Change the FileName and Table/Sheet/Range references to suit.

 

[jon321]

ok this starts to work well... however i need it to find a specific table within the word doc... not just all tables as there is more than !.. is ther any way of doing this??

Thanks

 

[Andrew Poulsom]

My code selects the first table. To copy a different table change the index in Tables(1). I don't know how to find a "specific table" in Word.

 

[jon321]

Thanks andrew am a big step closer now.. the only added part which i need is to be able to add in the funtion of copying a table into word from excel, before it pastes into the other spreadsheet. i can get it to paste into the word document. but it adds a table in rather than pasting over/replacing the text in the existing table!!

Any suggestions.. your code up to that point is great..thanks for your time

 

[Andrew Poulsom]

Are you copying a table from Excel to Word and then from Word to Excel? Why not just Excel to Excel?

 

[jon321]

hard to explain... a user inputs info into excel.. which is copied to a word document...(there is currently 1000+ of these hence not being able to do it excel to excel)then the link is pasted back to the same excel sheet with a link... meaning that the system is automated in terms of name input and creates a link so that when ever a word document is changed after it has been created with this process.. my system recognises this change as the user changes an issue number on the word document.

 

[Andrew Poulsom]

This code copies an Excel range to an existing Word table, overwriting the previous contents:

Sub Test()
    Const wdFileName As String = "P:\TEMP\MrExcel\TableFromExcel.doc"
    Const wdLine As Integer = 5
    Const wdExtend As Integer = 1
    Const wdCharacter As Integer = 1
    Dim wdApp As Object
    Dim wdDoc As Object
    Dim Sh As Worksheet
    Dim Rng As Range
    Set wdApp = CreateObject("Word.Application")
    Set wdDoc = wdApp.Documents.Open(wdFileName)
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A1:C3")
    Rng.Copy
    wdDoc.Tables(1).Select
    wdApp.Selection.MoveDown Unit:=wdLine, Count:=Rng.Rows.Count - 1, Extend:=wdExtend
    wdApp.Selection.MoveRight Unit:=wdCharacter, Count:=Rng.Columns.Count - 1, Extend:=wdExtend
    wdApp.Selection.Paste
    wdDoc.Save
    wdApp.Quit
End Sub

Change the file name and Sheet/Range/Table references to suit.

 

[jon321]

working great thanks !!