Insert data into specific cells of a word table from excel

[KatieF]

I need to insert data into specific cells in a word table that already exists from an excel spreadsheet. The columns will always stay the same just the rows referenced can change.
I will always insert values from column A such as A3 into my first word table in the first row,second cell. Does anyone know how you do this? I did try using this example as a basis but it didn't work out too well : vba - Taking data from Excel and inserting into a pre existing Word table? - Stack Overflow. Has anyone got a simple method to get it in the correct cell?

Code snippet below:

[/FONT][/COLOR][/SIZE][COLOR=#000000][FONT=Tahoma][SIZE=2]
[/SIZE][/FONT][/COLOR]
[COLOR=#000000][FONT=Tahoma][SIZE=2]'Get the Excel range
Set xlRng = Selection

 'Count the rows and columns that the table has in the word document
         With .Tables(1)
      For i = 1 To xlRng.Rows.EntireRow.Count
          For x = 1 To xlRng.EntireColumn.Count
            .Cell(i, x).Range.Text = xlRng.Cells(i, x).Value
            'Print value of integer to check it has a value assigned to it
           ' Debug.Print i
          Next
      Next
    End With
   
'    With .Tables(2)
'      For ii = 1 To xlRng.Rows.EntireRow.Count
'          For xx = 1 To xlRng.EntireColumn.Count
'            .Cell(ii, xx).Range.Text = xlRng.Cells(ii, xx).Value
'          Next
'      Next
     
    .Save

 

[Macropod]

This looks to me like a follow-on from your previous thread: http://www.mrexcel.com/forum/excel-questions/867797-writing-data-excel-into-existing-word-table.html
It would probably have been better to continue in the same thread. Please explain what is the relationship between the rows & columns in which the Excel data are found and the Word table cells you want the data written to.

 

[KatieF]

I thought if I had a different question I was supposed to start a new thread - I'll keep that in mind. Basically I have a word document with empty tables that have to be filled (the data comes from the excel spreadsheet) The data will always be the same things like name,order no etc and will always sit in the same columns in the excel sheet and be transferred over in the same order. There is a lot of rows in the excel spreadsheet however due to the large quantity of data.

I just need the first two cells I choose to be placed in to the first table in the word document of row 1 - columns 2 and 4 respectively. Any other cells in excel I choose after the first 2 should be put in the second table of the word document,it is 7 rows with a header included and 5 columns across.

I can currently get it to write to the tables fine, I can get it to write to a specific cell in the first table but after that I can't get the rest of the cells populated and it duplicates the data but it will if I'm currently using the same code but just changing table number.

Does that make things a little clearer? Hope I haven't confused you!

 

[KatieF]

*Update

I can write to the first table now, just working on it it duplicating the data!

With .Tables(1)
      For i = 1 To xlRng.Rows.EntireRow.Count
          For x = 1 To xlRng.EntireColumn.Count
          'Word cells first then cells from xl
            .Cell(1, 2).Range.Text = xlRng.Cells(i, x).Value
            .Cell(1, 4).Range.Text = xlRng.Cells(i, x).Value

 

[Macropod]

Unless you have all the cells you want to export from Excel selected at the same time, whatever macro you use wouldn't know the first table has already been populated hence, without testing the contents of the first table the second time around, it will naturally re-write the first table. Alternatively, if your workbook is structured such that, with the first cell selected you always know how many rows/columns away the rest of the data are, a single macro could be coded to use that logic to populate however many Word tables you need to work with.

 

[KatieF]

There is always a empty row between each populated row and each column will always be in the same place containing the same data. I currently have it working to populate the first table fine now using the code below:

 With .Tables(1)
      For i = 1 To xlRng.Rows.EntireRow.Count
        '  For x = 1 To xlRng.EntireColumn.Count
          'Word cells first then cells from xl
            .Cell(1, 2).Range.Text = xlRng.Cells(i, 1).Value
            .Cell(1, 4).Range.Text = xlRng.Cells(i, 6).Value 
            Next
            End With

What would be a better method would be once I selected the row itself (by either clicking the first cell in that row or clicking the number for the row it then retrieved the same data each time e.g - took data from columns a,b,g,k etc. Is that do able would you know?

Thanks for your replies!

 

[Macropod]

From your description, it looks like you need something along the lines of:

    With .Tables(1)
      .Cell(1, 2).Range.Text = xlRng.Cells(1, 1).Value
      .Cell(1, 4).Range.Text = xlRng.Cells(1, 6).Value
    End With
    With .Tables(2)
      .Cell(1, 2).Range.Text = xlRng.Cells(1, 8).Value
      .Cell(1, 4).Range.Text = xlRng.Cells(1, 11).Value
    End With

PS: This assumes you're using the code I posted in your other thread.

 

[KatieF]

Hi MacroPod,

Thank you for your reply, I think that for my second table I will need something along the lines of this but I want to start it at cell (2,1) and end it at cell (7,5) or just start it at cell(2,1) and then iterate the data through until there is no more data.

I was trying to use the the range to do it but that didn't work:

Set Rng = Range(Cells(2, 1), Cells(7, 5))

I have this and that populates fine but starts from the incorrect row - what method would I use to start it from cell(2,1)? I don't know loads atm so I'm just trying to learn so I can understand how it would work. I figured it would be changing the .Cell(i,x) section

    For i = 1 To xlRng.Rows.EntireRow.Count
          For x = 1 To xlRng.EntireColumn.Count
            .Cell(i, x).Range.Text = xlRng.Cells(i, x).Value

Thanks for taking the time to reply!

 

[Macropod]

Since you're apparently using the same Excel row for both tables and you have only a few cells in each to populate, there really isn't much point to implementing a pair of nested For .. Next loops. You may as well explicitly populate the cells as per my last post. All you need to do is match up the row/column references for both the workbook and the Word tables.