Excel как обратиться к ячейке другой книги
Ваша ошибка в том, что Open открываете уже открытый файл, и он закрывается перед открытием, очищая блоки управления.
Правильно в программе активировать (Activate) книгу, если с ней много работы. Затем активировать лист и ячейку, но часто их достаточно выбрать (Select).
Application.Workbooks("имя_файла").Activate
ActiveWorkbook.Worksheets("имя_листа").Range("A5").Select
MsgBox Selection
Для книги в коллекции книг Workbooks можно указывать порядковый номер файла, автоматически присвоенный при открытии. Порядок можно определить
в списке окон Windows (в Excel) или глядя по-еврейски (справа-налево) на ярлыки открытых Excel-файлов на Панели задач. Но этим номером в автоматической программе пользоваться не рекомендуется, так как можно перед запуском вручную закрыть или открыть какие-то файлы и порядок изменится, да и количество. Свежеоткрытый файл меет порядковый номер 1, то есть попадает в голову коллекции. Значит этот способ пригоден только для фиксированного числа открытых файлов.
Правильно вместо номера задавать ИМЯ открытого файла (и даже заранее отслеживать ситуацию, открыт ли он).
Лист надо также выбирать или активировать по его ИМЕНИ на вкладке в рабочей книге. Но можно и по порядковому номеру вкладки. Дело в том, что при создании НОВОГО листа он открывается по-еврейски в левой вкладке книги и автоматически получает номер 1 в коллекции листов книги. Tо есть свежий лист это всегда Worksheets(1). Но его затем можно передвинуть среди вкладок и он этого номера не потеряет, но вы можете запутаться (как и с файлами)! Правильный порядковый номер листа (вместе с именем в скобках) можно увидеть в среде VBA в списке проекта.
Пользуясь именами, вы не ошибётесь, но для этого произвольные имена нужно уметь запоминать в программе, чтобы потом обращаться к ним в процессе выполнения.
В книге которая должна заставить работать программу нужно в среде VBA открыть код Эта книга и вписать в него
макрос с именем Auto_Open(). Он должен содержать непосредственно вашу рабочую программу или хотя бы её точный её вызов, то есть с указанием на дополнительный модуля в котором программа находится.
Вы щелкаете по файлу, содержащему Auto_Open(), он открывается, и если открыт файл с этим дополнительным модулем, то всё работает .
Память не может быть READ. :)
• Excel при его Открытии Вываливает в Виртуальное Пространство ОЗУ свою текущую Информацию. Такова Windows, и таков порядок её Организации.
• Excel Создаёт в Temp-Каталоге Файлы Пошагового Изменения. Они нужны, чтобы с помощью Отката Команды (Ctrl+Z) можно было вернуться к предыдущим Состояниям.
• Excel каждые 10 (если не ошибаюсь) минут Создаёт в Temp-Каталоге по одной Резервной Копии с текущего Состояния Открытого Документа (Фоновое Автосохранение).
• Excel Создаёт в том Месте, откуда Пользователем был Открыт Файл Временный Информационный Файл. В случае Аварийного Завершения Работы (внезапное обесточивание компа). Если при Работе в Excel`е всё пройдёт успешно, и в конце Пользователь/сама Операционная Система Закроет Файл (не важно с Сохранением или без), тогда этот Файл будет Стёрт Excel`ем. Активные Файлы, как и Память (которая не может быть READ) точно также не могут быть READ. :)
Следовательно надо, чтобы что-то извне давало бы команду или какую-то Связь (через которую будут Выполняться какие-то Фоновые Задания, или что-то там, что надо Запустить в VBA). Значит надо загодя в сам Стандартный Шаблон умышленно Вставить Код, когда ещё не запущен Excel. Excel всегда Открывает Новый Документ из имеющегося Шаблона.
PS. Насколько я понимаю, на Активные Файлы в Windows можно влиять через Реестр, Прописав Доступные Действия для Активных Файлов. Расширять возможности Excel`я, наверное, придётся прямо отсюда из «Базы Данных» Windows. Т. е. где-то здесь должна быть лазейка?! ¯\_(°·о) _/¯
В этой статье рассказывается о том, как искать значения и возвращать данные из другой книги, а также как находить / искать значения из другой книги. Здесь я подробно расскажу о трех решениях.
Данные Vlookup и возвращаемые значения из другой книги в Excel
Например, вы создаете таблицу «Покупка фруктов» в Excel, и теперь вам нужно просмотреть фрукты из другой книги и вернуть соответствующие цены, как показано на скриншотах ниже. Здесь я расскажу вам, как решить эту проблему с помощью функции VLOOKKUP в Excel.
1. Откройте обе книги, из которых вы будете просматривать значения и возвращать значения.
2. Выберите пустую ячейку, в которую вы вернете цену, введите формулу = ВПР (B2; [Price.xlsx] Sheet1! $ A $ 1: $ B $ 24,2; FALSE) в него, а затем перетащите его маркер заполнения, чтобы применить эту формулу к диапазону по мере необходимости.
Заметки:
(1) В приведенной выше формуле B2 - это плод, который вы будете искать в другой книге, Price.xlsx указывает имя файла книги, из которой вы будете искать, Sheet1 означает имя листа, из которого вы будете искать, и A $ 1 : $ B $ 24 - это диапазон, из которого вы будете искать. Вы можете изменить их по своему усмотрению.
(2) После закрытия книги, из которой вы искали, формула автоматически обновится до = ВПР (B2; 'W: \ test \ [Price.xlsx] Sheet1'! $ A $ 1: $ B $ 24,2, FALSE) , W: \ test \ - это путь сохранения книги, из которой вы искали.
Пока все цены вернулись правильно, как показано на скриншоте слева. И эти цены будут обновляться автоматически, если исходная книга, которую вы искали, изменилась.
 | Формула слишком сложна для запоминания? Сохраните формулу как запись Auto Text для повторного использования одним щелчком мыши в будущем! Подробнее . Бесплатная пробная версия |
Данные Vlookup и возвращаемые значения из другой закрытой книги с VBA
Может возникнуть путаница при настройке пути сохранения, имени файла и рабочего листа в функции VLOOKUP. Этот метод позволит легко решить эту проблему с помощью VBA.
1. нажмите другой + F11 клавиши, чтобы открыть окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модуль, а затем вставьте ниже код VBA в открывающееся окно модуля.
VBA: данные Vlookup и возвращаемые значения из другой закрытой книги
Внимание: Этот VBA будет возвращать значения в столбце, который находится на 2 столбца позади выбранного столбца. Например, я выбираю столбец B при применении этого VBA, значения возвращаются в столбце D. Если вам нужно изменить столбец назначения, узнайте код xRg.Offset (0, 2) .Formula = xString & GetColumn (xFCell.Column + 1) & "$" & xFCell.Row и заменить 2 на другой номер по мере необходимости.
3. нажмите F5 ключ или щелкните Запустите кнопку для запуска этого VBA.
4. В открывшемся диалоговом окне укажите диапазон данных, который вы будете искать, и щелкните значок OK кнопку.
5. Теперь выберите книгу, в которой вы будете искать значения в открывшемся диалоговом окне «Выбор книги», и нажмите кнопку Откройте кнопку.
Теперь все выбранные значения ищутся в указанной закрытой книге, и соответствующие значения возвращаются в указанном столбце. Смотрите скриншот:
Перед созданием своей первой программы в Excel необходимо разобраться в объектную модели приложения. Имея четкое понимания об объектной модели Excel вы сможете управлять практически всеми объектами, создавать/изменять листы, графики, отдельные файлы и многое другое. Возможно на первый взгляд она покажется довольно сложной, но не пугайтесь на конкретных примерах у вас сложится целостная картина.
Структура объектов
Проще всего рассматривать объектную модель как некое дерево или иерархическую структуру, так как каждый объект имеет свое ответвление. Кусочек этой структуры вы можете увидеть на рисунке далее.
Самый главный объект, вершина нашей структуры, это собственно само приложение Excel - объект Application. Какие объекты входят в Excel? правильно книги (файлы Excel), поэтому в объекте Application содержится коллекция из книг - объект Workbooks. Из чего состоят книги - из листов, диаграмм, соответственно внутри объекта Workbooks мы видим объект Worksheets и Charts. Идем дальше, лист в свою очередь состоит из строк, столбцов, ячеек это объекты Rows, Columns, Range.
Это, конечно, как вы понимаете только часть объектной модели Excel, мы перечислили только одни их самых основных объектов. Полное дерево объектов исчисляется сотнями объектов. Возможно она сейчас кажется сложной, не переживайте со временем вы начнете быстро в ней ориентироваться. Главное сейчас - это понять, что есть некие объекты, которые могут состоять из других объектов.
Объекты и коллекции объектов
Многие объекты, которые мы перечислили выше являются коллекциями. Коллекция - это группа однотипных объектов (или классов). Например Workbook s - это коллекция всех открытых в данный момент книг (объектов Workbook). А коллекция Worksheet s состоит из листов книги (объектов Worksheet), и так далее. Как правило все коллекции заканчиваются буквой S.
Давайте посмотрим, как обращаться к конкретным элементам коллекции в коде VBA.
Мы можем указать порядковый номер (начиная с единицы) элемента в коллекции. Worksheets(3) - в этом случае мы обращаемся к 3-му листу книги.
Мы также можем указать название листа в кавычках Worksheets("Название листа").
Аналогичным образом можно обратиться к конкретной книге Workbook("Названием книги").
Чтобы обратиться к объекту Excel (к листу, или ячейке) необходимо указать ссылку на этот объект, перечислив полный путь к нему в соответствии с объектной моделью.
Например, для ячейки A1 полная ссылка будет выглядеть следующим образом:
На самом деле полный путь писать не обязательно. Application - можно практически всегда не указывать. Workbooks("Название книги") - можно не указывать, если необходимо обратиться к ячейке из активной книги, а Worksheets("Название листа") можно не писать в случае если код должен выполнять действия на активном листе. Т.е. в можно упростить до:
Но всегда имейте ввиду, что это будет ячейка на активном листе.
Свойства объектов
Все объекты имеют свойства и методы. Например, объект Range (диапазон ячеек) имеет свойство Value, в котором содержится значение.
Вы можете получать значения из свойств объекта, просто указав путь к нужному свойству. Следующий код выведет значение ячейки A1 на активном листе.
А следующий код присвоит свойству Value новое значение 12345.
Протестируйте эти функции в своем файле.
Кстати, есть свойства объектов, которые доступны только для чтения. Т.е. вы сможете только получить значения этих свойств, но не сможете присвоить им другие значения. Например, свойство Range("A1").Address которое содержит адрес ячейки. При попытке записать новое значение в такое свойство будет возникать ошибка.
Или же свойство Worksheets.Count - вы можете только получить количество листов, но не можете изменить количество листов, задав новое свойство Count. Для этого существуют методы объектов.
Методы объектов
В отличие от свойств, которые просто сохраняют или передают значение, методы выполняют какие-либо действия с объектом. Например метод Worksheets.Add создает новый пустой лист в книге. Еще один пример это метод Clear, который позволяет очистить содержимое ячеек. Следующий код очистит данные и форматы из диапазона ячеек A1:C10.
Есть аналогичная функция, которая не удаляет при этом форматирование ячеек, а только очищает их от значений
Методы могут иметь дополнительные аргументы. Например, метод AddComment имеет обязательный аргумент Text, в котором необходимо передать текст комментария к ячейке.
Также можно использовать следующий код для вызова методов. Возможно это более привычная запись, когда по аналогией с функцией Excel мы в скобках передаем аргументы этой функции.
Приемы и лайфхаки
Автоматическое заполнение кода по начальным символам
Часто, особенно на первых порах, вы не запомните точное название всех объектов, но будете помнить их первые символы. В редакторе кода вы можете использовать отобразить список названий и автоматически записать объект по первым символам. Для этого используйте сочетание клавиш Ctrl+ J и увидите список из предложенных вариантов.
Просмотр свойств и методов у объекта
Когда вы будете писать свои программы, то редактор кода VBE будет подсказывать список свойств и методов у объекта. Чтобы отобразить этот список достаточно ввести объект, например, Worksheets и поставить точку, отобразится список свойств и методов. Выберите нужный с помощью мышки или же используйте стрелки. Вы также можете начать писать название свойства и редактор кода подберет подходящее. Когда выбор сделан - нажимайте клавишу Tab.
Явно указывайте тип переменных
Предыдущий лайфхак не будет работать для созданных локальных переменных до тех пор, пока вы явно не укажите их тип. Например, если вы захотели создать переменную MySheet в которой хотите хранить текущий лист. То список свойств и методов у такой переменной вы не увидите. Но стоит добавить строчку кода и явно указать тип переменной:
И подсказки снова заработают.
Быстрый просмотр справки
Все свойства и методы знать невозможно, так как их тысячи. Первое время вы будете активно пользоваться справкой. Советую использовать официальную справку на сайте Microsoft.
Чтобы быстро посмотреть информацию по интересующему вас объекту, свойству или методу просто поставьте курсор на интересующий элемент кода и нажмите клавишу F1. В окне браузера откроется официальная справка по этому элементу.
Поиск по объектной модели
Также вначале пути у вас постоянно будут возникать вопрос "А как называется свойство, которое отвечает за. ". Иногда мы и не заем есть ли такое свойство/метод вообще. Хороший вариант изучения - просмотр объектной модели. Переходите в редактор кода VBA и нажимайте клавишу F2. Откроется окно с поиском по объектной модели. Тут вы найдете все имеющиеся свойства, методы, события и прочие элементы в модели. Просматривайте их, как правило в названии элемента заложена его суть и вы найдете то что искали. А в процессе поиска будете запоминать и другие элементы, которые будут попадаться на глаза.
Давайте разберем на примере. Допустим нам надо защитить лист, но мы не знаем как точно зазывается свойство или метод.
- Открываем объектную модель клавишей F2.
- Так как мы хотим защитить лист, то логично предположить, что это метод в объекте Worksheet. Введем его в строку поиска и нажмем бинокль.
- В результатах поиска выбираем наш объект Worksheet и просматриваем его элементы.
- Находим метод Protect (Защита).
- Мы также можем просмотреть описание всех аргументов этого метода
- Конечно если что-то не понятно, то жмем клавишу F1 и открываем справку по этому методу с подробным описанием каждого аргумента.
На этом данную статью завершим, а в следующих мы более подробно поговорим про самый распространенный объект Excel - объект Range.
В приложении Excel все данные как правило находятся в ячейках на листах, с которыми макросы работают как с базой данных. Поэтому, начинающему программисту VBA важно понимать как читать значения из ячейки Excel в переменные или массивы и, наоборот, записывать какие-либо значения на лист в ячейки.
Обращение к конкретной ячейке
Прежде чем читать или записывать значение в ячейке, нужно определиться с тем, как можно указать какая именно ячейка нам необходима.
Полный путь к ячейке A1 в Книге1 на Листе1 можно записать двумя вариантами:
Пример 1: Обратиться к ячейке A3 находящейся в Книге1 на Листе1
Однако, как правило, полный путь редко используется, т.к. макрос работает с Книгой, в которой он записан и часто на активном листе. Поэтому путь к ячейке можно сократить и написать просто:
Пример 2: Обратиться к ячейке A1 в текущей книге на активном листе
Если всё же путь к книге или листу необходим, но не хочется его писать при каждом обращении к ячейкам, можно использовать конструкцию With End With. При этом, обращаясь к ячейкам, необходимо использовать в начале "." (точку).
Пример 3: Обратиться к ячейке A1 и B1 в Книге1 на Листе2.
Так же, можно обратиться и к активной (выбранной в данный момент времени) ячейке.
Пример 4: Обратиться к активной ячейке на Листе3 текущей книги.
Чтение значения из ячейки
Есть 3 способа получения значения ячейки, каждый из которых имеет свои особенности:
По-умолчанию, если при обращении к ячейке не указывать способ чтения значения, то используется способ Value.
Пример 5: В ячейке A1 активного листа находится дата 01.03.2018. Для ячейки выбран формат "14 марта 2001 г.". Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.
Пример 6: В ячейке С1 активного листа находится значение 123,456789. Для ячейки выбран формат "Денежный" с 3 десятичными знаками. Необходимо прочитать значение ячейки всеми перечисленными выше способами и отобразить в диалоговом окне.
При присвоении значения переменной или элементу массива, необходимо учитывать тип переменной. Например, если оператором Dim задан тип Integer, а в ячейке находится текст, при выполнении произойдет ошибка "Type mismatch". Как определить тип значения в ячейке, рассказано в следующей статье.
Пример 7: В ячейке B1 активного листа находится текст. Прочитать значение ячейки в переменную.
Таким образом, разница между Text, Value и Value2 в способе получения значения. Очевидно, что Value2 наиболее предпочтителен, но при преобразовании даты в текст (например, чтобы показать значение пользователю), нужно использовать функцию Format.
Запись значения в ячейку
Осуществить запись значения в ячейку можно 2 способами: с помощью Value и Value2. Использование Text для записи значения не возможно, т.к. это свойство только для чтения.
Пример 8: Записать в ячейку A1 активного листа значение 123,45
Все три строки запишут в A1 одно и то же значение.
Пример 9: Записать в ячейку A2 активного листа дату 1 марта 2018 года
В данном примере тоже запишется одно и то же значение в ячейку A2 активного листа.
Визуальное отображение значения на экране будет зависеть от того, какой формат ячейки выбран на листе.
Читайте также: