Auto Create Hyperlinks to files on **

[kevin1010]

We have an excel document with many entries that correspond to individual files. The files are located in multiple sub-folders.

Here is an example: {} indicate a column

{A3} A1.1.1 PROCESS
{A4} A1.1.1.1 PROCESS DESCRIPTION
{A5} AD159-S00000-D-RPT-01231 {C5} PROCESS DESCRIPTION
{A6} A1.1.1.2 DESIGN BASIS CRITERIA
{A7} AD159-S00000-D-RPT-01227 {C7} BASIC ENGINEERING DESIGN DATA

The bolded words are the actual folder structure.
The non-bold is the actual file name minus the extension.
The italic words are a description of that file.
This permeates through the entire excel document. Some folders have over a 100 file names while others may only contain a few.

There are over 6000 lines in the document. Is there ANY way to automate this process instead of clicking each individual line and hyper-linking them one by one.

[tigeravatar]

kevin1010,

This could be automated with a macro, but the "folder structure" you have would be invalid. As in, A1.1.1 PROCESS is not a complete folder folder path. A complete folder path would look something like:

C:\A\A1.1.1 PROCESS

~tigeravatar

[kevin1010]

kevin1010,

This could be automated with a macro, but the "folder structure" you have would be invalid. As in, A1.1.1 PROCESS is not a complete folder folder path. A complete folder path would look something like:

C:\A\A1.1.1 PROCESS

~tigeravatar

You are correct about the complete folder path, I kind of figured we could just create a cell to auto add that in front of the path. Something like file:///...

I do not know how to make the macro though. I am by no means an expert.

[tigeravatar]

kevin1010,

If you could post a sample workbook, I can create a macro to suit your needs

~tigeravatar

[kevin1010]

omg, if you can figure this out, I'll have to buy you beer someday.

This contains the first 30 lines... there are about 5600 total. Be aware there are other "main" sections. Like this one starts with Process. There is also electrical, mechanical, etc...

[tigeravatar]

kevin1010,

I created a macro that should accomplish what you're looking for. Some notes about the macro:

• Change FldrCol to the column that contains the folder and file information (it was column A in the sample workook)
• Change StartRow to the row that the folder and file information starts on (it was row 3 in the sample workbook)
• Change the strBasePath to the base folder that contains the listed folders and subfolders. Be sure to include the ending \

Here's the code:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[tigeravatar]

kevin1010,

Forgot to mention something about the macro. In your sample workbook, the cell containing folder name "A1.1.1 PROCESS" was highlighted. The macro assumes all such folders are highlighted using that same color. The subfolders under that were bolded. The macro assumes all such subfolders are also bolded.

Let me know if this is not the case or if you have any questions,
~tigeravatar

[kevin1010]

Ok, you rock.

That totally worked. It error'd out a few times but that is our fault. Some of the directory names don't exactly match up to the excel document. Can fix that.

I have one more issue though...

I noticed all the hyperlinks paths are relative right now to our network drive. But we will be handing this excel document with all the folder/files to our clients on **'s. Which means the drive letter and pathing will change a bit.

I can copy the entire contents first to the ** and then run the macro... but what if the clients **-ROM drive isn't D:\
There any fix to that?

Also.. is there a way to tell it to not error out when it encounters a blank line? There are several throughout the document... its ok if not. We can just add n/a there and it works fine.

Nuts... another thing. I need this macro to ignore all files except .pdf's. We have native .docs in there and it is hyperlinking some of those too.

edit: umm ok... weird... it is **ing my letters C and D.

[tigeravatar]

Unfortunately you'd just have to change strBasePath to be whatever is needed. As an alternative, instead of using Const strBasePath As String = "C:\Test Folder\" you could instead do something like this:

Please Login or Register  to view this content.

Just change the Range("A1") to the cell that would contain that path (again, make sure the cell ends with the backslash). That way they can just update the cell to desired

