Remkomplekty.ru

IT Новости из мира ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Добавить лист в excel vba

11.5 Коллекция Sheets и объект Worksheet , их свойства и методы

Объект Excel.Worksheet, программное создание, обнаружение, удаление листа Excel средствами VBA, свойства, методы и события объекта Excel.Worksheet

В Word ниже объекта Application и Document начинались уже объекты непосредственно для работы с текстом — Selection, Range и т.п. В Excel между объектом рабочей книги и ячейками есть еще один промежуточный объект — объект Worksheet (лист). Объекты Worksheet в книге объединены в коллекцию Sheets.

Чаще всего для ввода данных в Excel (напрямую или из базы данных) нам потребуется в первую очередь определиться с листом, на который пойдет ввод данных — либо просто выбрать его, либо вначале создать, а потом выбрать.

Процесс создания выглядит очень просто:

Dim oExcel As New Excel.Application ‘Запускаем Excel

oExcel.Visible = True ‘Делаем его видимым

Dim oWbk As Excel.Workbook

Set oWbk = oExcel.Workbooks.Add () ‘Создаем новую книгу

Dim oSheet As Excel.Worksheet

Set oSheet = oWbk.Worksheets.Add() ‘ Создаем новый лист

oSheet.Name = «Новый лист» ‘Присваиваем ему имя «Новый лист»

Метод Add() для коллекции Worksheets принимает несколько необязательных параметров, главная задача которых — определить, между какими существующими листами будет вставлен новый лист. Если ничего не указывать, то новый лист будет помещен самым первым.

Часто встречается и другая задача — просто найти нужный лист среди листов книги, например, если мы открыли существующую книгу. Сделать это очень просто, поскольку коллекция Worksheets умеет работать с именами листов. Ниже приведен пример, в котором мы так же запускаем Excel и создаем новую книгу, но при этом находим лист с именем «Лист1» и переименовываем его в «Новый лист»:

Dim oExcel As New Excel.Application ‘Запускаем Excel

oExcel.Visible = True ‘Делаем его видимым

Dim oWbk As Excel.Workbook

Set oWbk = oExcel.Workbooks.Add() ‘Создаем новую книгу

Dim oSheet As Excel.Worksheet

Set oSheet = oWbk.Worksheets.Item(«Лист 1») ‘ Находим Лист1

oSheet.Name = «Новый лист» ‘Присваиваем ему имя «Новый лист»

Обратите внимание, что в английской версии Excel этот код, скорее всего, не пройдет, поскольку листы там по умолчанию называются «Sheet1», «Sheet2» и т.п. Если вы в вашем коде используете имена листов по умолчанию и при этом вашей программе придется работать на компьютерах с разноязычными версиями Excel, обязательно предусмотрите дополнительные проверки или просто используйте номера листов вместо их имен.

У коллекции Sheets, помимо привычных нам свойств и методов ( Count, Item, Add(), Delete()) и свойств и методов, которые удобнее применять для объекта Worksheet ( Visible(), Copy(), Move(), PrintOut(), PrintPreview(), Select()) — поскольку все равно указывать конкретный лист — есть и один специфический метод FillAcrossSheets() — скопировать объект диапазона Range (варианты: полностью, только содержимое, только оформление) во все листы данной книги.

