Обращение к ячейке в другой книге Excel из кода VBA по ссылке, содержащейся в ячейке текущей книги. Переход по ссылке в другую книгу. Примеры.
Ссылки на другую книгу
В ячейку «A1» листа «Лист6» текущей книги вставлена ссылка на ячейку «C12» листа «Лист1» книги «Другая книга.xlsm».
Ссылка в ячейке, если книга «Другая книга.xlsm» открыта:
|
1 |
='[Другая книга.xlsm]Лист1'!$C$12 |
Ссылка в ячейке, если книга «Другая книга.xlsm» закрыта:
|
1 |
='C:\Users\Evgeniy\Desktop\[Другая книга.xlsm]Лист1'!$C$12 |
Если путь до ячеек содержит пробелы, как в этом примере, он заключается в апострофы.
Перед обращением к другой книге необходимо проверить – открыта ли она. Если книга закрыта, ее следует открыть. Один из вариантов кода смотрите в параграфе «Примеры обращения к другой книге».
Обращение к ячейке по ссылке
Обращение из кода VBA Excel к ячейке в другой книге для определения ее координат по ссылке из ячейки «Лист6!A1» текущей книги:
|
1 2 |
MsgBox Range(ThisWorkbook.Sheets("Лист6").Range("A1").Formula).Row 'Результат = 12 MsgBox Range(ThisWorkbook.Sheets("Лист6").Range("A1").Formula).Column 'Результат = 3 |
Примеры обращения к другой книге
Условие
В ячейке «A1» листа «Лист6» текущей книги размещена ссылка: ='[Другая книга.xlsm]Лист1'!$C$12. Необходимо перейти по ссылке из ячейки «Лист6!A1» в другую книгу, скопировать диапазон из 9 ячеек (3х3) в другой книге, где первой ячейкой диапазона является ячейка из ссылки, и вставить скопированный диапазон в диапазон «Лист6!A2:C4» текущей книги.
Решение
Если точно известно, что другая книга открыта:
|
1 2 3 4 5 |
Sub Primer1() With ThisWorkbook.Sheets("Лист6") Range(.Range("A1").Formula).Resize(3, 3).Copy .Range("A2:C4") End With End Sub |
Если неизвестно, открыта другая книга или нет:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
'Функция для проверки состояния книги (открыта или нет) Function BookOpenClosed(wbName As String) As Boolean Dim myBook As Workbook On Error Resume Next Set myBook = Workbooks(wbName) BookOpenClosed = Not myBook Is Nothing End Function Sub Primer2() Dim s1 As String, s2 As String, s3 As String, n1 As Long, n2 As Long 'записываем ссылку из ячейки Лист6!A1 в переменную s1 s1 = ThisWorkbook.Sheets("Лист6").Range("A1").Formula 'вырезаем имя книги из ссылки и записываем в переменную s2 n1 = InStr(s1, "[") n2 = InStr(s1, "]") s2 = Mid(s1, n1 + 1, n2 - n1 - 1) 'проверяем состояние книги If Not BookOpenClosed(s2) Then 'если книга закрыта, вырезаем путь к ней из ссылки и записываем в переменную s3 n1 = InStr(s1, ":\") - 1 n2 = InStrRev(s1, "\") s3 = Mid(s1, n1, n2 - n1 + 1) 'открываем другую книгу, объединив путь к ней и ее имя Workbooks.Open (s3 & s2) End If 'копируем ячейки из другой книги в текущую Range(s1).Resize(3, 3).Copy ThisWorkbook.Sheets("Лист6").Range("A2:C4") End Sub |
И еще один вариант кода VBA Excel для случаев, когда неизвестно, открыта книга или нет. Здесь просто осуществляется попытка открыть книгу, и она будет открыта, если в ячейке содержится ее полное имя, включая диск и подкаталоги. В случае возникновения ошибки, управление будет передано следующей строке.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Sub Primer3() Dim s1 As String, s2 As String, n1 As Long, n2 As Long 'записываем ссылку из ячейки Лист6!A1 в переменную s1 s1 = ThisWorkbook.Sheets("Лист6").Range("A1").Formula 'вырезаем полное имя книги из ссылки и записываем в переменную s2 On Error Resume Next n1 = InStr(s1, ":\") - 1 n2 = InStrRev(s1, "]") s2 = Mid(s1, n1, n2 - n1) s2 = Replace(s2, "[", "") 'пробуем открыть книгу, если она не открыта Workbooks.Open (s2) On Error GoTo 0 'копируем ячейки из другой книги в текущую Range(s1).Resize(3, 3).Copy ThisWorkbook.Sheets("Лист6").Range("A2:C4") End Sub |
Переход к ячейке по адресу из формулы рабочего листа в пределах одной книги смотрите в следующей статье.