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.

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
|

Thanks alot.

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
|

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.

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
|

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

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
|