Excel Hyperlinks 1

[marshyrob]

Hi All

I am wondering if the following is possible.

I have an Excel worksheet which has various columns. One is a column that has ID numbers in. Each ID number relates to a .txt file called the same as the ID number(which are located in the same folder). In the column next to this i want to place a hyperlink that references the .txt file matching the ID number. I have a lot of ID numbers so manually doing this is going to take forever and its also an on going project.

Has anyone got any suggestions using VB? i know VERY little about it but i can get by using examples.

Any help is appreciated.

Rob

 

[Skie]

Sub MakeALink
intRow = 1
'Change to the first row with a filename
strCell = "A" + Trim(Str(intRow))
'Change A to the column with the filenames
While Range(strCell) <> ""
    strLink = "C:\Temp\" & Range(strCell).Value & ".txt"
'Change C:\Temp to the path that the files are located
'Change .txt to the extension of the files
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(intRow, "B"), _
        Address:=strLink, TextToDisplay:=strLink
'Change "B" to the column you want the link to be in
    intRow = intRow + 1
    strCell = "A" + Trim(Str(intRow))
'Change "A" to the column with the filenames
Wend
End Sub

 

[Skie]

By the way, this is a VBA. So, you'll need to put it into Excel and run it for it to work.

 

[marshyrob]

Hi Skie

Thanks for the script, ive changed the script to suit the file locations and cells etc and added to Excel as a macro. When i run the macro nothing happens? No errors or no hyperlinks!?

Any ideas?

Here is the Script:

Sub MakeALink()
intRow = 54
'Change to the first row with a filename
strCell = "D" + Trim(Str(intRow))
'Change A to the column with the filenames
While Range(strCell) <> ""
    strLink = "H:\IT\Systems Infrastructure\Network Services\IDS\Alerts" & Range(strCell).Value & ".txt"
'Change C:\Temp to the path that the files are located
'Change .txt to the extension of the files
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(intRow, "E"), _
        Address:=strLink, TextToDisplay:=strLink
'Change "B" to the column you want the link to be in
    intRow = intRow + 1
    strCell = "D" + Trim(Str(intRow))
'Change "A" to the column with the filenames
Wend
End Sub

 

[Skie]

Your code worked for me. I put information in cells D54 through D70 and it populated a links in cells E54 through E70. Does cell D54 contain the first or last record (if you're going top to bottom)? Is D54 a blank cell? The code above would start at cell D54 and go down one cell until a blank cell is reached. If you want to specify the rows that will be used, you can use the code below. If a cell contains no information, then no link will be created.

Sub MakeALink()
intEndRow = 54
'Change 54 to the last row you want to have a link
strCell = "D" + Trim(Str(intRow))
For intRow = 1 to intEndRow
'Change 1 to the first row you want to have a link
  If Range(strCell) <> "" then
    strLink = "H:\IT\Systems Infrastructure\Network Services\IDS\Alerts" _
      & Range(strCell).Value & ".txt"
    ActiveSheet.Hyperlinks.Add Anchor:=Cells(intRow, "E"), _
      Address:=strLink, TextToDisplay:=strLink
    strCell = "D" + Trim(Str(intRow))
  End If
Next
End Sub

 

[marshyrob]

Hi Skie

I really appreciate your help.

In answer to your questions:

D54 was a blank cell, if i add something to it then it does nothing. I tried changing the script so that it starts from the first cell that does have info in (D2) and now i get a "runtime error 424 object required" at this line of code. The debug arrow is pointing at the 2nd line.

 ActiveSheet.Hyperlinks.Add Anchor:=Cells(intRow, "E"), _
        Address:=strLink, TextToDisplay:=strLink

If i use your new script and change the rows to the rows i require i get a "runtime error 1004 method range of object_global failed" at this line of code:

If Range(strCell) <> "" Then

Any clue as to what these are?

thanks for your help

Rob

 

[Skie]

It was trying to use row D0. This fixes it.

Sub MakeALink()
  intEndRow = 54
'Change 54 to the last row you want to have a link
  For intRow = 1 To intEndRow
'Change 1 to the first row you want to have a link
    strCell = "D" + Trim(Str(intRow))
    If Range(strCell) <> "" Then
      strLink = "H:\IT\Systems Infrastructure\Network Services\IDS\Alerts" _
        & Range(strCell).Value & ".txt"
      ActiveSheet.Hyperlinks.Add Anchor:=Cells(intRow, "E"), _
        Address:=strLink, TextToDisplay:=strLink
    End If
  Next
End Sub

Here's a link to some info about the 424 error:

http://www.miesoftware.com/Support/Articles/ols25.htm

 

[marshyrob]

Skie

YOU ROCK!!

Yeah thats what im talking about!!

Just one other thing and its perfect! I need it to do it real time, so whenever i add info to any of the cells in column D it automatically puts the link in column E. I tried changing intEndRow = to 300 but it doesnt work, as i presume it sees nothing when the script is run, how can it be looped to keep going and checking for any new data in the D column.

But hey this is great, im gonna give you a star!

Top Man!

Made my day! Happy New Year!!

Thanks

Rob

 

[mrmovie]

there are onchange events in Excel, put a pointer to your sub/function in one of the onchange events and it will run your code everytime a cell is changed!!!
you might want to therefore modify your code so that it only checks the cell that has changed to see if it needs to be updated..otherwise you might slow things down in Excel! good luck

 

[marshyrob]

Hi Mrmovie

Thanks for the info, where would i find that option?

Regards

Rob

 

[mrmovie]

go into visual basic editor in excel.
double click on a worksheet in the Project window on the LHS
on the right handside you will not have have 2 drop downs '(General)' and '(Declarations)'. Change (General) to 'Worksheet', then the Right Hand drop down will give you things like 'SelectionChange' and 'Change' etc etc, pick one that suits you. you will notice that the SelectionChange Sub is passed ByVal Target as Range, this will be helpful when you then come to code which cell you want to check the Hyperlink etc

 

[marshyrob]

Thanks MrMovie

i see where your coming from. Problem is i dont know much about VB so i wouldnt know what to do there. i have the working macro that skie did for me but i dont know how to add this functionality to the exisiting macro.

Im not very good at this sort of thing, sorry!!

any help would be appreciated.

Rob

 

[mrmovie]

not that im shirking but i would say we should close this thread off. i would recommend opening another thread in the VBA forum. Post the code you have working and your need to have the functionality/check run on some sort of OnChange event and that fact you want to therefore limit your For Each iteration to the just the cell/range that has changed. you should get flooded with helpful responses.

regards,
richard

 

[marshyrob]

Thanks Ill try that!

Rob