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
