Поиск Excel vba в другой книге
Обновлено: 21.11.2024
Excel чрезвычайно мощен, даже используя только базовые функции добавления данных в ячейки и электронные таблицы, сортировки и обработки этих данных в прекрасную работу сотового удовольствия. Тем не менее, Excel способен на гораздо больше, чем позволяет стандартное редактирование содержимого ячеек, благодаря магии сценариев Visual Basic для приложений или VBA.
Мы кратко рассмотрим один простой пример использования VBA в Excel для извлечения значений из одного листа , но это лишь верхушка айсберга того, что Excel и VBA могут делать вместе.
Доступ к редактору Visual Basic
Для начала вам нужно найти редактор Visual Basic в Excel. Поиск этого зависит от того, какую версию Excel вы используете, но для большинства современных версий редактор Visual Basic можно найти на вкладке «Разработчик» на ленте меню.
Найдя его, просто нажмите «Макросы» , введите имя макроса (в этом примере мы будем использовать MySum), затем нажмите «Создать», чтобы открыть редактор и начать создание сценария.
Сценарий
Оказавшись в редакторе, вы увидите объявление макроса MySum, который представляет собой стандартную функцию vba, которую мы можем редактировать в соответствии с нашими требованиями.
В нашем примере, возможно, у нас есть рабочий лист с названием "Продажи", и мы хотим использовать этот макрос для суммирования (суммы) первых 25 значений в столбце B.
Таким образом, чтобы начать наш скрипт, нам нужно выбрать соответствующий рабочий лист с именем Sales , а затем, используя этот выбранный объект , сузить наш выбор, захватив определенные ячейки (известные как диапазон ячеек), которые мы хотим суммировать.
Теперь нам действительно нужно получить общее количество или сумму всех этих значений, поэтому мы заключаем предыдущий вызов объекта в WorksheetFunction of Sum, например так:
Но это не последний шаг. В нашем примере мы хотим извлечь эту сумму из рабочего листа «Продажи», а затем вставить это итоговое значение в текущую ячейку, которую мы выбрали при запуске этого макроса.
Для этого мы будем использовать объект ActiveCell, установив его значение равным нашей сумме:
Хорошо, теперь наш макрос завершен, так что давайте сохраним и вернемся к нашей настоящей электронной таблице, чтобы проверить ее.
Запуск макроса
Чтобы убедиться в его работоспособности, выберите другую таблицу, а затем выберите одну ячейку, в которую вы хотите вставить сумму.
Чтобы запустить сохраненный макрос, просто вернитесь на вкладку "Разработчик", снова выберите "Макросы" и выберите свой макрос MySum. Содержимое ячейки теперь должно быть заменено суммой указанных нами значений столбца Продажи B.
Я пытаюсь написать макрос, который позволяет пользователю выбрать ячейку, содержащую уникальное значение, запустить макрос, предлагающий пользователю выбрать книгу для "поиска" этого значения и вернуть номер строки, в которой это значение был найден в исходной книге.
Пример:
- Выделите ячейку с "ABC123" в Workbook1
- Запустите макрос
- Выберите отдельную книгу (Workbook2)
- Макрос ищет выбранную Workbook2 для "ABC123"
- Если он найден, он возвращает номер строки, в которой это уникальное значение "ABC123" было найдено в Workbook2, в ИСХОДНУЮ Workbook1.
Будем ОЧЕНЬ признательны за любую помощь.
Факты об Excel
Если Excel сообщает, что у вас есть ссылки, но вы не можете их найти, перейдите в раздел "Формулы", "Диспетчер имен". Найдите старые ссылки на мертвые книги и удалите их.
SnareStudio
Новый участник
Доменик
MrExcel MVP
Несколько замечаний.
- Вы не упомянули, нужно ли искать каждый лист в выбранной книге, поэтому я предположил, что нужно искать каждый лист.
- Кроме того, вы не упомянули, где вы хотели бы вернуть номер строки, поэтому я предположил, что вы хотите, чтобы он возвращал одну ячейку справа от выбранной ячейки.
SnareStudio
Новый участник
Макрос выполнит поиск только на листе Sheet1 (или как называется первый лист) выбранной книги. Нужно будет только найти этот первый лист.
Я просто хочу, чтобы номер строки был присвоен переменной для последующего использования. Его не нужно устанавливать в значение ячейки. В конце концов он будет установлен в формулу ячейки.
SnareStudio
Новый участник
Я также хотел, чтобы он перебирал выбранные ячейки (а не только одну ячейку). Весь выбор будет в одном столбце.
Доменик
MrExcel MVP
SnareStudio
Новый участник
У меня есть столбец (столбец B) серийных номеров в книге отчетов (Workbook1). Я выберу диапазон (скажем, первые 5). Затем я собираюсь запустить макрос, который предложит мне открыть другой рабочий лист, содержащий более подробную информацию для каждого SN в выборе (Workbook2).
Затем он просматривает выборку в Workbook1 и находит соответствующий номер строки для каждого совпадающего значения ячейки в Workbook2.
Затем он свяжет столбец E + строку выбранной ячейки в Workbook1 со столбцом E + значением строки, где было найдено совпадение в Workbook2. Просто ссылаясь на эту ячейку как на формулу. Пример: ='C:\Example\[Workbook2.xlsx]Sheet1'!$E$8
Найти — очень полезная и мощная функция Excel. Вместе с функцией Offset вы также можете изменить ячейки вокруг найденной ячейки. Ниже приведены несколько основных примеров, которые вы можете использовать в своем коде.
Используйте «Найти», чтобы выбрать ячейку
В приведенных ниже примерах выполняется поиск в столбце A листа с именем "Sheet1" для значения поля ввода. Измените имя листа или диапазон в коде на свой лист/диапазон.
Совет. Вы можете заменить поле ввода строкой или ссылкой на ячейку следующим образом:
FindString = "SearchWord"
Или
FindString = Sheets("Sheet1").Range( "D1").Значение
В этом примере будет выбрана первая ячейка в диапазоне со значением InputBox.
Если у вас есть более одного вхождения значения, будет выбрано последнее вхождение.
Если у вас есть даты в столбце A, в этом примере будет выбрана ячейка с сегодняшней датой. Примечание. Если ваши даты являются формулами, возможно, вы должны изменить xlFormulas на xlValues в приведенном ниже примере. Если ваши даты являются значениями, xlValues не всегда работает с некоторыми форматами дат.
Отметить ячейки с одинаковым значением в столбце A в столбце B
В этом примере выполняется поиск в Sheets("Sheet1") в столбце A каждой ячейки со словом "ron" и используется параметр "Смещение", чтобы отметить ячейку в столбце справа. Примечание: вы можете добавить больше значений в массив MyArr.
Окрашивание ячеек с одинаковым значением в диапазоне, на листе или на всех листах
В этом примере все ячейки в диапазоне Sheets("Sheet1").Range("B1:D100") окрашиваются в "ron". См. комментарии в коде, если вы хотите использовать все ячейки на листе. В этом примере я использую индекс цвета, чтобы присвоить всем ячейкам с "ron" цвет 3 (обычно это красный цвет)
Совет. Для изменения цвета шрифта см. примеры строк под макросами.
Пример для всех рабочих листов в книге
Изменить цвет шрифта вместо внутреннего цвета
'Изменить цвет заливки на "без заливки" во всех ячейках
.Interior.ColorIndex = xlColorIndexNone
'Изменить шрифт в столбце на автоматический
.Font.ColorIndex = 0
Rng.Interior.ColorIndex = myColor(I)
С
Rng.Font.ColorIndex = myColor(I)
Копировать ячейки на другой лист с помощью команды «Найти»
В приведенном ниже примере будут скопированы все ячейки с адресом электронной почты из диапазона Sheets("Sheet1").Range("A1:E100") на новый рабочий лист в вашей книге. Примечание. Я использую в коде xlPart вместо xlWhole, чтобы найти каждую ячейку с символом @.
Если вы хотите заменить только значения на листе, вы можете использовать Заменить вручную (Ctrl+h) или использовать Заменить в VBA. Приведенный ниже код заменяет ron на dave во всем листе. Измените xlPart на xlWhole, если вы хотите заменить ячейки только на ron.
Здесь я делюсь тремя простыми методами, показывающими, как читать и извлекать данные в том виде, в каком они есть, из другой книги Excel на вашем компьютере или с удаленного компьютера. Во время извлечения или извлечения данных исходный файл может находиться в закрытом или открытом состоянии.
Извлечь данные из определенного листа в другой книге
Допустим, у меня есть файл Excel (исходный файл), содержащий данные в табличном формате.
Чтобы извлечь данные из другой книги с помощью макроса, необходимо указать имя книги и полный путь к процедуре (или фрагменту кода) для обработки. Вы можете жестко закодировать имя файла и путь, сохранить его в переменной, а затем обработать. Однако здесь я использую метод FileDialog() для выбора исходного файла (книги).
Поскольку я использую метод FileDialog, мне нужна кнопка ActiveX на листе. Поэтому сначала добавьте кнопку ActiveX. Напишите код в событии нажатия кнопки.
Сначала я создаю объект FileDialog, чтобы открыть диалоговое окно файла. Я могу выбрать книгу из любого места на компьютере. Затем я вызываю процедуру «readExcelData()», где у меня есть код для чтения исходного файла.
Что внутри процедуры readExcelData() . Процедура принимает параметр (или аргумент), имя исходного файла и полный путь к нему. Теперь взгляните на это свойство Application.ScreenUpdating, которое я установил как «false». Установка для этого свойства значения false гарантирует, что макрос (или код) будет работать быстро и гладко, поскольку он не будет обновлять экран. Подробнее об этом объекте читайте здесь. Вы должны установить значение true после выполнения кода.
Далее я открываю исходный файл. Он находится в состоянии только для чтения, что означает, что в течение всего этого процесса вы не можете ничего делать с исходным файлом, даже если он открыт.
Задайте src = Workbooks.Open(sTheSourceFile, True, True) ' Открыть исходный файл.
Получив доступ к файлу Excel, я получу общее количество строк и столбцов и прочитаю все данные таблицы в файле.
Извлечь только определенный диапазон данных из другой книги
В первом примере выше я извлекаю данные из каждой строки и столбца в Sheet1 . Для этого я использую свойство UsedRange. Неважно, сколько таблиц у вас есть в исходном файле. Он извлечет из него все данные.
Однако вы можете ограничить объем данных, которые вы хотите извлечь из исходного файла. Все, что вам нужно сделать, это указать диапазон.
Предположим, вы хотите получить или извлечь данные только из второго столбца (столбца B) листа Sheet1. Вы можете использовать этот код.
В этом примере я использовал метод Range(), чтобы указать диапазон, из которого я буду извлекать данные. Метод FileDialog() остается прежним.
👉 Теперь, если вы хотите извлечь данные из нескольких файлов и показать их в одном файле, но на разных листах, вы должны прочитать этот пост.
Макрос для извлечения данных из нескольких листов из другой книги
Макрос для извлечения данных с нескольких листов из другой книги очень прост. Однако метод, которым я здесь делюсь, немного отличается от первых двух примеров, которые я объяснил выше.
Сначала посмотрим на код.
Используя метод диалогового окна файла, вы можете выбрать книгу Excel, в которой есть данные на нескольких листах. Часть извлечения данных находится внутри процедуры readExcelData() .
Открыв исходный файл, я просматриваю каждый лист и копирую данные как есть (из используемого диапазона ) в целевую книгу.
Здесь я поделился тремя различными способами, чтобы объяснить, как извлекать данные из другой книги с помощью макроса. У вас есть разные методы для разных требований. Однако мне больше всего нравится третий метод, при котором данные просто копируются с нескольких листов и записываются в целевую книгу.
Вы можете использовать аналогичные методы для извлечения данных из нескольких книг Excel и записи их в целевую книгу.
Читайте также: