Хитрости »
Основные понятия (22)
Сводные таблицы и анализ данных (7)
Графики и диаграммы (5)
Работа с VB проектом (11)
Power BI и Power Query (11)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (62)
Разное (37)

Как получить данные из закрытой книги?

Достаточно часто появляется вопрос: как извлечь данные из закрытой книги Excel через VBA? Звучит может быть странновато, но это так: вопрос регулярно поднимается на форумах. Собственно, именно в связи с этим и появилась на свет данная статья. В принципе ничего сложного в задаче нет. При этом получить данные можно разными способами, в том числе при помощи функций пользователя(UDF).
Попробуем разобраться с некоторыми методами, их плюсами и минусами.

Получение данных из закрытой книги из процедуры

Данный код работает достаточно медленно, но с его помощью можно "вытащить" из закрытой книги значения сразу нескольких ячеек. Код ниже работает быстрее, но с его помощью можно извлечь значения лишь одной ячейки:

Если честно, сам я не очень-то люблю ни один из данных методов, т.к. они совершенно лишены гибкости. Я предпочитаю открывать книгу. Делаю это, скрывая от пользователя при помощи свойства ScreenUpdating объекта Application.

Есть и более экзотический метод - при помощи GetObject:

При таком подходе пользователь разницы не увидит, а действия можно производить с ячейками разные: и сравнение, и отбор по критериям, и фильтровать, и сортировать и т.д. Плюс из книги можно переносить не только значения ячеек, но и форматы, формулы. Но выбирать метод получения значений из закрытых книг вам. Все зависит от ситуации. Все указанные коды работают. Если не работают - то проверьте верно ли указаны все исходные данные(имя книги и расширение, имя листа, путь к папке с книгой).


Получение данных из закрытой книги при помощи UDF
Тот же код, что уже был рассмотрен выше, но оформленный в виде UDF(функции пользователя):

Синтаксис функции (вызов с листа):
=Get_Value_From_Close_Book("C:\Книга1.xls";"Лист1";"B1")
sWb - полный путь до книги, данные из которой необходимо извлечь ("C:\Книга1.xls")
sShName - имя листа в указанной книге, данные из которого необходимо извлечь ("Лист1")
sAddress - адрес ячейки(диапазона) данные которой необходимо получить ("B1")

Чтобы получить массив ячеек(например B1:B10), необходимо выделить необходимое количество ячеек и ввести в них эту функцию, как формулу массива.
Думаю, не надо пояснять, что любой аргумент может быть задан не статичным текстом, а ссылкой на ячейку с этим текстом. Именно в этом и преимущество использования именно функций, а не процедур.


ПОЛУЧЕНИЕ ДАННЫХ ПРИ ПОМОЩИ ADO
Так же есть еще один достаточно экзотический метод получения данных из действительно закрытой книги - через ADO(ActiveX Data Objects). По сути это получение данных через запрос SQL, используя для этого технологию ADO.

Вызывать эту функцию следует из другой процедуры или функции. Пример процедуры, для вызова этой функции:

Для вызова функции Extract_Value_ADO непосредственно с листа(в виде функции UDF) придется несколько изменить приведенный выше код функции, либо извлекать функцией значение только одной ячейки, что будет не очень экономично с точки зрения ресурсов и использование для этого ADO будет слишком неоправданным. Если кому необходимо, то для вызова функции с ячейки листа и возврата значения одной ячейки, необходимо заменить строку:

на такую:

Синтаксис вызова с листа в таком случае будет следующим:
=Extract_Value_ADO("C:\"; "Книга1.xls"; "Лист1"; "A1")
Важно: если данные извлекаются только из одной ячейки, то следует указать две ячейки: А1:А2. Это особенность работы с запросами

Если же необходимо извлекать данные диапазона ячеек, то в этом случае можно применить такую функцию:

Синтаксис вызова с листа точно такой же как и в функции выше, только нужно будет выделить необходимое количество ячеек и ввести в них эту функцию, как формулу массива.:
=Extract_Value_ADO_Sh("C:\"; "Книга1.xls"; "Лист1"; "A1:B10")
sPath - путь к папке с книгой, данные из которой необходимо извлечь ("C:\")
sWb - имя книги, включая расширение(.xls в примере), данные из которой необходимо извлечь ("Книга1.xls")
sShName - имя листа в указанной книге, данные из которого необходимо извлечь ("Лист1")
sAddress - адрес ячейки(диапазона) данные которой необходимо получить ("A1")
Важно: если данные извлекаются только из одной строки, то следует все равно указать минимум две строки: А1:B10. Это особенность работы с запросами. При попытке указать только одну строку А1:A10 функция вернет значение ошибки. При этом первая строка воспринимается как заголовки. Т.е. данные должны начинаться как минимум со второй строк(A2), а в A1 - заголовок
Хоть эта функция имеет определенные недостатки - она может быть в разы быстрее предыдущей.


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Печать Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных ссылки
Обсуждение: 122 комментария
  1. Павел:

    =СУММЕСЛИ(Extract_Value_ADO_Sh("C:\"; "Книга1.xls"; "Лист1"; "A1:A11");5;Extract_Value_ADO_Sh("C:\"; "Книга1.xls"; "Лист1"; "B1:B11"))
    выдаёт # ЗНАЧ. Что я делаю не так?
    Есть какая нибудь другая реальная альтернатива без открывания файла(даже в тени)? Прост у меня много файлов с одним названием и бывает что один из них даже открыт, а нужно собрать с них данные.

    0
  2. Виталий:

    В методе с GetObject переменная, в которую загружены данные, обнуляется сразу после закрытия этого файла. Переменная глобальная типа Object, процедура выполняется из модуля, в чём может быть причина?

    0
    • Виталий,
      vData - будет содержать значения
      objCloseBook - примет значение Nothing, т.к. объект, на который она ссылается закрыт, значит выгружен из памяти. И неважно на каком уровне объявлена эта переменная - объект закрыт, значит недоступен.

      0
      • Виталий:

        В vData у меня загружается ListObject. Всё хорошо, но стоит закрыть файл, и в vData все поля . Пробовал и с GetObject, и с CreateObject. Специально сделал vData глобальной, думал проблема внутри процедуры.

        0
        • Виталий, ListObject - это ссылочный объект. И если закрыть книгу - он тоже обнулится, т.к. доступа к самому объекту уже не будет. Подучите чуточку мат.часть в области работы с объектами. И еще раз напишу: хоть как объявите vData - если пытаетесь в неё загнать ссылку на объект - он обнулится при закрытии книги.

          0
          • Виталий:

            Дмитрий, возможно, в моём случае следует перечитать ListObject в двумерный массив. Спасибо за ответы.

            0
  3. Павел:

    Добрый день!
    Есть вопрос касательно вышеуказанного кода с использованием ScreenUpdating или GetObject. Что нужно поменять в коде, чтобы копировалось не значение, а формат? Заранее благодарю.

    0
  4. Возник еще вопрос, использую третий код сверху. Когда запускаю код прямо из VB, все отлично. А когда нахожусь на листе и зыпускаю с помощью комбинации кнопок - не срабатывает. Т.е. открывает вспомогательную книгу и на ней останавливается.

    0
Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Тренинги

Заказать
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2017 Excel для всех  Войти
Авторизация
*
*
Регистрация
*
*
*
Пароль не введен
*
captcha
Генерация пароля