У объекта Worksheet — множество важных свойств и методов:

  • Cells — одно из наиболее часто используемых свойств объекта Worksheet. Работает точно так же, как и рассмотренное выше одноименное свойство объекта Application — за исключением того, что вам больше не нужно ограничиваться только активным листом. Аналогично работают свойства Columns и Rows.
  • EnableCalculation — возможность отключить автоматический пересчет значений ячеек в книге.
  • EnableSelection — возможность запретить выделять на листе: все, ничего не запрещать, или разрешить выделять только незаблокированные ячейки.
  • Next — возможность получить ссылку на следующий лист в книге. Previous — то же самое для предыдущего листа.
  • PageSetup — как и в Word, возможность получить объект PageSetup, при помощи которого можно настроить те же параметры, что и через меню Файл ->Параметры страницы.
  • свойство Protection позволяет получить объект Protection, при помощи которого можно запретить пользователю вносить изменения в лист Excel. Настройке параметров защиты также служат и другие свойства, названия которых начинаются на Protection.
  • QueryTables — исключительно важное свойство. Оно возвращает коллекцию QueryTables — набор объектов QueryTable, которые, в свою очередь, представляют данные, полученные из внешних источников (как правило, из баз данных).
  • Range — самое важное свойство объекта Worksheet. Возвращает объект Range (диапазон ячеек), который в объектной модели Excel занимает примерно такое же место, что и одноименный объект в объектной модели Word. Этот объект будет рассматриваться ниже.
  • Type — возможность определить тип данного листа. Обычно используются два типа: xlWorksheet (обычный лист) и xlChart (диаграмма).
  • UsedRange — возвращает объект Range, представляющий собой прямоугольную область, включающую все непустые ячейки. Удобно для целей копирования или форматирования.
  • Visible — возможность спрятать лист с глаз пользователя (например, если он используется для служебных целей).

Некоторые важные методы объекта Worksheet:

  • методы Activate() , Calculate(), Copy(), Paste(), Delete(), Move(), Evaluate(), Select(), SaveAs(), PrintOut(), PrintPreview(), Protect(), Unprotect()нам уже знакомы . Отличие заключается только в том, что теперь эти методы могут применяться для выбранного вами листа.
  • метод PivotTables() возвращает коллекцию очень интересных объектов PivotTable (сводная таблица), которые будут рассматриваться ниже;
  • метод Scenarios() возвращает коллекцию Scenarios, состоящую из объектов Scenario (сценарии). Сценарии — это именованные наборы вводных данных, которые можно использовать для проверки различных сценариев (разные суммы продаж, уровни налогов, расходов и т.п.)
  • SetBackgroundPicture() — возможность назначить листу фоновое изображение (естественно, желательно, чтобы оно было полупрозрачное — «водяной знак», иначе на его фоне будет трудно читать текст в ячейках).
  • ShowAllData() — показать все скрытые и отфильтрованные данные на листе.

Самое важное событие объекта Worksheet — это, конечно, Change. Существует множество практических задач, когда изменение пользователем значения ячейки должно приводить к изменению значения в ячейке другого листа/рабочей книги Excel или даже в базе данных. Другая ситуация, в которой используется это события — сложная проверка вводимого пользователем значения (например, опять-таки через обращение к базе данных). Эта событийная процедура работает со специальным параметром Target — то есть объектом Range, представляющим изменившуюся ячейку. При помощи свойств и методов объекта Range вы можете получить информацию об изменившемся значении, столбце и строке, в котором произошло изменение и т.п.

Читать еще:  Vba excel код символа

У объекта Worksheet есть еще два очень удобных события (их сильно не хватает объекту Document в Word). Это — события BeforeRightClick() и BeforeDoubleClick(). Как понятно из названия, первое событие позволяет перехватывать щелчок правой кнопкой мыши по любому месту в листе, а второе — двойной щелчок мышью. При помощи этих событий вы можете назначить свою реакцию (открытие контекстных меню, выдачу предупреждающих сообщений, переход в другой режим работы и т.п.) на действия пользователя.

Создание и именование рабочего листа в Excel VBA

У меня есть очень простой код, который добавляет новый лист после текущих листов в документ Excel, а затем меняет его имя на имя, введенное в текстовое поле в форме пользователя. Хорошо работает с новой книгой, однако в книге, содержащей несколько существующих листов, он создает новый лист, но не переименовывает его.

Это происходит только при первом запуске этого кода, в следующий раз он будет работать нормально. Еще более странным является то, что если вы откроете редактор VBA, чтобы попытаться отладить его, он также будет работать нормально. Это, очевидно, делает поиск ошибки довольно трудно.

Код, который я использую, находится здесь:

