PDA

View Full Version : [SOLVED] Macro to copy from Excel and Paste in Word Template



deepaksethu
11-20-2006, 07:08 AM
I need a bit of (hopefully) straightforward VBA advice. I have docments
based on a Excel Sheet and documents based on a particular
Word template. I want to copy a small selection from the active document
based on the Excel Sheet to the active document based on the Word
template. I want my VBA program to be stored in the Word template The VBA code should say:
1. Go to the active Excel document (name of document not specified
because it is might be any document based on the template, but it is the
current active document in Excel).
2. Start at the active cell (will always be Column B, but could be any
row), select this cell then copy it.
3. Go to the open Word document (name of document not specified because it might be any document based on the template, but it is the current active document in Word).
4. Paste the Excel selection at the Pre-determined slot in the Word document. Different position of cursor. Paste/Special/Unformatted
Text.


Any suggestions/advice would be very greatly appreciated.

CBrine
11-20-2006, 08:06 AM
deepaksethu,
This should do what you want.



Sub GetExcel()
Dim XLApp As Object, ws As Object
On Error GoTo ErrHanlder
Set XLApp = GetObject(, "Excel.application")
Set ws = XLApp.activesheet
XLApp.Selection.Copy
Selection.PasteExcelTable False, False, True
Set ws = Nothing
Set XLApp = Nothing
Exit Sub
ErrHanlder:
Select Case Err.Number
Case Else
MsgBox Err.Number & " " & Err.Description
End Select
End Sub


HTH
Cal

Ken Puls
11-20-2006, 09:42 AM
Hi there, and welcome to VBAX!

The route that Cal has pointed out will definately work, but this is one of those great times when you have multiple routes to choose from. :)

We also have a KB article which pushes named Excel ranges into Word bookmarks: Push Excel Named Range Values to Bookmarks in Word It could easily be adjust to be a "pull" from Word if you found that idea more attractive.

PS, Cal, great to see you here again! :)

CBrine
11-20-2006, 09:49 AM
Thanks Ken,
I was just checking things out over here, seeing what's new.

Congrats on the MVP nomination, how fresh is the air at those lofty heights with Zack?:-)

Ken Puls
11-20-2006, 09:59 AM
LOL! Thanks, Cal. :)

I still feel pretty humbled by those around, to be honest. ;)

deepaksethu
11-21-2006, 07:07 AM
Thanks a ton ! Cal and all others for your effort and time. This was really helpful.

CBrine
11-21-2006, 08:53 AM
Deepaksethu,
Glad we could help. Please mark the post as solved if everything is finished.

Cal