Microsoft Office Forums linking to excel from word

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-07-2012, 05:53 AM
jillapass jillapass is offline linking to excel from word Windows 7 32bit linking to excel from word Office 2007
Novice
linking to excel from word
 
Join Date: Dec 2011
Posts: 26
jillapass is on a distinguished road
Default linking to excel from word

I am having a real brain fade at the moment.



I have an excel spreadsheet open (it will always be open)

And I want to link to it.

I know the command to retrieve the data, but how do I actually tell it which spreadsheet without trying to open it again.
Reply With Quote
  #2  
Old 06-07-2012, 06:54 PM
macropod's Avatar
macropod macropod is offline linking to excel from word Windows 7 64bit linking to excel from word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,569
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

That depends on the code you're using. I'm not even sure why you need vba, since you can maintain a link between Word & Excel via Paste Special > Paste Link.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 06-08-2012, 12:37 AM
jillapass jillapass is offline linking to excel from word Windows 7 32bit linking to excel from word Office 2007
Novice
linking to excel from word
 
Join Date: Dec 2011
Posts: 26
jillapass is on a distinguished road
Default

I need vba because it is part of a program to get various information to save the file.
I need to get the info from a particular cell in excel, which I can do, but my code which I have shown below opens the spreadsheet, but I get an error because it is already open.

I was pushing the info from excel into content control fields, but I had to move the fields into the header and I cannot find a way to refer to header content controls from excel, it worked fine when they were in the main body of the word document, but cannot see the header ones.

Dim exWb As Excel.Workbook
Dim objexcel As New Excel.Application
Set exWb = objexcel.Workbooks.Open("M:\care plan system\menu forms\office forms menu.xlsm")

Dim advisor
advisor = exWb.Sheets("menu").Cells(5, 4)
ThisDocument.SelectContentControlsByTitle("LAA").I tem.Range.Text = advisor
Reply With Quote
  #4  
Old 06-08-2012, 01:12 AM
macropod's Avatar
macropod macropod is offline linking to excel from word Windows 7 64bit linking to excel from word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,569
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

So which do you prefer to do - push from Excel, or pull from Word? Pushing data into headers etc is not at all difficult.

Rather than populating content controls directly, you might do better to use custom document properties, and update those with the Excel data. You can then use DOCPROPERTY fields wherever you want in the document to replicate that data. It also means that moving the output's location, duplicating it or even deleting it won't compromise your code.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 06-08-2012, 01:20 AM
jillapass jillapass is offline linking to excel from word Windows 7 32bit linking to excel from word Office 2007
Novice
linking to excel from word
 
Join Date: Dec 2011
Posts: 26
jillapass is on a distinguished road
Default

I would probably rather push if I can.
But, I don't know enough to even know what custom document properties are.
Can you enlighten me.

Basically I have 8 fields in an excel sheet that I want to end up in the word document.

The word document is quite complex with 3 sections, 2 have headers which need this excel info, but the other ones doesn't. Up until now this form has been used with the headers being populated with content controls and the toolkit replicating these in the various places. The size of the document can also vary depending on how much detail is entered as each table could possibly expand over multiple pages.

These documents have been in place for a while, but have required entering the header info 12 times (one for each form) when this info is already on a spreadsheet.

Therefore I want them to be able to open the spreadsheet, enter the details then click on a button to open the word document, which then takes some info from the spreadsheet.

It was all working fine until I tried to put spreadsheet details in the word header.

Any help would be great, thanks.
Reply With Quote
  #6  
Old 06-08-2012, 01:28 AM
macropod's Avatar
macropod macropod is offline linking to excel from word Windows 7 64bit linking to excel from word Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,569
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

To create a custom document property, go to Word > Info > Properties > Advanced Properties > Custom. For a recent implementation, see:
https://www.msofficeforums.com/word-...numbering.html

As for updating a content control in a document header, you'd use something like:
ActiveDocument.Sections(1).Headers(wdHeaderFooterP rimary).Range.ContentControls(1).Range.Text = "Hello"
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
linking to excel from word linking word from Excel Lorna B Word 1 03-22-2012 03:36 PM
linking to excel from word Linking graphs from excel Markc PowerPoint 2 05-11-2010 02:17 AM
Linking Excel to Word engineer_in_training Word 0 01-06-2010 01:30 PM
Linking Excel Pivot Tables in a Word Document wmarsh3561 Word Tables 0 11-17-2009 06:29 AM
Linking from Excel to Word streng Word 4 10-28-2008 08:23 AM


All times are GMT -7. The time now is 06:14 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft