Solved: VBA for Cell Link to network folder document.

[Karti]

Hi all,

I run a report that gives me a list of numbers in a column. ie BB101, BB102 etc
The numbers (BB101) also relate to a document in a folder on a server

I am looking for some ideas where through VBA, a macro takes the details from a cell, searches for the same numbered BB101.doc and opens the document once clicked.

I have it where I can set the link.
[vba]
If Range ("D9") = BB101 Then
Range ("D9").Select
ActiveSheet.Hyperlinks.Add Anchor:="This would be my server link"
End If[/vba]
But what I am looking for, is it to search the Documents Folder and once it has found the document, create a hyperlink to it from the cell.

The add anchor would be a link to a server but as I have less than 5 posts it would not pick it up.

I am thinking about a For Each, but I am new to VBA.

Any help is greatly appreciated.

Regards

K

[Bob Phillips]

Surely, if you know the folder, and the name you just construct the hyperlink?

[Karti]

The report can have up to 100 cells that point to documents in the folder and within the folder there are about 6000 documents.

I can do it manually but I need to run the report every week and it takes time to set the links.

I thought that if I could take the link and get it to search for teh document and then hyperlink it , it would save time.

Now that I think of it would there be a way to take the cell contents and add it to the hyperlink so that it is

"\ \ server001 \Documents\" + Cell Contents + ".doc"

Regards

K

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const ROOT_PATH As String = "C:\test\"
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

.Cells(i, TEST_COLUMN).Hyperlinks.Delete
If Dir(ROOT_PATH & .Cells(i, TEST_COLUMN).Value) = .Cells(i, TEST_COLUMN).Value Then

.Hyperlinks.Add Anchor:=.Cells(i, TEST_COLUMN), _
Address:=ROOT_PATH & .Cells(i, TEST_COLUMN).Value, _
TextToDisplay:=.Cells(i, TEST_COLUMN).Value
End If
Next i
End With
End Sub
[/vba]

[Karti]

Many thanks,

As I am still learning I will try and get back as soon as possible with my answer

Regards

K

[Kenneth Hobs]

Welcome to the Forum!

Here is my approach which is a bit similar to xld's.
[vba]Sub RefreshMyLinks()
Dim ws As Worksheet
Dim cell As Range
Dim pFolder As String, fExt As String, fAdd As String

Set ws = Worksheets("Sheet1")
pFolder = ws.Range("A1").Value 'x:\MSWord
fExt = ".doc"
'Add trailing \ if needed
If Right(pFolder, 1) <> "\" Then pFolder = pFolder & "\"

With ws
.Hyperlinks.Delete
For Each cell In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
fAdd = pFolder & cell.Value & fExt
If Dir(fAdd) <> vbNullString Then
.Hyperlinks.Add cell, fAdd
End If
Next cell
End With
End Sub
[/vba]

[Karti]

xld,

I have created spreadsheet called Book1Test.xls.
I have created 3 documents in C:\excel called AB100, AB101 and AB102

I generally understand what the code is but I get two errors. I have checked the data and got one of my colleagues to do the same. I still get two errors. One is a 400 on a message window when I run the macro from the sheet and a Runtime 1004 when I run it from the VBA run (F5) command

Any ideas?

Regards

Jim

[Kenneth Hobs]

If you are going to use xld's code, you probably need to concatenate ".doc"?

e.g.
[vba]Public Sub ProcessData()
Const ROOT_PATH As String = "C:\test\"
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

.Cells(i, TEST_COLUMN).Hyperlinks.Delete
If Dir(ROOT_PATH & .Cells(i, TEST_COLUMN).Value & ".doc") <> vbNullString Then
.Hyperlinks.Add Anchor:=.Cells(i, TEST_COLUMN), _
Address:=ROOT_PATH & .Cells(i, TEST_COLUMN).Value & ".doc", _
TextToDisplay:=.Cells(i, TEST_COLUMN).Value
End If
Next i
End With
End Sub [/vba]

[Karti]

Ken,

Also trying your way. Where you have commented out x:\MSWord, I am slightly confused. Would this be where I place my link, which is c:\excel.

Again, I would like to try this but am I right in saying that it takes teh cell value and adds it to the path with the extension .doc added.

Many thanks for the assistance

regards

K

[Kenneth Hobs]

I set it for Sheet1's A1 value. You can use a Constant as xld did if you don't want to put the parent folder's path in cell A1 on Sheet1.

I also guess that your values were in A2 on Sheet1 and down.

[Karti]

Kenneth,

You are correct in that my details are A1, A2 etc

Ahhhh Done it!

It is just pFolder = "c:\excel"

I have now tested it with a network share and it works fine.

This is the best way I believe as it sets it as a link directly, without worrying about searching.

I will still look at the other code from XLD as I think that may come in handy as well.

Great support, great forum

Many thanks

K