~tigeravatar

[kevin1010]

There a way to tell it to ignore all files except .pdf's? It is hyperlinking the .doc's (native files) when it should only be doing the .pdf's.

[tigeravatar]

kevin1010,

Change this line:

Please Login or Register  to view this content.

To this instead:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[kevin1010]

TigerAvatar you are da man! or woman!
That totally worked!

I am still concerned about the drive mapping though. Either I don't quite understand what you meant or it isn't quite working. My fear is when we hand out all these **'s that someone is going to have their **-ROM on the letter E: or F: drive and it won't read the hyperlink correctly then.

As it stands, I'm just using the D:\Dossier as the root level and will burn the ** accordingly to that. There a command like %systemroot% that will simply read from whatever drive the client is opening in at?

[tigeravatar]

kevin1010,

My fear is when we hand out all these **'s that someone is going to have their **-ROM on the letter E: or F: drive and it won't read the hyperlink correctly then.

To get the **-rom drive letter, take a look at this link.

There a command like %systemroot% that will simply read from whatever drive the client is opening in at?

If you just need it to read from wherever the file is opening from, you can use:

Please Login or Register  to view this content.

That code will return the drive letter and colon. So if they opened it on the E: drive, it would return E:

Hope this helps,
~tigeravatar

[tigeravatar]

kevin1010,

To use it in the macro, replace this line:

Please Login or Register  to view this content.

With this instead:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[kevin1010]

TigerAvatar, you are a genius. Your code works 100% first try. Man, I can't say enough how thrilled we are over this. Seriously... we were putting hours away to do this manually.... 6000 lines.

So glad you found this thread and helped us.


So I ran your macro, it linked every file. I noticed in the hyperlinks it contains my path to where I put the folder. D:\Dossier\...etc

Does that mean when I burn this to *** someone will be able to open it regardless of what their drive letter is? Is that correct?

[tigeravatar]

kevin1010,

You're welcome

As far as the drive letter is concerned, the macro will only set the hyperlink to the drive letter it found when the macro was run. If its then sent to another user, and that user's ** drive is on a different drive letter, the hyperlinks wouldn't work. To get around this problem, you could have the macro run at the Workbook_Open event. However, this would then require that the folders the hyperlinks point to also be moved to the same drive. This only presents a problem in a situation like the following example:

User receives the **
User doesn't want to use the ** everytime he needs to open the workbook, so he copies the workbook to his desktop, but not the supporting files
The workbook updates the links to the C: drive now instead of the ** drive
They hyperlinks are broken (and opening the workbook probably results in several errors from running the code and it not finding the folders on the C: drive).

If the user only opens the workbook from the **, then putting the macro in the Workbook_Open event would take care of the issue. It would be easy enough to implement a check to see if the hyperlinks are already pointing to the correct drive letter and only run the macro if it does not match.

~tigeravatar

[kevin1010]

I understand. So we would need to make sure we tell each client that they have to open the excel document wherever the files are located. I think that would be fine.

Question 1: I don't THINK it would take too long for your macro to run through 6000 lines except... does it need to open every folder on the **? That could potentially take some time to do from a **.. or maybe not? I'm not sure.

Question 2: What code and where do I put it to make this happen? They open the workbook and it auto runs the macro to do the hyperlinks.

Question 3: Is there a way to have a macro ask the user if they want to redo hyperlinks? So if they copy the contents to a server or their hard drive... they can just run it once and not have to redo it again each time. From a ** I imagine the hyperlinks will not save.

Thank you!!!!! btw, dont' feel like you need to respond... you have helped us out enough as it is. We can get by with what we have... I so appreciate your help.

[tigeravatar]

Question 1: I don't THINK it would take too long for your macro to run through 6000 lines except... does it need to open every folder on the **? That could potentially take some time to do from a **.. or maybe not? I'm not sure.