Все очень просто. Мне интересно, если эта проблема заключается в том, что он пытается переименовать лист, прежде чем он будет правильно создан? Есть ли лучший способ написать этот код?

Update: я начал отлаживать это с помощью msgboxes, так как открытие отладчика останавливает проблему, и кажется, что он просто перестает обрабатывать код на полпути:

3 Ответа

Вам не нужно знать, где он находится или как его зовут, вы просто называете его WS.
Если вы все еще хотите сделать это «old fashioned» способом, попробуйте это:

Вы используете обработчик ошибок? Если вы игнорируете ошибки и пытаетесь назвать лист так же, как существующий лист или имя с недопустимыми символами, это может быть просто пропуск этой строки. Смотрите функцию CleanSheetName здесь

для списка недопустимых символов,которые вы можете проверить.

Обновление

Другие вещи, чтобы попробовать: полностью квалифицированные ссылки, бросание в Doevents, очистка кода. Этот код квалифицирует вашу ссылку на листы до ThisWorkbook (вы можете изменить ее на ActiveWorkbook, если это подходит). Он также добавляет тысячу DoEvents (глупый перебор, но если что — то займет некоторое время, чтобы сделать это, это позволит ему-Вам может понадобиться только один DoEvents, если это действительно что-то исправит).

Наконец, всякий раз, когда у меня возникает тупая проблема VBA, которая просто не имеет смысла, я использую CodeCleaner Роба Бови. Это надстройка, которая экспортирует все ваши модули в текстовые файлы, а затем повторно импортирует их. Вы можете сделать это и вручную. Этот процесс очищает любой поврежденный p-код, который висит вокруг.

Вы фиксируете ячейку перед нажатием кнопки (нажатие Enter)? Содержимое ячейки должно быть сохранено до того, как ее можно будет использовать для присвоения имени листу.

Лучший способ сделать это-открыть диалоговое окно и получить имя, которое вы хотите использовать.

Похожие вопросы:

Я пытаюсь использовать событие изменения рабочего листа в Excel VBA, но оно, похоже, не работает. Из того, что я понял, достаточно просто определить функцию обработки Worksheet_Change, как я это.

Я пытаюсь автоформировать, как отображается нижняя часть страницы Excel, чтобы автоматически просмотреть все имя листа. По умолчанию половина имени листа обрезается, так как горизонтальная полоса.

У меня есть набор пользовательских функций vba, которые находятся в модуле excel, а затем вызываются из электронной таблицы excel. на данный момент все работает нормально. Меня попросили.

У меня есть рабочий лист с несколькими событиями изменения рабочего листа. К сожалению, я получаю некоторые ошибки памяти из-за них. Мне было интересно, может ли кто-нибудь посмотреть на этот.

Я пытаюсь улучшить производительность проекта C# Winform. Ранее создание файла Excel выполнялось в течение 100000 МС (с использованием Office.Interop.Excel). Используя ClosedXml это около 5000 мс.

Встроенные функции Excel имеют стиль UPPERCASE символов. Я немного OCD и как мои пользовательские функции рабочего листа, чтобы быть UPPERCASE тоже, как и встроенные функции Excel. Однако Excel 2013.

В Microsoft Excel я написал несколько кодов для события изменения рабочего листа. Теперь я хочу создать sub и поместить его в файл personal.xlsb , чтобы всякий раз, когда пользователь запускает его.

Эй, ребята, у меня есть большой объем данных в файле excel прежде чем я начну использовать данные, мне нужно обрезать их все один раз но когда я использую vba для обрезки всего рабочего листа, это.

Обычно функция оценки Excel VBA является удобным способом получить результат формулы рабочего листа в VBA без необходимости помещать формулу в ячейку рабочего листа. Тем не менее, я нахожу, что если.

Макрос на VBA Excel – Формируем документы по шаблону

