Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 801
  • Last Modified:

Need to use VBA to do what <CTRL>-<F9> does from the keyboard in word.

I want to insert one specific cell from an excel spreadsheet into word.

I currently do this by pressing <ctrl<-<f9> while in word and then typing:
LINK Excel.Sheet.8 "\\\\server\\path\\to\\excel\\file\\filename.xls" "Cell_Name" \a \t  
in the field.

How can I do this from VBA in Excel or Ms Access?

Thanks a bunch!
0
jasgot
Asked:
jasgot
1 Solution
 
SteinerCommented:
Try this one, be sure so set a reference to the Word object library inside the application this code should run:

Option Explicit

Sub WdField()
   Dim WdApp As Word.Application, WdDoc As Word.Document, rng As Word.Range
   
   Set WdApp = New Word.Application
   
   'The document to insert the field into
   Set WdDoc = WdApp.Documents.Open("e:\test\fields.doc")
   
   'The bookmark that marks the position, called bm_field
   rng = WdDoc.Bookmarks("bm_field").Select
   
   WdDoc.Fields.Add rng, wdFieldLink, "Excel.Sheet.8 ""\\\\server\\path\\to\\excel\\file\\filename.xls"" ""Cell_Name"" \a \t"
   WdDoc.Save
   WdDoc.Close
   Set WdDoc = Nothing
   
   WdApp.Quit
   Set WdApp = Nothing
End Sub
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now