Access data in a closed workbook containing a protected worksheet

In a LinkedIn group, someone wanted to access data in a shared server-based workbook that contained a protected sheet with locked cells that were not selectable. In addition to sharing an automated way of doing this, this post contains two other embedded tips.

The solution, as many know, is to enter a formula in the destination worksheet that references the source cell, e.g., =’C:\Temp\[Book2.xlsx]Sheet1’!$E$5

Given the high likelihood of making an error in entering long formulas, I decided to see if I could automate the process.

Tip 1: In doing so, I discovered that under certain circumstances Excel will make a very interesting correction. If the source workbook has a single worksheet, then one can use any sheet name in the formula and Excel will change it to the correct one! So, if book2.xlsx contains a single sheet named Sheet1, and one were to enter the incorrect formula =’C:\Temp\[Book2.xlsx]abc’!$E$5, Excel will correct it to =’C:\Temp\[Book2.xlsx]Sheet1’!$E$5.

That aside, since the cells in the source worksheet are not selectable, one cannot construct the formula using click-and-point. So, I decided that as long as one wants the values from the source cells to be in the same cell in the destination worksheet, why not select the cells in the destination worksheet? The code below does just that. Also, there is no longer a need to open the shared server workbook at all!

One final note. I rarely use so many different interactions with the consumer, preferring a userform. But, the below is easier to share. ;-)

Tip 2: The Inputbox method gets a single piece of information from the user, e.g., the sheet name in the code below. If the user were to cancel the resulting dialog box, the method returns False. The usual way to check for this is to compare the returned value with “False”. But, this precludes a legitimate response of “False”! So, I tend to check if the returned type is a boolean. The same applies to the GetOpenFilename method.

Enter the code below is a standard VBE module. Then, open the destination worksheet (or create a new one), and then run the linkToExternal subroutine. It will ask for the source workbook, the source worksheet, and then the destination cells. The code will add in each destination cell a formula that links to the same cell in the source worksheet.

Option Explicit

Sub linkToExternal()
    If ActiveWorkbook Is Nothing Then
        MsgBox "Please open the destination workbook before running this macro"
        Exit Sub
        End If
    Dim FName
    FName = Application.GetOpenFilename( _
        Title:="Please select the source workbook")
    If TypeName(FName) = "Boolean" Then Exit Sub
    FName = Left(FName, InStrRev(FName, Application.PathSeparator)) _
        & "[" & Mid(FName, InStrRev(FName, Application.PathSeparator) + 1) _
        & "]"
    Dim SheetName
    SheetName = Application.InputBox("Please enter the name of the source sheet", Type:=2)
    If TypeName(SheetName) = "Boolean" Then Exit Sub
    FName = "='" & FName & SheetName & "'!"
    Dim Rng As Range
    On Error Resume Next
    Set Rng = Application.InputBox( _
        "Please select the destination cells into which you want the corresponding source cell values", _
        Type:=8)
    On Error GoTo 0
    If Rng Is Nothing Then Exit Sub
    Dim aCell As Range
    For Each aCell In Rng
        aCell.Formula = FName & aCell.Address(True, True)
        Next aCell
    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>