Очень часто бывает такое, что нужно сформировать документы по определенному шаблону, на основе каких-то данных, например, по каждому сотруднику или по каждому лицевому счету. И делать это вручную бывает достаточно долго, когда этих самых сотрудников или лицевых счетов много, поэтому сегодня мы рассмотрим примеры реализации таких задач в Excel с помощью макроса написанного на VBA Excel.

Немного поясню задачу, допустим, нам необходимо сформировать какие-то специфические документы по шаблону массово, т.е. в итоге их получится очень много, как я уже сказал выше, например, по каждому сотруднику. И это нужно сделать непосредственно в Excel, если было бы можно это сделать в Word, то мы бы это сделали через «Слияние», но нам нужно именно в Excel, поэтому для этой задачи мы будем писать макрос.

Читать еще:  Vba excel работа с файлами

Мы с Вами уже выгружали данные по шаблону через клиент Access из базы MSSql 2008 в Word и Excel вот в этой статье — Выгрузка данных из Access в шаблон Word и Excel. Но сейчас допустим, у нас данные располагаются в базе, в клиенте которой нельзя или слишком трудоемко реализовать такую задачу, поэтому мы просто выгрузим необходимые данные в Excel и на основе таких данных по шаблону сформируем наши документы.

В нашем примере мы, конечно, будем использовать простой шаблон, только для того чтобы это было просто наглядно и понятно (только в качестве примера), у Вас в свою очередь шаблон будет, как мне кажется намного сложней.

Напомню, что на данном сайте тема VBA Excel уже затрагивалась, например, в материале – Запрет доступа к листу Excel с помощью пароля

И так приступим!

Пишем макрос на VBA Excel по формированию документов

Реализовывать нашу задачу будем на примере «Электронной карточке сотрудника» (я это просто придумал:), хотя может такие и на самом деле есть), т.е. документ в котором хранится личные данные сотрудника вашего предприятия, в определенном виде, именно в Excel.

Примечание! Программировать будем в Excel 2010.

И для начала приведем исходные данные, т.е. сами данные и шаблон

Данные.

Лист, на котором расположены эти данные так и назовем «Данные»

Шаблон.

Лист, на котором расположен шаблон, тоже так и назовем «Шаблон»

Далее, нам необходимо присвоить имена полей для вставки, так более удобней к ним обращаться чем, например, по номеру ячейки.

Это делается очень просто, выделяете необходимую ячейку или диапазон, и жмете правой кнопкой мыши и выбираете «Присвоить имя», пишите имя ячейки и жмете «ОК»

Свои поля я назвал следующим образом:

  • ФИО – fio;
  • № — number;
  • Должность – dolgn;
  • Адрес проживания – addres;
  • Тел. № сотрудника – phone;
  • Комментарий – comment.

Код макроса на VBA Excel

Для того чтобы написать код макроса, открывайте на ленте вкладку «Разработчик», далее макросы.

Примечание! По умолчанию данной вкладке в Excel 2010 может и не быть, чтобы ее отобразить нажмите правой кнопкой по ленте пункт меню «Настройка ленты»

затем, в правой области поставьте галочку напротив пункта «Разработчик»

После вкладка разработчик станет отображаться на ленте.

Далее, когда Вы откроете вкладку разработчик и нажмете кнопку «Макросы» у Вас отобразится окно создания макроса, Вы пишите название макросы и жмете «создать».

После у Вас откроется окно редактора кода, где собственно мы и будем писать свой код VBA. Ниже представлен код, я его как обычно подробно прокомментировал:

Теперь осталось выполнить этот макрос, для этого откройте вкладку разработчик->макросы->выполнить наш макрос:

и после выполнения у Вас в той же папке появится вот такие файлы

Вот с таким содержимым:

Для удобства можете на листе с данными создать кнопку и задать ей событие выполнить наш только что созданный макрос, и после чего простым нажатием выполнять этот макрос. Вот и все! Удачи!

Добавить лист в excel vba

Поговорим про то, как в VBA обращаться к листам книги Excel.

Вариант 1