I'm not entirely sure how long it would take. Not all that long, maybe a few seconds I would guess. The macro doesn't actually open the subfolders, just looks at their contents.

Question 2: What code and where do I put it to make this happen? They open the workbook and it auto runs the macro to do the hyperlinks.

Attached are instructions for where to paste the code. I changed the name to Private Sub Workbook_Open() so it just needs to be copy/pasted.
-Also, it will still need a standard module with only 1 line in it:
Public UpdateLinks As Integer

The following is the completed macro (with comments included)

Please Login or Register  to view this content.

Question 3: Is there a way to have a macro ask the user if they want to redo hyperlinks? So if they copy the contents to a server or their hard drive... they can just run it once and not have to redo it again each time. From a ** I imagine the hyperlinks will not save.

I included this functionality in the macro. It also asks if they want to remember that decision. Make sure to include the standard module with the line: Public UpdateLinks As Integer

In my test environment it is working. Test it out on your end, let me know if you come across any bugs.
~tigeravatar

[kevin1010]

- I copied the contents of your macro into a new excel document. I put the excel document in the same folder that has all the discipline specific folders in it. Tried to run it and get the below error.

Run-time error '9':
Subscript out of range.

I click on de-bug and it has this line in yellow:

If Left(ActiveSheet.Hyperlinks(1).Address, InStr(1, ActiveSheet.Hyperlinks(1).Address, ":", vbTextCompare)) = _
Left(ActiveWorkbook.Path, InStr(1, ActiveWorkbook.Path, ":", vbTextCompare)) Then

btw, I'm pretty sure I added the Module correctly. I right clicked in the left pane -> Insert -> Module
Then I added the line Public UpdateLinks As Integer

Saved and closed. Still get that runtime error.

[tigeravatar]

kevin1010,

I should have thought of that error. Its happening because the code assumes a hyperlink already exists. To avoid the error, just put the line On Error Resume Next above the If statement, like so:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

[kevin1010]

K, that fixed the error message but now nothing is happening at all.

I open the excel document and nothing appears to happen. I also do not receive a dialog asking me to update links or not.

[kevin1010]

Here is (sample portion) of what I have been working with...

[tigeravatar]

kevin1010,

Do you have a standard module with only the following line?

Please Login or Register  to view this content.

~tigeravatar

[kevin1010]

see attachment above (two posts up). I'm pretty sure I did the module correctly...

[tigeravatar]

kevin1010,

Yeah, we posted at same time. I've just gone over and gotten rid of some bugs. Attached is a modified version of your sample file. It should work now. Let me know if its still giving you problems

~tigeravatar

[kevin1010]

Ok friend. It did ask me if I wanted to update links now, however when I click Yes, nothing appears to happen.

I've tried several different combinations of Yes, No, Remember, etc... but none of the (A column) gets updated.

I tried putting the .xls file on C:\ root and also in C:\Dossier where all the folders for this document are located.

Am I doing something wrong?


EDIT:

NM! My bad. I see I needed to update the folder path in your code.

[kevin1010]

Tiger... you are one bada** programmer for excel. Seriously, I can't believe how quickly you figured out how to do all this.

It worked like a charm. I'm going to burn the contents to a ** and see how that works but I'm pretty sure it is going work great.

As a final note to close this thread, I really do appreciate what you have done for me and how you stuck through the whole process. You did this completely on your own free will and didn't even have to help me. It means a lot and reflects upon your character how you would help a total stranger out on the internet. Thank you so much Tiger. I wish you well in the future and if you are ever in Houston, give me a shout.

[tigeravatar]

kevin1010,

You're very welcome and thank you for the scales tap! I enjoy the challenge and each time I learn something new.

Now that your issue is solved, please click "EDIT" in your original post, click "Go Advanced" and set the Prefix box to "[SOLVED]".

Best of luck to you,
~tigeravatar

[kevin1010]

done.

p.s. I would have hit that scale on every post... but wouldn't let me. :P

Take care!