Need Access to copy range of cells from Excel to Word

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello:
I have found all kinds of info on sending info to an Excel spreadsheet, but
I cannot find any code examples on how to:
1) Select a range of cells in a worksheet
2) Copy that selection to the Windows clipboard
3) Paste that selection into a Word document (I'm using bookmarks to place
the data)

I would appreciate some assistance with this as I am becoming frustrated
with it but I really need to be able to do it.
Thanks, Ted
 
I cannot find any code examples on how to:
Click to expand...

Since these are all Word or Excel questions, you'll get fuller answers
there -- which is to say that they'll probably point you at the help
files. Here are some hints:
1) Select a range of cells in a worksheet
Click to expand...

set rng = Wks.range(cellone, celltwo)
rng.select
2) Copy that selection to the Windows clipboard
Click to expand...

http://www.mvps.org/access/api/api0049.htm

but remember you don't need to select anything in order to get the
text...
3) Paste that selection into a Word document (I'm using bookmarks to
place the data)
Click to expand...

strTemp = Clipboard_GetText ' see above
rngWordRange.Text = strTemp


otherwise

wdrngNewParagraph.Text = xlrngCurrentCell.Text

but remember that a Word.Range is a completely different object from a
Word.Range, so you are going to have to concentrate to avoid mixing them
up.

Hope it helps


Tim F
 
Tim:
Thank you very much for your response.
I tried for hours to get your idea to work but it didn't so I kept on trying
the hit and miss method until I finally found something that would work.
I'm posting that code in this reply so as to share it with others who may
read this post and could use it.

'Launch the Word document template (.dot)
Set objWord = New Word.Application
objWord.Documents.Add Application.CurrentProject.Path & "\documentname.dot"
objWord.Visible = True

'Launch and open the Excel Worksheet
Set objXL = New Excel.Application
With objXL
.Visible = False
Set objWkb = .Workbooks.Open(RS3.Fields("FileName") 'gets filename from
open recordset
Dim objRange = Range(CRS3.Fields("PrintRange")) 'gets range of cells to
select from open recordset
objRange.Select 'Select the range
objRange.Copy 'Copy the selection
End With

'Go back to Word and paste in the selection
With objWord
.Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject, _
Placement:=wdInLine, DisplayAsIcon:=False
End With

'Close Excel
objWkb.Application.ActiveWindow.Close SaveChanges:=False
objXL.Quit
Set objXL = Nothing

'Print the Word Document
objWord.PrintOut

'Close the Recordsets
CRS3.Close
Set CRS3 = Nothing

'Close Word
objWord.Application.ActiveDocument.Close(wdDoNotSaveChanges)
objWord.Quit

Set objWord = Nothing

Thanks again for your response.
Ted
 
objRange.Select 'Select the range
objRange.Copy 'Copy the selection
End With

'Go back to Word and paste in the selection
With objWord
.Selection.PasteSpecial Link:=False, DataType:=wdPasteOLEObject, _
Placement:=wdInLine, DisplayAsIcon:=False
End With
Click to expand...

Just don't forget to warn the user that the important information she left
in the clipboard is going to be wiped out without asking...


All the best


Tim F
 
Back
Top