Sub Get_Value_From_Close_Book_Formula()

 Dim sPath As String, sFile As String, sShName As String

 sShName = "Лист1"
 With Application.FileDialog(msoFileDialogFilePicker)
 .Filters.Clear
 .Filters.Add "Microsoft Excel files", "*.xls"
 .AllowMultiSelect = False
 .InitialFileName = ThisWorkbook.Path

 If .Show = 0 Then Exit Sub
 sep_ = Application.PathSeparator
 sFile = Split(.SelectedItems(1), sep_)(UBound(Split(.SelectedItems(1), sep_))) ' "Книга1.xls"
 sPath = Replace(.SelectedItems(1), sFile, "") '"C:\Documents and Settings\"

 End With

 With Range("C2:C101")
 .Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1:A100"
 .Value = .Value
 End With
 End Sub