У глобального объекта Excel Application есть 2 коллекции листов: Sheets и Worksheets . Отличаются они тем, что вторая коллекция не включает в себя специальные листы макросов, которые поддерживала Excel 4.0 (на минуточку — 1992 год) во времена, когда VBA ещё не было в природе (появился в Excel 5.0). Тяжело найти эстетов, которые всё ещё пользуются такими листами с макросами, поэтому можно считать, что эти коллекции идентичны. Лично я использую коллекцию Sheets просто потому, что 5 букв лучше, чем 9.

Sheets(1).Cells(1,1)

Sheets(1) — это не первый лист, созданный в книге, а лист, который на панели ярлыков листов занимает КРАЙНЕЕ ЛЕВОЕ положение. То есть Sheets(2) будет указывать на второй слева лист на панели ярлыков. Если вам нужен конкретный лист, то ТАК ссылаться на него, как вы понимаете, плохая идея, ибо пользователь, изменив порядок следования листов, нарушит работоспособность вашей программы.

Во всех примерах я буду обращаться к ячейки A1, так как речь не о свойствах объекта Worksheet , а о том, какие есть способы получить синтаксическую конструкцию, указывающую на нужный нам лист.

Вариант 2

Sheets(«First»).Cells(1,1)

Пожалуй, такое обращение к листу наиболее популярно в народе. В скобках и кавычках мы видим пользовательское имя листа. Плохо только то, что имя листа, что называется «hard coded» (жёстко запрограммировано), что является моветоном в программировании. Ибо, если у вас в модуле конструкция Sheets(«First») встречается 100 раз, то при смене имени листа придётся делать поиск с заменой 100 констант в коде. Поэтому обычно делают иначе.

Вариант 3

Гораздо разумней добавить ещё один уровень абстракции и объявить константу, ссылающуюся на имя листа.

Public Const wsFirst = «First»

Sheets(wsFirst).Cells(1,1)

В случае смены имени вам придётся исправить ТОЛЬКО эту константу в начале модуля. Однако, если программа написана и сдана пользователю, а тот в один прекрасный момент берёт и переименовывает лист, то наш код тут же перестаёт работать, что, согласитесь, не слишком хорошо.

Вариант 4

У листа есть свойство Name , которое мы видим на ярлыке листа и которое можем менять (если не включена защита структуры книги, но сейчас не об этом), но ещё есть свойство CodeName , которое присваивается листу в момент создания, и которое пользователь сменить не может. В русской редакции офиса эти CodeName -ы формируются по принципу: Лист1 , Лист2 , Лист3 и т.д. В англоязычной версии это: Sheet1 , Sheet2 , Sheet3 , etc.

Читать еще:  Не работает вставка в excel

Если вы удалите лист, то его CodeName повторно не будет использовано. Так вот — VBA разпознаёт имена совпадающие с CodeName листа, как объекты типа Worksheet .

Лист1.Cells(1,1) или [Лист1].Cells(1,1)

Это отличная защита от того, что пользователь перенесёт или переименует лист. Но подобные имена ни о чём не говорят нам в процессе программирования в плане того, что располагается на листе, поэтому вы будете частенько ошибаться, путая листы. А те, кто будут потом разбираться в вашем коде, тоже спасибо вам не скажут. Поэтому я предлагаю следующую модификацию этого способа:

Вариант 5

Public wsData As Worksheet

Set wsData = Лист1

wsData.Cells(1,1)

Строку 1 размещаем в объявлениях модуля. Строку 2 размещаем в обработчике события Workbook_Open . И после этого в любом месте книги мы можем использовать объявленные переменные ( wsData , строка 3). На мой взгляд, это самый удобный и продвинутый вариант из рассмотренных.

Вариант 6

Не забываем про свойство ActiveSheet объекта Application , указывающее на активный лист книги. Если вы работаете с текущим листом, то использовать его, я считаю, оптимально.

ActiveSheet.Cells(1,1)

Вариант 7

Ну и последнее, если вам передан в подпрограмму объект типа Range, то не забываем, что ссылку на лист возвращает его свойство Worksheet .

