Using Excel Worksheet as an index to open Word files

[JimmyC]

I have user request to open Word documents from an "index" worksheet page in EXCEL (Office 2010). Column A is blank, to start--but the user will place a capital x (X) in the column if they want the word file to open. Column B has the generic name for the user's easy reference while Column C contains the UNC path to the document. Currently rows 2- 23 have information in Columns B and C---but the rows will increase as more documents are added.

Can VBA "read" Column A and if his an X, then open the document following the path in Column C? The code would then move down a row and evaluate Column A again---until it reaches a blank cell in Column B (i.e., since column A is blank when not opened---I think the code would need to evaluate whether the cell in Column B is blank)---if it is, then the code can stop executing. I am really struggling trying to piece together code I am finding on the internet. I can't seem to determine how to "know" when Column B is blank and I can't seem to find code to "open" a Word file using a UNC path in a cell.

Thank you.
JimC

[HansV]

You can do it like this:

Code: Select all

Sub OpenDocs()
    Dim r As Long
    Dim m As Long
    ' Find the last used row
    m = Range("C" & Rows.Count).End(xlUp).Row
    ' Loop through the rows
    For r = 2 To m
        ' Does column A contain an "X"?
        If Range("A" & r).Value = "X" Then
            ' If so, open the document whose path is in column C
            ActiveWorkbook.FollowHyperlink Range("C" & r).Value
        End If
    Next r
End Sub

[Rudi]

Hans, will that work if column B only contains the "friendly" name?

[HansV]

Sorry, should have used column C. I will edit my previous reply. Thanks for pointing out my mistake.

[JimmyC]

Hans and Rudi---thank you. I will probably be back to ask a "how" question once I can use the code, watch it work, etc. as I really try to learn something every time I am helped in this great lounge/forum. I seem to be able to identify "how" to do something in VBA, but searching for code and trying to make it work has not been successful for me...so I really want to learn something when I get the actual code to solve a specific issue. Thanks again. JimC

[HansV]

I inserted a few comments in the code to explain how it works.

[JimmyC]

Hans---you are so kind. I will diligently work through this to learn as I go...thank you and have a great weekend. Jim