Use Word VBA string in Excel macro?

[Ed]

I write reports in Word and save them using a Word VBA macro, which pulls
certain bits of info from the doc and puts them together in a string to
specify file path and name.

Then I need to put a hyperlink to that doc in an Excel worksheet. How easy
would
it be to use the Word file path/name string as my hyperlink string in Excel?
(I'm trying to save having to navigate through folders to the doc each time
I use Ctrl+K. Also, the path link is long and always truncated in the
dialog box, so I have to manually massage each one. A string in a macro
seems easier.)

Ed

 

[Jonathan West]

Hi Ed,

It is possible to pass information between Word & Excel. This article gives
you a sample of how to do it.

Control Excel from Word
http://www.mvps.org/word/FAQs/InterDev/ControlXLFromWord.htm

 

[Ed]

It looks like this would have me create a Word macro that would open (or
activate) the spreadsheet and insert the hyperlink. In order to do that, I
would have to have the code find out the name of the workbook in use today,
and then identify the correct worksheet and cell to use for the hyperlink.

Seems like a lot to go through to insert a hyperlink! <g>

I was hoping rather there was some way to "preserve" the DocName string when
I left Word to go into Excel. That way, I am in the current worksheet and
can select the proper cell -click a button and voila: instant link.

If there isn't an easy and "regular"way to do this, might the Clipboard
work? I seem to recall ways to use the Clipboard discussed on the MVP
site, and the Clipboard contents stay available even after shutting down the
program. But are there problems inherent with doing things that way?

Ed

 

[Jonathan West]

It looks like this would have me create a Word macro that would open (or
activate) the spreadsheet and insert the hyperlink. In order to do that, I
would have to have the code find out the name of the workbook in use today,
and then identify the correct worksheet and cell to use for the hyperlink.

Seems like a lot to go through to insert a hyperlink! <g>

I was hoping rather there was some way to "preserve" the DocName string when
I left Word to go into Excel. That way, I am in the current worksheet and
can select the proper cell -click a button and voila: instant link.

If there isn't an easy and "regular"way to do this, might the Clipboard
work? I seem to recall ways to use the Clipboard discussed on the MVP
site, and the Clipboard contents stay available even after shutting down the
program. But are there problems inherent with doing things that way?

Yes, the only way I can think of that might work would be to put a string
containing the filename onto the clipboard.

This article will show you how.

Manipulating the clipboard using VBA
http://www.mvps.org/word/FAQs/MacrosVBA/ManipulateClipboard.htm

Then you paste into the appropriate place when you move to Excel.

 

[Ed]

Thanks - yet once again! - for all your help.

Ed