Это всё, что я имел сказать по поводу ссылок на листы в Excel VBA 🙂

Как вставить в книгу Excel новые листы? Как сделать копии листа в Excel? Как добавить новые листы с заданными именами?

В этой заметке рассмотрим на примере Excel 2007 рассмотрим добавление новых листов в рабочую книгу, копирование существующего листа, а также создание листов с заданными именами .

Как вставить новый чистый лист в книгу Excel?

Для вставки нового листа достаточно выбрать пункт меню «Вставить. «, либо нажать кнопку, следующую сразу за последним листом рабочей книги. Еще одним способом добавления нового листа в рабочую книгу является нажатие сочетания клавиш Shift+F11 на клавиатуре. Можно также настроить создание книг с произвольным количеством листов, если Вас не устраивают стандартные три листа. Сделать это можно следующим образом, нажать кнопку Office, затем в меню выбрать «Параметры Excel» и в разделе «Основные» выбрать группу «При создании книг». В графе «Число листов» задайте нужное значение.

Как скопировать лист Excel или создать его точную копию?

Для того чтобы сделать копию листа в Excel 2007, необходимо выбрать лист, который Вы хотите скопировать и кликнуть на нем правой кнопкой мыши, выбрать пункт «Переместить/скопировать. «.

Далее в появившемся окне ставим галку в поле «создать копию» и при необходимости выбираем строку «(переместить в конец)», при этом созданная копия выбранного листа станет последним листом рабочей книги.

Как изменить имя или переименовать лист рабочей книги Excel?

Для того чтобы переименовать лист, необходимо кликнуть правой кнопкой мыши на ярлычке нужного листа и выбрать из контекстного меню пункт «Переименовать», кроме того, можно просто дважды кликнуть левой кнопкой мыши на нужном ярлычке листа и его имя станет доступным для изменения (кроме тех случаев, когда установлена защита структуры и окон).

Описанные выше действия относятся к стандартным средствам Excel и их вполне достаточно при работе с книгами, имеющими небольшое количество листов. Для массового добавления рабочих листов, вставки большого количества копий, а также для создания листов с заданными именами целесообразно использовать средства VBA.

Как многократно вставить пустые листы в книгу?

Как создать множество копий листа? Как добавить в книгу листы с именами из ячеек

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

Для того, чтобы воспользоваться этим средством, необходимо установить надстройку на свой компьютер, вызвать диалоговое окно надстройки и задать в нём исходные данные для работы программы.

Можно выбрать одну из доступных опций:

1. Вставить новые листы в заданном количестве

Пользователем задается требуемое количество листов, выбирается опция вставки листов и запускается программа, результатом работы которой является рабочая книга с заданным количеством пустых листов.

2. Скопировать активный лист заданное число раз

Выбирается копируемый лист, по аналогии с предыдущим пунктом задается желаемое число копий, в результате действия программы в книге появляется заданное число копий активного листа.

3. Присвоить новым листам именами из ячеек

Вместо того чтобы создавать новые листы со стандартными именами «Лист1», «Лист2», «Лист3» и так далее, а затем переименовывать их, тратя на эту операцию много времени, можно предварительно сформировать список имен в ячейках листа рабочей книги, а затем в диалоговом окне надстройки выделить диапазон с нужными именами и запустить программу. Перед созданием листов с именами из значений ячеек производится проверка этих значений на соответствие требованиям к именам листов, которые:

а) не должны превышать 31 знака;

б) не должны содержать знаков: : / ? * [ или ];

в) не должны оставаться пустыми.

После прохождения проверки добавляемым листам присваиваются имена в соответствии со значениями ячеек выделенного диапазона.

Создание квитанций в Excel

На основе представленной выше надстройки можно, например, автоматизировать процесс формирования квитанций либо любых других бланков документов, имеющих одинаковый вид, в формате Excel. Для этого создается лист с формой квитанции и с помощью макроса тиражируется в таком количестве, в каком это необходимо.

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector
×
×