PDA

View Full Version : copy cells from excel table into a pre-formatted table in word



AlexMason
07-15-2010, 01:43 AM
Hey guys, im genuinely stuck here with no idea how to go about this.

what i have is a table in an excel sheet containing axial clearances (minimum calculated ones and provisional amounts entered by hand) for respective stages in a turbine. the table also contains data for other parts of the turbine, but i just want the turbine stage data.

now these tables can vary in size, but i already wrote some code to determine the size of the table, where the last stages are in the table and how to determine what cells the rest of the stages are in.

so i know where the data is, i have a starting point to begin transferring.

i know how to open word and the template file from excel. but now i need to copy the data from excel, cell by cell to cells in word.

in my current example i have 12 stages, and 5 sets of clearance data (stages in columns, clearances in the rows). the template only allows for 4 stages per word page (so ill need 3 pages). the action needs to be as followed.

go to stage 1, copy 1st clearance cell, go to word document, paste that data in the first stage column in the first row. back to excel, copy the 2nd clearance cell, go to word, paste in the first stage column in the 2nd row... and so on.

does word treat its tables like excel treats worksheets? do they have similar addresses? like A1, B2 etc?

any help is much appreciated.

Alex

AlexMason
07-15-2010, 02:05 AM
actually im being a bit obtuse here........ surely, i can just do the copy n paste stuff from the excel sheet, into a new excel sheet where the formatted table is, then just paste the whole caboodle into word at the end?

just a case of retaining the formatting then.

geekgirlau
07-15-2010, 10:52 PM
You can get some rather dodgy formatting as a result of copying & pasting from Excel to Word.

Another option might be looping through your range, and just writing the cell value directly to the relevant table cell.

To give you a starting point in Word:


Dim tbl As Table

Set tbl = ActiveDocument.Tables(1)

tbl.Cell(1, 1).Range.Text = "test"



You just need to co-ordinate the loop through your range in Excel, with looping through the corresponding cells in your Word table.