Use Excel VBA to add a hyperlink to a word document

I did mine in 2003 using a DOT file. That might be the difference. Mine put the "Name Here" as the link text.

As I said, it needs a bit more work to avoid the error when closing. I generally just use DOC files to do this kind of thing. You can easily copy the original DOC file and use it. If you want an example, let me know. I think this would avoid all the problems. Otherwise, I would have to see how to tweak your code some more.

well, I need to file to be a .dotm file as there is another macro that runs on document close when the user is done editing it.

I discovered something and thought it might be worth investigating.
If I copy a cell that includes a hyperlink and then paste in Word, it will take the hyperlink formatiing with it.

Currently the excel file is set up where the name (Text to Display) comes from one cell and the address comes from another. I may be able to use these cells to create the hyperlink in excel and then copy the cell and paste to the word bookmark.

I really appreciate all your help on this.
Rick

Well, I found something that may work. Now I just need to tweak it so that it looks right in my template.

If you copy a cell that contains a hyperlink in excel, and then paste it using pastespecial in word, it will paste the hyperlink correctly. The only issue I am having now is that when it pastes the cell it adds a carriage return at the end which screws up the spacing on my template.

So the code that works to get a hyperlink from excel to word is as follows:

Please Login or Register  to view this content.

For future reference if anyone else chooses to use this code, be sure to add error trapping and to release the word document after you are done controlling it with excel.

Anyone have any thoughts on the carriage return issue?
Rick

You can use Replace to replace the vbCrLf.

do you mean using a find and replace in word?
I was under the assumption that the line feed or carriage return that is inserted is no different than any other line feed in Word that is created by pressing enter. If this assumption is incorrect, how would I search for the VbCrLf? I know there are some special search terms for things like carriage returns and other formating items, is there one for this particular item?

Rick

After the paste, select the bookmark and then use the Selection in VBA's Replace().

Untested:

Please Login or Register  to view this content.

Originally Posted by Kenneth Hobson

After the paste, select the bookmark and then use the Selection in VBA's Replace().

Untested:

Please Login or Register  to view this content.

The problem with this technique is that when you paste into the bookmark, the bookmark itself is removed. Once you have pasted into the bookmark, you can no longer search for that bookmark to do a replace.

Now I have several bookmarks after this, and I may be able to do a search for them, but then I would have to back up to vbCrLf and delete it.
the document is actually formatted as such:

Cc: Vendor: [txtVendor2], [txtVendor3], [txtVendor4]
Verification:[txtVeri]
Purchasing:[txtPurch]

The bookmarks and their actual names are in the brackets above. So if I place a hyperlink in the bookmark named txtVendor2, I could then search for the VbCrLf backwards from the txtVendor3 bookmark.

Rick

Originally Posted by Kenneth Hobson

You can try changing the DOCM to DOTM and see how this works for you. This method takes into account the scenario where MSWord may already be open.

Please Login or Register  to view this content.

When using this code I get a runtime error 424 on the wd.hyperlinks.add line

I have changed this line to accomodate the code I already have in place, so that line actually currently reads as:

Also, I am using this code with a .docm file. I may have misunderstood, but I was under the impression that the errors we were getting before were caused by the .dotm file.

Please Login or Register  to view this content.

Thanks again
Rick

You don't really need LinkName and LinkAddress unless you reference it more than once. Just use it directly. However, being such a small program, it makes little difference either way.


Select is generally not needed and can slow things. It is better to use something like:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

The code that you are seeing is just a small part of the overall program that I am working with. It actually involves 2 user forms, 1 workbook and the Word Document.

I am using the linkname and linkaddress because ultimately I am putting 8 hyperlinks into different bookmarks throughout my document and it was my opininon that it would be easier to code and follow the code if I used the defined variables.

In the actual program I am dealing with, I am using a find on a specific sheet of the workbook to find the row that I need to work with and then assigning the value to LinkName and LinkAddress by the following code:

Please Login or Register  to view this content.

Where x is determined by which particular persons information I am trying to recover.

Any ideas other ideas why my linkname is not coming out correctly?
I will go try a few things and see if I can get anything to work.

Thanks again.
Rick

Does debug show what you expect? e.g.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The above code functions correctly. I think this will get me where I need to be for the Macro I am working on. I may have to change the NameCell and AddressCell ranges for each person that I need to add to the document, but that shouldn't be to hard.

I do also have a data validation to determine who and how many people the user wishes the document to be sent to so that will cut down some on the processing time.

Unless you have any suggestions, I think this is what I will use and we can call this one solved. I really don't understand why they had to make it so difficult to do this as it seems like it should be a fairly simple task.

Thanks for all of your help

Rick

Originally Posted by Kenneth Hobson

Does debug show what you expect? e.g.

Please Login or Register  to view this content.

I tried something similar by putting in a messagebox to print the results of linkname and yes it shows the correct result. It just seems weird that I don't get what I am expecting.

When I run the debug.print LinkName code in the immediate window, yes I do get what I am expecting to showup.

I even tried setting it as:

Please Login or Register  to view this content.

I get the same result in a messagebox (and the immediate window) but the same problem in Word. It looks like it is an issue with Word though because sometimes it shows up in word as what looks to be an asian character. Of course if the font is wrong and the character cannot be shown, then Word will display it as a square.

Rick

Might be a 2007 issue. If you post an xls with the problem and a sample docm, I can test how it marks up the docm in 2003.

I have attached the xls that I used.
I was not able to attache the .docm file as I can not get it to save below the 10.0kB minimum allowed file size.

All the document is though is a macro enabled document with a bookmark in it that is named txtVendor2

The original document is set up something like a fax to where it looks like this:

Cc: Vendor: [txtVendor2]
Purchasing:

I don't know why it does this. granted the other code works so I can use that and have tested it in my main macro as functional.

Rick

I got it working the way you suggested. If I recall correctly, the method you are suggesting and using is called late binding and the method I was using (Dim wb as excel.application) is considered early binding.

Is there any reason you recommend the late binding over the early binding?

I initially had some issues because I needed to get rid of all the original bindings, but I have it set up correctly now.
On to the next Task on my design list.

Rick

There are arguments for one binding method over another. Generally, I like early binding so that I can make use of intellisense. Since you are doing this from Excel, the reference to it is the default and preset. It is the reference to a new MSWord or an existing MSWord that we have to check for. I have not done it with early binding but that doesn't mean that we can't do it.

Someday, I probably need to see if I can do it using early binding. One can use an error routine as I did in the late binding method or other methods like API's FindWindow(). Part of it comes down to using New in the Dim or not.

cheers

Thanks for all the info Kenneth. I really appreciate all your help.

On a personal note, I used to leave closer to you as I grew up in Kansas City and went to school in Pittsburg, Ks. just north of the OK State Line.

Thanks again
Rick