Макрос в эксель на заполнение таблицы из нескольких файлов
Доброго времени суток!
Это моя первая сложная работа с макросами. Части поставленной задачи не могу разобрать. Помогите, пожалуйста, записать код макроса на следующую таблицу. На листах 1 и 2 есть данные, которые постоянно пополняются. На листе 3 должна формироваться таблица с объединенными данными с этих двух листов по номеру заказа. ФИО могут отличаться на этих двух листах, тогда по ячейкам в обобщенной таблице факта и плана может быть "пусто".
Еще на листе 4 есть таблица данных, из которой также по данным номера заказа будет формироваться таблица на листе 5. Из всплывающего списка в ячейке А2 будет выбираться номер заказа, и по этому номеру будет заполняться таблица. К датам легко применима формула СУММАЕСЛИ, но! К заказчику и № договора ее применить нельзя. Может быть есть другая формула, которую можно применить, или же макросом записать такое действие?
Буду благодарна Вашей помощи.
Доброго времени суток!
Это моя первая сложная работа с макросами. Части поставленной задачи не могу разобрать. Помогите, пожалуйста, записать код макроса на следующую таблицу. На листах 1 и 2 есть данные, которые постоянно пополняются. На листе 3 должна формироваться таблица с объединенными данными с этих двух листов по номеру заказа. ФИО могут отличаться на этих двух листах, тогда по ячейкам в обобщенной таблице факта и плана может быть "пусто".
Еще на листе 4 есть таблица данных, из которой также по данным номера заказа будет формироваться таблица на листе 5. Из всплывающего списка в ячейке А2 будет выбираться номер заказа, и по этому номеру будет заполняться таблица. К датам легко применима формула СУММАЕСЛИ, но! К заказчику и № договора ее применить нельзя. Может быть есть другая формула, которую можно применить, или же макросом записать такое действие?
Буду благодарна Вашей помощи. Искра
Это моя первая сложная работа с макросами. Части поставленной задачи не могу разобрать. Помогите, пожалуйста, записать код макроса на следующую таблицу. На листах 1 и 2 есть данные, которые постоянно пополняются. На листе 3 должна формироваться таблица с объединенными данными с этих двух листов по номеру заказа. ФИО могут отличаться на этих двух листах, тогда по ячейкам в обобщенной таблице факта и плана может быть "пусто".
Еще на листе 4 есть таблица данных, из которой также по данным номера заказа будет формироваться таблица на листе 5. Из всплывающего списка в ячейке А2 будет выбираться номер заказа, и по этому номеру будет заполняться таблица. К датам легко применима формула СУММАЕСЛИ, но! К заказчику и № договора ее применить нельзя. Может быть есть другая формула, которую можно применить, или же макросом записать такое действие?
Буду благодарна Вашей помощи. Автор - Искра
Дата добавления - 13.11.2013 в 23:31
А надо из всего этого сформировать табличку в Excel - приблизительно такого вида:
На помощь придёт функция DATfolder2Array
Код функции DATfolder2Array:
Комментарии
Последнюю строку в статье прочитайте
Надо скопировать в конец кода функцию из другой статьи
Не понимаю, что я делаю не так
Даже без исправлений выдает ошибку "sub or function not defined"
У кого заработало ?
У меня возникла проблема с
Function DATfolder2Array(ByVal FolderPath$, ByVal ColumnsCount As Long, _
ByVal TextColumns$, ByRef ErrorsArr) As Variant
Есть ли работающий вариант?
Найдите в коде строку
Запись ro = Replace(ro, vbTab, ";") заменяет разделитель табуляцию на точку с запятой
По аналогии, можно выполнить и другие замены? для других разделителей
Например, чтобы запятая тоже считалась разделителем, то дополните строку еще одной командой замены:
А можно указать несколько разделителей, если да, то как?
Цифра 7 в этой строке DataArr = DATfolder2Array(Папка, 7, "1,2,4,5", ErrorsArray) я так понимаю указывает на количество столбцов ?
Если до то как сделать без явного указания, чтобы определял из текстовика с разделением через ";" ?
Большое спасибо за Ваши разработки.
Здравствуйте, Иван.
В вашем случае нужен совсем другой, более сложный, макрос. Можем сделать под заказ.
Нет, там функция считывает файл целиком
Если требуется частично файл загружать - это надо другой макрос делать
Спасибо за ответ.
По ходу возник еще вопрос: с помощью функции ReadTXTfile можно выбрать не все строки файла, а только определенные (скажем со 2 по 20)
Здравствуйте, Владимир
Код написан под разделитель столбцов ; (точка с запятой)
Поменяйте в коде ; на , — и всё
Всего в 2 строках надо поменять:
Добрый день, подскажите если не сложно.
В файлах данные разделяются при помощи - ","
Сложно переделать ваш код для того что бы он распознавал такое разделение?
Беру свои слова обратно, все работает
Пробовал запустить выполнение макроса на сбор данных из несколькоих txt файлов в Excel, но ничего не получается. Нельзя ли выложить пример рабочего файла?
Аналогичная проблема, не могу делать удобоваримым под CSV.
У меня тоже с прибора каждые 3 часа файл вылазит.
Я так понимаю что для импорта файлов с таким форматом строки достаточно в коде сделать так: ' считываем данные из все файлов .DAT в папке в двумерный массив
DataArr = CSVfolder2Array(ПапкаДляФайлов$, 66, "2", ErrorsArray), однако импорта не происходит.
А именно и будет много однотипных файлов: каждые сутки прибор формирует CSV файл с именем типа дд_мм_гг.scv, и строки типа:(22:31:35;Time; 1,02400e+03;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;) т.е. время,служебная информация и далее непосредственно данные до 64 значений. Требуется импорт столбца времени и столбцов данных из этих файлов, но при этом в Excel первым столбцом должна быть еще и дата из имени файла или должен создаваться новый лист с именем файла, т.е. датой.
А почему вы решили взять за основу именно этот макрос?
Этот код предназначен для сложной обработки папки с однотипными файлами,
а для одиночного файла макрос подойдёт попроще.
У меня на сайте много примеров таких макросов - выбирайте любой.
Например, можете взять за основу макрос загрузки CSV в Excel
Есть CSV файл состоящий из 66 столбцов, как изменить макрос чтобы импортировать данные из файла?
не показывает то что нужно,я не пойму где в этом макросе указывать строчки которые нужны из документов
Мой макрос загружает ВСЕ строки из текстовых файлов
(а в примере, ссылку на который я дал в предыдущем комментарии, - все строки, кроме первых)
Более того, при вызове функции нужно указывать количество загружаемых столбцов.
К тому же, этот код не универсальный - прямо в функции прописаны разделители строк и полей (точка с запятой, и перевод строки)
Если у вас другие разделители - либо изменяйте код функции, либо ждите, пока я опубликую новую статью с универсальным вариантом макроса загрузки данных из текстовых файлов.
В другой статье я выложил пример использования этого макроса:
(смотрите пример №2 - нажатием первой (зеленой) кнопки скачаете файлы, а нажатием второй (желтой) кнопки, загрузите данные из файлов CSV на лист)
У меня тож не показывает то что нужно,я не пойму где в этом макросе указывать строчки которые нужны из документов,может в этом все дело.
Может и хороший макрос, но.. Увы, не работает.
Добавьте ниже этого макроса код функции ReadTXTfile, взяв его здесь:
эту ошибку исправил, теперь пишет что команда не существует в этом месте newtxt = ReadTXTfile(FolderPath$ & filename)именно чтение TXT, и указывает на Function DATfolder2Array(ByVal FolderPath$, ByVal ColumnsCount As Long, _
ByVal TextColumns$, ByRef ErrorsArr) As Variant что теперь то не так может быть?
PS: Код взят из рабочего файла - так что ошибок быть не должно.
макрос не запускается пишет значение вот этого кода неоднозначно: Function DATfolder2Array(ByVal FolderPath$, ByVal ColumnsCount As Long, _
ByVal TextColumns$, ByRef ErrorsArr) As Variant
что это может быть?
Добрый день. Стоит задача собирать данные из нескольких файлов с одинаковой структурой в один, имеющий аналогичную структуру. Имена файлов имеют одинаковое правило - 2016_07_ОП_*наименование*, то есть год, месяц, ОП-название формы, далее идет наименование отдела. Собирать данные нужно с конкретного листа и вставлять в файл на лист, с таким же наименованием (именовать лист нужно автоматически). Так же в сборочный файл нужно добавить две колонки - Год и Месяц. Брать данные для их заполнения нужно из имен файлов, то есть в данном случае всем скопированным строкам нужно поставить год - 2016 и месяц - 07.
Есть вот такой макрос, найденный на этом форуме (Извините за неудобства. Вставляю со всеми отступами, но они почему-то пропадают):
Сбор данных разных файлов, рассортированных по трем папкам
Доброго времени суток! Очень прошу всех помочь существует одна довольно заковыристая задачка.
Сбор информации с разных файлов
Кто может помочь в написании не сложного (а для меня очень сложного :) ) макроса. Суть в.
Сбор данных из разных книг
Добрый день! Помогите решить вопрос: Есть папка с 100+ файлами, там примерно одни и те же данные.
Из постановки задачи непонятно с какого именно конкретного листа брать данные, его имя или позиция.
Непонятно что Вы имеете ввиду под словом вставлять - заменять полностью старые данные или добавлять снизу к старым данным.
Укажите номера колонок для добавления года и месяца .
Из постановки задачи непонятно с какого именно конкретного листа брать данные, его имя или позиция - имя листа "Бюджет расходов и платежей"
Непонятно что Вы имеете ввиду под словом вставлять - заменять полностью старые данные или добавлять снизу к старым данным - может не до конца корректно изъясняюсь. попробую пояснить: взяли первый файл из папки, в нем взяли лист "Бюджет расходов и платежей", с него перенесли все данные в сводный файл, далее взяли следующий файл, аналогичный лист, данные добавляем под перенесенными ранее в сводный файл.
Укажите номера колонок для добавления года и месяца - год 12, месяц 13.
Так как не видно образца, то просто добавляя данные я добавлю и лишние шапки таблицы в середину данных.
По идее макрос, который я привел выше, берет только один раз шапку, а дальше только данные. Или это не так?
Вложил форму. Я удалил из нее лишние листы, оставил только тот, из которого нужно копировать.
В сводном нужно создать лист с таким же именем и переносить в него.
Решение
Огромное спасибо. Все работает.
Добавлено через 19 часов 51 минуту
Добрый день. С исполнителями оказалось все сложнее)
Задача немного изменилась.
1) Нужно чтобы собирались данные в открытый файл, а не чтобы он создавался. С этим я вроде бы справился, исключив эту строку "Workbooks.Add"
2) Помимо этой нужна еще следующая модификация макроса: файл, в который собираются данные имеет следующее имя (например) 2016_07_HR.xlsx Необходимо, чтобы этот файл обращался к папке с файлами и собирал из них только те строки, в которых в колонке А стоит HR.
3) Нужен обратный макрос, который разбивает сборочный файл на многие по следующим признакам:
- имя файлов содержит часть имени файла, который разбивается в части года и месяца, то есть 2016_07_
- разбивка идет по колонке B. То есть данные с общем значением в колонке B заносятся в отдельный файл.
- Остальная часть имени файла - значение колонки B. То есть если, к примеру, там стоит Екатеринбург, то создается файл 2016_07_Екатеринбург.xlsx и в него переносятся все строки из сборочного, со значением Екатеринбург.
- В итоге получается столько файлов, сколько уникальных значений в колонке B. Структура в них аналогична сборочному.
- Пользователь открывает файл, который нужно разбить, макрос будет в нем и выбирает папку в которой нужно создать файлы.
Здравствуйте, прошу помощи в доработке процедуры.
Имеется файл test.xlsm с макросом, который должен обрабатывать выбранные .xls файлы пользователем. И копировать из файлов определенные ячейки в табличку на первом листе test.xlsm.
1. Вызываю циклом выбранные файлы, но не могу понять открыть этот файл И скопировать оттуда нужную информацию в общею таблицу в файле test.xlsm Например ячейки B7 B9 B10 из открытого файла в b2 c2 d2 файла test.xlsm
Прикладываю файл примера с загружаемыми файлами.
Здравствуйте, прошу помощи в доработке процедуры.
Имеется файл test.xlsm с макросом, который должен обрабатывать выбранные .xls файлы пользователем. И копировать из файлов определенные ячейки в табличку на первом листе test.xlsm.
1. Вызываю циклом выбранные файлы, но не могу понять открыть этот файл И скопировать оттуда нужную информацию в общею таблицу в файле test.xlsm Например ячейки B7 B9 B10 из открытого файла в b2 c2 d2 файла test.xlsm
Прикладываю файл примера с загружаемыми файлами.
joyks
Имеется файл test.xlsm с макросом, который должен обрабатывать выбранные .xls файлы пользователем. И копировать из файлов определенные ячейки в табличку на первом листе test.xlsm.
1. Вызываю циклом выбранные файлы, но не могу понять открыть этот файл И скопировать оттуда нужную информацию в общею таблицу в файле test.xlsm Например ячейки B7 B9 B10 из открытого файла в b2 c2 d2 файла test.xlsm
Прикладываю файл примера с загружаемыми файлами.
Автор - joyks
Дата добавления - 08.01.2015 в 11:17
Sub Выбор_нескольких_файлов()
Dim i As Long '.
Dim il As Long
Dim a()
'диалог для выбора файла
Dim fd As FileDialog 'объектная переменная диалог выбора выбора файлов и папок
Set fd = Application.FileDialog(msoFileDialogFilePicker) 'тип диалога выбор файла
fd.AllowMultiSelect = True 'включить выбор множества файлов
fd.Filters.Clear 'очистить фильтры типов файлов, если он запомнил их
fd.Filters.Add "Файлы Excel-я", "*.xls;*.xlsx" 'выбор только файлов екселя
fd.Show 'открыт диалог
If fd.SelectedItems.Count = 0 Then 'если не выбрали файл то "ошибка"
MsgBox "Не выбрали файл"
Exit Sub
End If
'Заполняю шапку таблицы
Range("A1").Value = "№ анкеты"
Range("B1").Value = "Фамилия"
Range("C1").Value = "Имя"
Range("D1").Value = "Отчество"
For i = 1 To fd.SelectedItems.Count 'перебор имён файлов
With GetObject(fd.SelectedItems(i)) 'открываем
a = .Sheets(1).[b5:b10].Value 'берём данные в массив
.Close False 'закрываем без сохранения изменений (их правда и нет)
End With
il = Range("A" & Rows.Count).End(xlUp).Row + 1 'определяем последнюю строку
Cells(il, 1) = a(1, 1) ' в неё и перекладываем данные из массива
Cells(il, 2) = a(3, 1)
Cells(il, 3) = a(5, 1)
Cells(il, 4) = a(6, 1)
Next i
'форматирую всю таблицу
With Range("A1").CurrentRegion
.Borders.ColorIndex = 1 'сетка черного цвета
.Columns.AutoFit 'автовыравнивание
With .Rows(1) 'для 1 строки заголовка
.Font.Bold = True 'жирный шрифт
.Interior.ColorIndex = 38 'цвет фона
.HorizontalAlignment = xlCenter 'по центру
End With
End With
Sub Выбор_нескольких_файлов()
Dim i As Long '.
Dim il As Long
Dim a()
'диалог для выбора файла
Dim fd As FileDialog 'объектная переменная диалог выбора выбора файлов и папок
Set fd = Application.FileDialog(msoFileDialogFilePicker) 'тип диалога выбор файла
fd.AllowMultiSelect = True 'включить выбор множества файлов
fd.Filters.Clear 'очистить фильтры типов файлов, если он запомнил их
fd.Filters.Add "Файлы Excel-я", "*.xls;*.xlsx" 'выбор только файлов екселя
fd.Show 'открыт диалог
If fd.SelectedItems.Count = 0 Then 'если не выбрали файл то "ошибка"
MsgBox "Не выбрали файл"
Exit Sub
End If
'Заполняю шапку таблицы
Range("A1").Value = "№ анкеты"
Range("B1").Value = "Фамилия"
Range("C1").Value = "Имя"
Range("D1").Value = "Отчество"
For i = 1 To fd.SelectedItems.Count 'перебор имён файлов
With GetObject(fd.SelectedItems(i)) 'открываем
a = .Sheets(1).[b5:b10].Value 'берём данные в массив
.Close False 'закрываем без сохранения изменений (их правда и нет)
End With
il = Range("A" & Rows.Count).End(xlUp).Row + 1 'определяем последнюю строку
Cells(il, 1) = a(1, 1) ' в неё и перекладываем данные из массива
Cells(il, 2) = a(3, 1)
Cells(il, 3) = a(5, 1)
Cells(il, 4) = a(6, 1)
Next i
'форматирую всю таблицу
With Range("A1").CurrentRegion
.Borders.ColorIndex = 1 'сетка черного цвета
.Columns.AutoFit 'автовыравнивание
With .Rows(1) 'для 1 строки заголовка
.Font.Bold = True 'жирный шрифт
.Interior.ColorIndex = 38 'цвет фона
.HorizontalAlignment = xlCenter 'по центру
End With
End With
Sub Выбор_нескольких_файлов()
Dim i As Long '.
Dim il As Long
Dim a()
'диалог для выбора файла
Dim fd As FileDialog 'объектная переменная диалог выбора выбора файлов и папок
Set fd = Application.FileDialog(msoFileDialogFilePicker) 'тип диалога выбор файла
fd.AllowMultiSelect = True 'включить выбор множества файлов
fd.Filters.Clear 'очистить фильтры типов файлов, если он запомнил их
fd.Filters.Add "Файлы Excel-я", "*.xls;*.xlsx" 'выбор только файлов екселя
fd.Show 'открыт диалог
If fd.SelectedItems.Count = 0 Then 'если не выбрали файл то "ошибка"
MsgBox "Не выбрали файл"
Exit Sub
End If
'Заполняю шапку таблицы
Range("A1").Value = "№ анкеты"
Range("B1").Value = "Фамилия"
Range("C1").Value = "Имя"
Range("D1").Value = "Отчество"
For i = 1 To fd.SelectedItems.Count 'перебор имён файлов
With GetObject(fd.SelectedItems(i)) 'открываем
a = .Sheets(1).[b5:b10].Value 'берём данные в массив
.Close False 'закрываем без сохранения изменений (их правда и нет)
End With
il = Range("A" & Rows.Count).End(xlUp).Row + 1 'определяем последнюю строку
Cells(il, 1) = a(1, 1) ' в неё и перекладываем данные из массива
Cells(il, 2) = a(3, 1)
Cells(il, 3) = a(5, 1)
Cells(il, 4) = a(6, 1)
Next i
'форматирую всю таблицу
With Range("A1").CurrentRegion
.Borders.ColorIndex = 1 'сетка черного цвета
.Columns.AutoFit 'автовыравнивание
With .Rows(1) 'для 1 строки заголовка
.Font.Bold = True 'жирный шрифт
.Interior.ColorIndex = 38 'цвет фона
.HorizontalAlignment = xlCenter 'по центру
End With
End With
Даже если вы совсем новичок в Excel и в программировании на VBA, вы можете легко записать макрос и автоматизировать часть своей работы. В этой статье речь я расскажу все, что вам нужно знать, чтобы начать работу с созданием макросов в Excel.
Что такое макрос?
Для начала немного о терминологии.
Макрос - это код, написанный на встроенном в Excel языке VBA (Visual Basic for Application). Макросы могут создаваться как вручную, так и записываться автоматически с помощью так называемого макрорекодера.
Макрорекодер - это инструмент в Excel, который пошагово записывает все что вы выполняете в Excel и преобразует это в код на языке VBA. Макрорекодер создает очень подробный код (как мы увидим позже), который вы сможете при необходимости отредактировать в дальнейшем.
Записанный макрос можно будет запускать неограниченное количество раз и Excel повторит все записанные шаги. Это означает, что даже если вы ничего не знаете о VBA, вы можете автоматизировать некоторые задачи, просто записав свои шаги и затем повторно использовать их позже.
Теперь давайте погрузимся и посмотрим, как записать макрос в Excel.
Отображение вкладки "Разработчик" в ленте меню
Перед тем как записывать макрос, нужно добавить на ленту меню Excel вкладку "Разработчик". Для этого выполните следующие шаги:
- Щелкните правой кнопкой мыши по любой из существующих вкладок на ленте и нажмите «Настроить ленту». Он откроет диалоговое окно «Параметры Excel».
- В диалоговом окне «Параметры Excel» у вас будут параметры «Настроить ленту». Справа на панели «Основные вкладки» установите флажок «Разработчик».
- Нажмите «ОК».
В результате на ленте меню появится вкладка "Разработчик"
Запись макроса в Excel
Теперь давайте запишем очень простой макрос, который выбирает ячейку и вводит в нее текст, например "Excel".
Вот шаги для записи такого макроса:
Поздравляем! Вы только что записали свой первый макрос в Excel. Хотя макрос не делает ничего полезного, но он поможет нам понять как работает макрорекордер в Excel.
Теперь давайте рассмотрим код который записал макрорекодер. Выполните следующие действия, чтобы открыть редактор кода:
Вы увидите, что как только вы нажмете кнопку "Выполнить", текст "Excel" будет вставлен в ячейку A2 и выбрана ячейка A3. Это происходит за миллисекунды. Но на самом деле макрос последовательно выполнил записанные действия.
Примечание. Вы также можете запустить макрос с помощью сочетания клавиш Ctrl + Shift + N (удерживайте клавиши Ctrl и Shift, а затем нажмите клавишу N). Это тот же самый ярлык, который мы назначили макросу при его записи.
Что записывает макрос?
Теперь перейдем к редактору кода и посмотрим что у нас получилось.
Вот шаги по открытию редактора VB в Excel:
- Перейдите на вкладку "Разработчик".
- В группе "Код" нажмите кнопку "Visual Basic".
Вы также можете использовать комбинацию клавиш Alt + F11 и перейти в редактор кода VBA.
Рассмотрим сам редактор кода. Далее коротко опишем интерфейс редактора.
- Панель меню: содержит команды, которые можно использовать во время работы с редактором VB.
- Панель инструментов - похожа на панель быстрого доступа в Excel. Вы можете добавить к ней дополнительные инструменты, которыми часто пользуетесь.
- Окно проектов (Project Explorer) - здесь Excel перечисляет все книги и все объекты в каждой книге. Например, если у нас есть книга с 3 рабочими листами, она появится в Project Explorer. Здесь есть несколько дополнительных объектов, таких как модули, пользовательские формы и модули классов.
- Окно кода - собственно сам код VBA размещается в этом окне. Для каждого объекта, указанного в проводнике проекта, есть окно кода, например, рабочие листы, книги, модули и т. д. В этом уроке мы увидим, что записанный макрос находится в окне кода модуля.
- Окно свойств - вы можете увидеть свойства каждого объекта в этом окне. Я часто использую это окно для обозначения объектов или изменения их свойств.
- Immediate Window (окно предпросмотра) - На начальном этапе оно вам не пригодится. Оно полезно, когда вы хотите протестировать шаги или во время отладки. Он по умолчанию не отображается, и вы можете его отобразить, щелкнув вкладку «View» и выбрав опцию «Immediate Window».
Когда мы записали макрос "ВводТекста", в редакторе VB произошли следующие вещи:
- Был добавлен новый модуль.
- Макрос был записан с именем, которое мы указали - "ВводТекста"
- В окне кода добавлена новая процедура.
Поэтому, если вы дважды щелкните по модулю (в нашем случае модуль 1), появится окно кода, как показано ниже.
Вот код, который записан макрорекодером:
В VBA, любая строка , которая следует за ' (знак апострофа) не выполняется. Это комментарий, который предназначен только для информационных целей. Если вы удалите первые пять строк этого кода, макрос по-прежнему будет работать.
Теперь давайте пробежим по каждой строке кода и опишем что и зачем.
Код начинается с Sub, за которым следует имя макроса и пустые круглые скобки. Sub - сокращение для подпрограммы. Каждая подпрограмма (также называемая процедурой) в VBA начинается с Sub и заканчивается End Sub.
- Range("A2").Select - эта строка выбирает ячейку A2.
- ActiveCell.FormulaR1C1 = «Excel» - эта строка вводит текст "Excel" в активной ячейке. Поскольку мы выбрали ячейку A2 в качестве первого шага, она становится нашей активной ячейкой.
- Range("A3").Select - выбор ячейки A3. Это происходит, когда мы нажимаем клавишу Enter после ввода текста, результатом которого является выбор ячейки A3.
Надеюсь, что у вас есть некоторое базовое понимание того, как записывать макрос в Excel.
Обращаем внимание, что код, записанный через макрорекордер, как правило, не является эффективным и оптимизированным кодом. Макрорекордер часто добавляет дополнительные ненужные действия. Но это не значит, что не нужно пользоваться макрорекодером. Для тех, кто только изучает VBA , макрорекордер может быть отличным способом проанализировать и понять как все работает в VBA.
Абсолютная и относительная запись макроса
Вы уже знаете про абсолютные и относительные ссылки в Excel? Если вы используете абсолютную ссылку для записи макроса, код VBA всегда будет ссылаться на те же ячейки, которые вы использовали. Например, если вы выберете ячейку A2 и введете текст "Excel", то каждый раз - независимо от того, где вы находитесь на листе и независимо от того, какая ячейка выбрана, ваш код будет вводить текст "Excel" в ячейку A2.
Если вы используете параметр относительной ссылки для записи макроса, VBA не будет привязываться к конкретному адресу ячейки. В этом случае программа будет "двигаться" относительно активной ячейки. Например, предположим, что вы уже выбрали ячейку A1, и вы начинаете запись макроса в режиме относительной ссылки. Теперь вы выбираете ячейку A2, вводите текст Excel и нажмите клавишу Enter. Теперь, если вы запустите этот макрос, он не вернется в ячейку A2, вместо этого он будет перемещаться относительно активной ячейки. Например, если выбрана ячейка B3, она переместится на B4, запишет текст "Excel" и затем перейдет к ячейке K5.
Теперь давайте запишем макрос в режиме относительных ссылок:
Макрос в режиме относительных ссылок будет сохранен.
Теперь сделайте следующее.
Как вы заметите, макрос записал текст "Excel" не в ячейки A2. Это произошло, потому что вы записали макрос в режиме относительной ссылки. Таким образом, курсор перемещается относительно активной ячейки. Например, если вы сделаете это, когда выбрана ячейка B3, она войдет в текст Excel - ячейка B4 и в конечном итоге выберет ячейку B5.
Вот код, который записал макрорекодер:
Обратите внимание, что в коде нет ссылок на ячейки B3 или B4. Макрос использует Activecell для ссылки на текущую ячейку и смещение относительно этой ячейки.
Не обращайте внимание на часть кода Range(«A1»). Это один из тех случаев, когда макрорекодер добавляет ненужный код, который не имеет никакой цели и может быть удален. Без него код будет работать отлично.
Что нельзя сделать с помощью макрорекодера?
Макро-рекордер отлично подходит для вас в Excel и записывает ваши точные шаги, но может вам не подойти, когда вам нужно сделать что-то большее.
- Вы не можете выполнить код без выбора объекта. Например, если вы хотите, чтобы макрос перешел на следующий рабочий лист и выделил все заполненные ячейки в столбце A, не выходя из текущей рабочей таблицы, макрорекодер не сможет этого сделать. В таких случаях вам нужно вручную редактировать код.
- Вы не можете создать пользовательскую функцию с помощью макрорекордера. С помощью VBA вы можете создавать пользовательские функции, которые можно использовать на рабочем листе в качестве обычных функций.
- Вы не можете создавать циклы с помощью макрорекордера. Но можете записать одно действие, а цикл добавить вручную в редакторе кода.
- Вы не можете анализировать условия: вы можете проверить условия в коде с помощью макрорекордера. Если вы пишете код VBA вручную, вы можете использовать операторы IF Then Else для анализа условия и запуска кода, если true (или другой код, если false).
Расширение файлов Excel, которые содержат макросы
Когда вы записываете макрос или вручную записываете код VBA в Excel, вам необходимо сохранить файл с расширением файла с поддержкой макросов (.xlsm).
До Excel 2007 был достаточен один формат файла - .xls. Но с 2007 года .xlsx был представлен как стандартное расширение файла. Файлы, сохраненные как .xlsx, не могут содержать в себе макрос. Поэтому, если у вас есть файл с расширением .xlsx, и вы записываете / записываете макрос и сохраняете его, он будет предупреждать вас о сохранении его в формате с поддержкой макросов и покажет вам следующее диалоговое окно:
Если вы выберете "Нет", Excel сохранить файл в формате с поддержкой макросов. Но если вы нажмете "Да", Excel автоматически удалит весь код из вашей книги и сохранит файл как книгу в формате .xlsx. Поэтому, если в вашей книге есть макрос, вам нужно сохранить его в формате .xlsm, чтобы сохранить этот макрос.
Читайте также: