place xl data into the document 
Author Message
 place xl data into the document

Hello
Im attempting to place a specific range of cells from xl into a word
document.
My code below will open the file, but I cannot figure out how to get the
actual data.
The data is on "Sheet1" and I will probably use the xlend to find the real
data to copy.  But How do I tell word to go to sheet1 and select a
particular range, then return that range as a paste?
From there, Ill have to have the table it creates automatically resize to
fit between the margins.

Thank you so much
Terry

Sub getexceldata()

Dim xldata As Object
Dim xlName As String
Set xldata = CreateObject("excel.application")
xlName = "c:\my documents\book1.xls"    Documents.Open xlName, , ReadOnly, ,
, , , , , , , Visible

    With xldata
        .Visible = True
    End With
    xldata.xlName.get
With xldata(xlName)
    .Activate
    Range("A1:N1").Copy
End With
ActiveDocument.Activate
Selection.Collapse Direction:=wdCollapseStart
Selection.Range.PasteSpecial DataType:=wdPasteText

xlApp.Quit

End Sub



Tue, 30 Mar 2004 19:14:01 GMT  
 place xl data into the document
Hi Terry,

Much more efficient would be to insert the information into Word in the form
a LINK field (which you can then turn into "plain text" if you don't want
the link). To see how this works, manually copy the range of cells in Excel.
In Word use Edit/Paste Special and activate "link". Press Alt+F9 to view the
LINK field code (this is like using the formula bar in Excel to view a
function).

In Word VBA, you need the .Add method for the fields collection, and you're
adding a field of .Type wdFieldLink. Everything following the word LINK in
the field you need to use for the .Text argument.

Note: Personally, I prefer to give the Excel range a NAME, and reference
that instead of a cell address for the "Range" part of the LINK field text.

Quote:
> Im attempting to place a specific range of cells from xl into a word
> document.
> My code below will open the file, but I cannot figure out how to get the
> actual data.
> The data is on "Sheet1" and I will probably use the xlend to find the real
> data to copy.  But How do I tell word to go to sheet1 and select a
> particular range, then return that range as a paste?
> From there, Ill have to have the table it creates automatically resize to
> fit between the margins.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
http://www.mvps.org/word
http://go.compuserve.com/MSOfficeForum

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)



Thu, 08 Apr 2004 18:04:24 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. H2 insert new XL worksheet incl data into existing XL workbook

2. how to gather html from several documents and place into one big document

3. Saving XL Document to outlook folder

4. Save XL file but don't want XL to prompt user

5. Read XL-sheet with VB4 without XL

6. Read XL-sheet with VB4 without XL

7. Read XL-sheet with VB4 without XL

8. SQL; Select Into Xl-File From XL-File

9. looking up data in XL

10. Automatic&NotVisible import of data from XL to Access

11. Autonumber in an Access Database to be placed in Word document

12. Hyperlink jumps to wrong place in same document

 

 
Powered by phpBB® Forum Software