Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word VBA link to Excel
In MS Word, I wanted to create a VBA macro code to link to a specific cell of text from Excel, but to break the link after the data is inserted from Excel. I would appreciate any help. I have been able to do this in Excel, but unable to do so with Word. -- Borg ------------------------------------------------------------------------ Borg's Profile: http://www.excelforum.com/member.php...o&userid=36396 View this thread: http://www.excelforum.com/showthread...hreadid=561797 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word VBA link to Excel
Option Explicit
Dim filePath, oExcel, oSheet filePath = "c:\Test.xls" Set oExcel = CreateObject("Excel.Application") oExcel.Workbooks.Open(filepath) Set oSheet = oExcel.ActiveWorkbook.Worksheets(1) oSheet.Name = "sheet1" MsgBox oSheet.Cells(1, 1) oExcel.DisplayAlerts = False oExcel.ActiveWorkbook.Close oExcel.Quit set oSheet = Nothing Set oExcel = Nothing -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Borg" wrote: | | In MS Word, I wanted to create a VBA macro code to link to a specific | cell of text from Excel, but to break the link after the data is | inserted from Excel. | | I would appreciate any help. | | I have been able to do this in Excel, but unable to do so with Word. | | | -- | Borg | ------------------------------------------------------------------------ | Borg's Profile: http://www.excelforum.com/member.php...o&userid=36396 | View this thread: http://www.excelforum.com/showthread...hreadid=561797 | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word VBA link to Excel
Thanks alot. :) :) :) -- Borg ------------------------------------------------------------------------ Borg's Profile: http://www.excelforum.com/member.php...o&userid=36396 View this thread: http://www.excelforum.com/showthread...hreadid=561797 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word VBA link to Excel
You're welcome.
BTW you won't need the line; oSheet.Name = "sheet1" -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Borg" wrote: | | Thanks alot. :) :) :) | | | -- | Borg | ------------------------------------------------------------------------ | Borg's Profile: http://www.excelforum.com/member.php...o&userid=36396 | View this thread: http://www.excelforum.com/showthread...hreadid=561797 | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word VBA link to Excel
The linking to Excel works perfectly. But instead of a Message Box, ho would I insert the text from the cell into the Word file? Thanks -- Bor ----------------------------------------------------------------------- Borg's Profile: http://www.excelforum.com/member.php...fo&userid=3639 View this thread: http://www.excelforum.com/showthread.php?threadid=56179 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word VBA link to Excel
I don't know the object model for word but you can just make the object,
place holder, or variable you intend to use in your word VBA equal to oSheet.Cells(1, 1) -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Borg" wrote: | The linking to Excel works perfectly. But instead of a Message Box, how | would I insert the text from the cell into the Word file? | | Thanks. | | | -- | Borg | ------------------------------------------------------------------------ | Borg's Profile: http://www.excelforum.com/member.php...o&userid=36396 | View this thread: http://www.excelforum.com/showthread...hreadid=561797 | |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word VBA link to Excel
Thank you sir, With your help, I played around with your VBA code and got it to do what I intended. Here is what I modified it to: Sub test() Dim filePath, oExcel, oSheet filePath = "C:\Documents and Settings\User\My Documents\Book1.xls" Set oExcel = CreateObject("Excel.Application") oExcel.Workbooks.Open (filePath) Set oSheet = oExcel.ActiveWorkbook.Worksheets(1) Selection = oSheet.Cells(1, 1) Selection.Copy Selection.Paste oExcel.DisplayAlerts = False oExcel.ActiveWorkbook.Close oExcel.Quit Set oSheet = Nothing Set oExcel = Nothing End Sub -- Borg ------------------------------------------------------------------------ Borg's Profile: http://www.excelforum.com/member.php...o&userid=36396 View this thread: http://www.excelforum.com/showthread...hreadid=561797 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Word VBA link to Excel
Glad to hear it worked for you.
-- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect "Borg" wrote: | | Thank you sir, | | With your help, I played around with your VBA code and got it to do | what I intended. | | Here is what I modified it to: | | Sub test() | Dim filePath, oExcel, oSheet | filePath = "C:\Documents and Settings\User\My Documents\Book1.xls" | Set oExcel = CreateObject("Excel.Application") | oExcel.Workbooks.Open (filePath) | Set oSheet = oExcel.ActiveWorkbook.Worksheets(1) | Selection = oSheet.Cells(1, 1) | Selection.Copy | Selection.Paste | oExcel.DisplayAlerts = False | oExcel.ActiveWorkbook.Close | oExcel.Quit | Set oSheet = Nothing | Set oExcel = Nothing | | End Sub | | | -- | Borg | ------------------------------------------------------------------------ | Borg's Profile: http://www.excelforum.com/member.php...o&userid=36396 | View this thread: http://www.excelforum.com/showthread...hreadid=561797 | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel link with Word | Excel Worksheet Functions | |||
how to link excel to word | Excel Discussion (Misc queries) | |||
PASTE LINK option not available when I select PASTE SPECIAL to link an image in Excel to a Word document. | Links and Linking in Excel | |||
Link table from excel to word using word VBA | Excel Discussion (Misc queries) | |||
VB Help WORD/EXCEL LINK | Excel Discussion (Misc queries) |