Auto hyperlinking on close to another document

[indygirl]

I have a word doc that on close I would like to have the hyperlink addess from that closed file automatically entered into the next available cell in a specific column in an excel spreadsheet. I think this should be possible with VBA but am not sure where to start since I am going to be working with 2 different applications. Can someone lend me some advice as to how to go about this - that is if it is possible?

Thanks!

 

[bigoldbulldog]

Is Excel responsible for opening the Word object? If yes, you can code your own close event on that object. Then when the Word object is closing it's caught and you update in Excel as needed.

If no, you can probably still let Excel know about the object and probably do as above.

Cheers,
ND

 

[indygirl]

The user opens a form in outlook then clicks a button to open the Word Document. The hyperlink needs to be from the Word Doc. and needs to be attached to the button in Outlook and into the cell in Excel. Does that make sense?

 

[Bong]

So, Outlook knows the name of the Word file, as well as the name of the Excel file into which the hyperlink should go? I'd say the macro has be in Outlook if that's the case. I don't work much with multiple applications although a lot of people on the forum do. I think I'd open the Excel file first, then when the button is clicked to open the Word file, add the hyperlink to the spreadsheet then. After all, if the Word file is opened, it will eventually be closed, why does the link need to be added when it closes, rather than when it opens?

_________________
Bob Rashkin

 

[indygirl]

Unfortunatly, Outlook only knows the name of the template.doc not the actual saved name. The word .doc is a template that the user completes than saves as another name. Outlook doesn't even know about the Excel file. Do you think this is still do-able?

 

[indygirl]

One more thing, Excel does not open the .doc - Outlook does.

 

[Bong]

Perhaps the Outlook macro could ask the user for the name to be used for saving. Then the template could be opened and immediately saved, using SaveAs with the given file name, then turned over to the user?

_________________
Bob Rashkin

 

[indygirl]

Great idea. I will see if I can make this happen. ALways up to a challenge. Thanks!

 

[indygirl]

I can do part of what I am looking for by assigning a Word Macro but I need to alter the macro so it will autosave. How can I go about stepping into the macro? When I click step into my word doc closes all together and I am left with nothing.

 

[Bong]

I assume that in your Word macro you have something like:

ChangeFileOpenDirectory <path to your target folder>
ActiveDocument.SaveAs FileName:="<your filename>.doc", _ FileFormat:=wdFormatDocument, _
 LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
  :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
  SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
  False

What happens when you have Word open and run the macro from there?

_________________
Bob Rashkin

 

[fumei]

It may help if you posated your actual code. For example,

I would like to have the hyperlink addess from that closed file

WHAT hyperlink address? And how are you getting it in your code?

In your original post you never mentuibed Outlook, yet this is from Outlook. You mkentuiin across 2 applications, yet, it is really THREE - Outlook, Word and Excel.

Please try and be specific. Another example:

When I click step into my word doc closes all together and I am left with nothing.

It is hard to comment, or make suggestions when we can't see what you are actually doing.

Here is your original post:

I have a word doc that on close I would like to have the hyperlink addess from that closed file automatically entered into the next available cell in a specific column in an excel spreadsheet. I think this should be possible with VBA but am not sure where to start since I am going to be working with 2 different applications. Can someone lend me some advice as to how to go about this - that is if it is possible?

1. Use Document_Close to perform the actions...on document close.

2. Pick "the" hyperlink address...although for the life of me I have no idea what that means. Is there only one? Is that what makes it "the" hyperlink?

3. The Outlook code can also create the Excel instance, and open the file. When the Word is closed, it passes "the" hyperlink address (as a string) back to Outlook, which passes it to the Excel instance.

There is a problem with your use of a "template". Using a document that is changed then saved as a different name is NOT a template.

You do not explain the issue of autosaving the document. Why? What is being changed?

I am not following what you are actually trying to do here.

Gerry
My paintings and sculpture

 

[indygirl]

I have been successful in creating the Outlook Form with a button which will open a .dot for the user to complete. I have an icon on the toolbar that will allow the user to see the exact path address of the saved .doc and another icon to open a .xls file to enter that specific path address.

However, I would prefer if the user can copy and paste the path address into a specific cell in the worksheet. So far, the user can copy and paste and it looks like a hyperlink - blue and underlined but the link does not work.

Is there another step to format an entire column to accept all text as hyperlinks?

 

[Bong]

Not as such, at least as far as I know. Have you tried the "Paste Link" option of "Paste Special"? The real way to make a text string equal a link is to pre-pend a protocol: [red]file://[/red]drive:\path\file.ext

_________________
Bob Rashkin