Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

marshyrob (TechnicalUser) (OP)
20 Dec 05 3:36
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
Helpful Member!  Skie (Programmer)
21 Dec 05 23:54

CODE

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 (Programmer)
21 Dec 05 23:56
By the way, this is a VBA.  So, you'll need to put it into Excel and run it for it to work.
marshyrob (TechnicalUser) (OP)
28 Dec 05 3:47
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 (Programmer)
30 Dec 05 19:47
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.

CODE

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 (TechnicalUser) (OP)
3 Jan 06 3:50
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 (Programmer)
3 Jan 06 20:11
It was trying to use row D0.  This fixes it.

CODE

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 (TechnicalUser) (OP)
4 Jan 06 3:06
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 (TechnicalUser)
4 Jan 06 4:37
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 (TechnicalUser) (OP)
4 Jan 06 4:43
Hi Mrmovie

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

Regards

Rob
mrmovie (TechnicalUser)
4 Jan 06 5:04
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 (TechnicalUser) (OP)
4 Jan 06 5:11
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 (TechnicalUser)
4 Jan 06 5:23
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 (TechnicalUser) (OP)
4 Jan 06 5:29
Thanks Ill try that!

Rob

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close