Quote:
Originally Posted by gmayor
Can you list the ranges in question and the worksheet name (or index) and I will have a look at it for you. Better still attach a workbook sample (you can change any sensitive information) and the document so that we can see what you are doing.
|
Sorry for the delay. OK, attached a simplified version of the Word & Excel Docs in question (there is more to them but this is all i need to provide to get the code I need - hopefully!
)
I would very much like to know the code (FROM WORD) to open the "open file" dialogue in Excel, to select a specified range in excel, copy it to the clipboard, switch back to the open Word document (also the code to switch back to the excel document), hopefully from there I can mix and match with what I already have (some macros stored in the document):
Select the range to paste over in WORD:
Sub SelectLocationT1()
'
' SelectLocationT1 Macro
'
'
Dim myCells As Range
With ActiveDocument
Set myCells = ActiveDocument.Range(Start:=ActiveDocument.Tables( 1).Cell(7, 2).Range.Start, _
End:=ActiveDocument.Tables(1).Cell(31, 2).Range.End)
myCells.Select
End With
End Sub
(i can modify anything you supply to select a different table)
To paste what is already on the clipboard in the right space after running the above:
Sub PasteNumbers()
'
' PasteNumbers Macro
'
'
Selection.PasteSpecial DataType:=wdPasteHTML
Selection.Font.Name = "Trebuchet MS"
Selection.Font.size = 10
Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
Selection.Cells.VerticalAlignment = wdCellAlignVerticalCenter
End Sub
(Again, I can modify to select different tables, columns etc)
The 1st range i need to copy from excel is A7:A31 for the numbers column, B7:B31 for the locations. Not a problem to work with A7:B31 as I can run a small macro at the end to format the table in Word as intended. If you are able to provide the code to do the above (open and choose an excel file that could be stored anywhere and named anything, select one of the tabs in the sheet (might need to assume it could have been saved with the wrong tab to the front), select the range listed and copy to the clipboard, then switch to word to allow me to mix in the above code... sounds simple huh? (if only). I can modify the code to then choose range A32:B56 and so on (blocks of 25) and paste in the appropriate table (ActiveDocument.Tables(1), ActiveDocument.Tables(3) etc)
Thank you