get data of cell out of an excel file and copy in word

[TOMMIETOM]

Hey,

I want to insert in my word doc the data of an open xls doc, ex cell A1.
Is is possible to create a macro in word that maximelise the open XLS file,
copies Cell A1, then minimalizes the xls file and than paste it into my word
doc ?

Thanks in advance !
Tom

 

[Graham Mayor]

Why not simply paste a link from that cell to your document? Then the macro
would be superfluous.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[TOMMIETOM]

Can the word doc be saved en reopend as the excel file is closes ?
How do I paste a link ?

Thanks!
Tom

 

[Graham Mayor]

Open the Excel file. Select the cell and copy to the clipboard. In Word edit

paste special > check the paste link box.

Set tools > options > general > update links at open and the linked cell
will display the content of the cell whenever the document is opened
(regardless of whether the Excel file is open) and will do so dynamically
when both the Word document and the Excel document are open.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[TOMMIETOM]

looks good !
now, how kan I do this in a macro in word ?
I have allready a macro that inserts a footer and a border, and I would like
to insert the contents of the excel cel in the footer.
This is what I have yet:

Sub CUE()

If ActiveWindow.View.SplitSpecial <> wdPaneNone Then
ActiveWindow.Panes(2).Close
End If
If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
ActivePane.View.Type = wdOutlineView Then
ActiveWindow.ActivePane.View.Type = wdPrintView
End If
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
If Selection.HeaderFooter.IsHeader = True Then
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageFooter
Else
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
End If
Selection.TypeText Text:="Erwin Custers" & vbTab
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldPage
Selection.TypeText Text:=vbTab
Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldDate
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
Selection.HomeKey Unit:=wdStory
With Selection.Sections(1)
With .Borders(wdBorderLeft)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderRight)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderTop)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders(wdBorderBottom)
.LineStyle = wdLineStyleSingle
.LineWidth = wdLineWidth050pt
.Color = wdColorAutomatic
End With
With .Borders
.DistanceFrom = wdBorderDistanceFromPageEdge
.AlwaysInFront = True
.SurroundHeader = True
.SurroundFooter = True
.JoinBorders = False
.DistanceFromTop = 24
.DistanceFromLeft = 24
.DistanceFromBottom = 24
.DistanceFromRight = 24
.Shadow = True
.EnableFirstPageInSection = True
.EnableOtherPagesInSection = True
.ApplyPageBordersToAllSections
End With
End With
With Options
.DefaultBorderLineStyle = wdLineStyleSingle
.DefaultBorderLineWidth = wdLineWidth050pt
.DefaultBorderColor = wdColorAutomatic
End With
End Sub

Many thanks !
Tom

 

[Graham Mayor]

From your description, it sounds as though you are creating a document
template on the fly to add to a document. it would make far more sense to
add the header/footer, including the link, to a blank document and save that
as a template and use it to create the documents in the first place.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[TOMMIETOM]

That is not possible, because the word file is created from a macro in XLS.
There is a large amout of text copied from xls to word.
So I have allready a word doc, I can't start from a blanc.
FYI : This is the macro in excel :

Selection.Copy

Const wdAlignParagraphCenter = 1

Set WordApp = GetObject(, "Word.Application")
With WordApp
.Visible = True
.Documents.Add

With .Selection
.Paste
.ParagraphFormat.Alignment = wdAlignParagraphCenter
.PageSetup.TopMargin = 36
End With

End With

 

[Graham Mayor]

Your macro is adding a Word document
..Documents.Add
You can qualify this with a template parameter top create that new document
from such a template instead of the default normal.dot template
..Documents.Add Template:="templatename"

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[TOMMIETOM]

so I made a word doc, saved it as a template named, test.dot and
I saved it in : Application Data\Microsoft\Templates
In the xls macro I tried
..Documents.Add Template:="test.dot"
and olso
..Documents.Add Template:="test"
But the template isn't used .. he opens a new doc as before.

But when I open a new word doc in word, and uses the template, it doesn't
uses the template.
So I think there is a problem with my template I made.
I tested it with a template to put a border 3d shadow ons the whole word doc.

 

[TOMMIETOM]

It works !
Many thanks !

one last little question : is it possible to avoid the display of : Do you
want to update the linked cells, yes or no.

 

[Graham Mayor]

..Documents.Add Template:="test.dot"

works for meI can't remember if that path is the correct one, but adding the
path should confirm it, but saving the formatted document as a template from
Word should automatically select the correct template path.

..Documents.Add Template:="c:\path\Templates\test.dot"


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Graham Mayor]

Here (Word 2003) it just updates the link automatically - if tools > options

general >update links is set. Otherwise it shows the old content?

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[TOMMIETOM]

also using word 2003.
when opening the template he displays : This document contains links that
refer to other files.
do you want to update ? yes/no

update automatic links at open in checked in the general options.
When I knick no after asking, he even opens the xls file to update... strange