Remkomplekty.ru

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

Vba excel добавить лист с именем

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 вы можете получить информацию об изменившемся значении, столбце и строке, в котором произошло изменение и т.п.

Читать еще:  Создание olap куба в excel

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

Конференция VBStreets

Весь вкус программирования!

  • Список форумовСмежные технологииVBA
  • Изменить размер шрифта
  • FAQ
  • Вход

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

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

Аэроплан » 23.10.2003 (Чт) 13:59

Макрос создает новый лист.
Неоднократно все время пока файл открыт.
Имена создаваемых файлов меняются Лист1, Лист2.
Как узнать его имя?

.
Я знаю только точное его положение. Он всегда третий слева.

Tony » 23.10.2003 (Чт) 14:29

Но ведь это совершенно не по взрослому 😉

Аэроплан » 23.10.2003 (Чт) 14:51

А как же тогда пользоваться
Код: Выделить всё Workbook_NewSheet(ByVal Sh As Object)
Ведь это, насколько я понимаю, как раз и есть инструмент для выхватывания «листовых новообразований» в файле.
Я вот только не знаю, как изменить имя листа.

Код типа Sh.Name = «имя_листа» не работает и вообще при компиляции он говорит, что Sh.Name содержит в себе имя ВСЕГО документа, т.е. Имя_файла.xls.

Вот такая петрушка.

Tony » 23.10.2003 (Чт) 14:55

Аэроплан » 23.10.2003 (Чт) 15:54

Да нет же. Не имеет значения, каким образом создается ЛИСТ.
Просто эта процедура как-то реагирует и что-то возвращает при создании нового листа.. Т.е. она точно реагирует и отрабатывается сразу при появлении нового листа. Однако она не возвращает ИМЯ нового созданного листа. Точнее я незнаю как его оттуда выдрать. Вот.
Код: Выделить всё Sub Workbook_NewSheet(ByVal Sh As Object)

‘ тут чтото я незнаю’

Sh.Name = Date
Sheets(newSheetName).Select
Sheets(newSheetName).Move After:=Sheets(3)
Sheets(«Хронология»).Select
Range(«A1:K3»).Select
Selection.Copy

Sheets(newSheetName).Select
ActiveSheet.Paste
Sheets(«Хронология»).Application.CutCopyMode = False

End Sub

Так примерно я наваял. А вот Sh.Name не работает.
Точнее работает, но не так. При компиляции выделенный Name возвращает имя ВСЕГО файла Имя_файла.xls и на этом вылетает. в ошибку.

RayShade » 23.10.2003 (Чт) 16:07

Что то ты мудришь по ходу.

У меня все работает на ура. И с выводом имени листа и с его изменением. Версия офиса какая? Сервиспаки?

Vitaly1 » 23.10.2003 (Чт) 16:41

Если знаешь расположение то так, для активной книги:

Извиняюсь.

Аэроплан » 23.10.2003 (Чт) 17:11

Я извиняюсь. Действительно перемудрил. Код этот работает.
блин, я его собрал, и стал жаловаться на судьбу, а проверить еще не успел.. Проблема в другом.
Совсем в другом.

Если в один день создается несколько листов, то необходимо из
Код: Выделить всё newSheetName = Date

сделать Код: Выделить всё newSheetName = Date + Time

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

Огромное спасибо за помощь и внимание.

Буду теперь из времени выдергивать двоеточия и менять их на «_».
Снова в поиск.

RayShade » 23.10.2003 (Чт) 17:17

Советую воспользоваться таким водом:

Код: Выделить всё newSheetName = replace(cstr(date+time),»:»,»_». vbtextcompare)

Vitaly1 » 23.10.2003 (Чт) 17:29

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

S = »20:10:3»
s1 =»»
for i=1 to len(s)
if mid(s,i,1) <>«:» then
s1=s1+mid(s,i,1)
else
s1=s1+» »
end if
next i

в s1 символ : замениться на пробел

RayShade » 23.10.2003 (Чт) 18:07

. без комментариев

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

Код: Выделить всё s = »20:10:3»
for i=1 to len(s)
if mid(s,i,1) = «:» then mid(s,i,1)=» «
next i

Vba excel добавить лист с именем

Профиль
Группа: Участник
Сообщений: 2
Регистрация: 20.10.2004

Репутация: нет
Всего: нет

Хоца сделать динамически формируемые отчет, то есть создавать лист на него копировать данные по выборке из этой же книги.

проблема вот в чем

Отчетов несколько, форматирование в каждом отчете разное.
А формироваться должны на одном листе.
Посему пытался сделать

Форматирование листа сохранялось, такое как

потом попробовал удалять лист и создавать новый

Получается, но лист добавляется не там где надо.
Можно ли поменять его положение в книге?
а) при создании
б) после создания

Советчик

Профиль
Группа: Модератор
Сообщений: 20437
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 32
Всего: 449

a) да. У Sheets.Add есть некие параметры
б) да. У Sheets есть некие методы

RTFM одним словом.

О(б)суждение моих действий — в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция — Администрация форума.

Опытный

Профиль
Группа: Участник
Сообщений: 377
Регистрация: 22.12.2003

Репутация: нет
Всего: 1

Профиль
Группа: Участник
Сообщений: 2
Регистрация: 20.10.2004

Репутация: нет
Всего: нет

1. Публиковать ссылки на вскрытые компоненты

Читать еще:  Как копировать таблицы в excel

2. Обсуждать взлом компонентов и делиться вскрытыми компонентами

  • Литературу по VB обсуждаем здесь
  • Действия модераторов можно обсудить здесь
  • С просьбами о написании курсовой, реферата и т.п. обращаться сюда
  • Вопросы по реализации алгоритмов рассматриваются здесь
  • Используйте теги [code=vb][/code] для подсветки кода. Используйтe чекбокс «транслит» (возле кнопок кодов) если у Вас нет русских шрифтов.
  • FAQ раздела лежит здесь!

Если Вам понравилась атмосфера форума, заходите к нам чаще! С уважением, Akina.

[ Время генерации скрипта: 0.1330 ] [ Использовано запросов: 21 ] [ GZIP включён ]

VBA — Add Sheets based on a value and name accodingly

I am having trouble with my code. I need to create new sheets and name them according to the name of a machine. Which is the range «B» & (12 * x — 5) . Yes the range changes based on x which in this example is equal to 1 to 33, and is within the proper For statement. However my code is creating a large number of sheets and not just 11, in this example. Sheets(«Tool Setup»).Range(«C18») = 11 in this example. Also, my intention is to name these sheets according to the value in Range «B» & (12 * x — 5) .

If someone can help me figure out how to create a code that will satisfy this problem, it would be much appreciated. I have a hunch it has to do with the variable Tabs .

Thank you in advance.

Создан 21 авг. 17 2017-08-21 16:57:33 A Cohen

you do not define x, I believe you mean to reference ‘Tabs’ – sourceCode 21 авг. 17 2017-08-21 17:03:07

you also do not give the newly added sheet a name, unless your intention was to give them default names ‘Sheet1’, ‘Sheet2’, etc – sourceCode 21 авг. 17 2017-08-21 17:08:31

‘Dim Sheet as Integer’ is a terribly misleading name to give to a variable that doesn’t contain a ‘Worksheet’ object reference. ‘sheetCount’ perhaps? What’s in »Tool Setup’!C18′? FWIW whatever is in there would probably be better off as a named range, e.g. ‘Sheet = ThisWorkbook.Names(«SomeConfigurationValue»).RefersToRange.Value’ – Mat’ s Mug 21 авг. 17 2017-08-21 17:35:26

‘Tabs’ is never assigned to (at least in the code you posted), so its value will always be ‘0’. What’s that hunch about? – Mat’ s Mug 21 авг. 17 2017-08-21 17:36:18

I don’t think your question can be answered as-is. Please [edit] to clarify. See [mcve] if you’re not sure how. – Mat’ s Mug 21 авг. 17 2017-08-21 17:37:12

I believe I edited it the best that I could for better help. I am sorry if I am unclear, I am quite new at VBA – A Cohen 21 авг. 17 2017-08-21 18:53:48

1 ответ

Based on what you’ve provided, I’ve come up with the code below which names 11 sheets according to a formula similar to yours. To demonstrate that it works, I placed consecutive numbers in the first 127 rows of Column B of the active sheet. Then, the code calculates which row to grab using the formula Range(«B» & (12 * i — 5) That formula gives the following sequence: 7 19 31 43 55 67 79 91 103 115 127, and so 11 sheets with those numbers are made.

If this is not what you intended, perhaps you can use this working code as a place from which to start. Or, better yet, provide more definition for your question.

Создан 21 авг. 17 2017-08-21 18:56:31 Tony M

Please be sure to properly indent your code when posting :). – Brandon Barney 21 авг. 17 2017-08-21 19:09:15

It’s good you are going through the code line by line. Also check the values of key variables by hovering your mouse over them, or if that doesn’t work, adding lines of code such as «s=num», or «s=sh.name», etc. (where s is declared as «Dim s as String»). If it is skipping over the «For» statement then «num» must be 1 or less. If it won’t name the sheet then first define another variable «Dim shName as String» and then replace the code naming the sheet with these lines «shName=sh.Range(«B» & (12 * i — 5))» followed by «Sheets(Sheets.Count).Name=shName» & check the value of shName – Tony M 21 авг. 17 2017-08-21 19:45:08

@Tony I attempted it and since ‘num’ will change with other data. I attempted to set sh = Sheets(«Tool Setup») and ‘num = sh.Range(«C18»).Value’. Also since I’ve previously used ‘i’ as a variable I changed it to ‘j’. However, It won’t name the sheets. It skips over that line of code. I also switched num line with Set – A Cohen 21 авг. 17 2017-08-21 19:45:44

@ACohen If it is skipping over it then the issue is likely that ‘i > num’ and if you mean that you replaced ‘num’ with ‘Set’ then you need to change ‘Set’ to something else (since ‘Set’ is an assignment operator). – Brandon Barney 21 авг. 17 2017-08-21 19:47:22

@TonyM So now it creates 11 slides, and gives me the names of them, but only four of them and places a buffer slide between them. ‘»CorrectName1″‘ — ‘Sheet2’ — ‘Sheet3’ — ‘»CorrectName2″‘ – A Cohen 21 авг. 17 2017-08-21 20:04:20

@ACohen It’s possible some of the names you are using contain illegal characters. To find out what the problem is, go through line-by-line while continually checking the values of variables just before they are used. I tried to describe how to do that in my previous comment so that you can learn how to solve problems like this. If you can learn how to do that, you’ll find VBA very powerful. When addressing questions on this site it’s best to eventually consider the problem solved since otherwise the problem continually changes, losing the original focus. Hope you feel you’ve been helped – Tony M 21 авг. 17 2017-08-21 20:21:01

Читать еще:  Количество строк в excel 2003

@TonyM I do appreciate the help and the assistance to teaching me. However, they are not illegal characters, and the names are going down the line of names correctly, It is just adding the names after two newly created sheets rather than 1 after the other. Thank you nonetheless. – A Cohen 21 авг. 17 2017-08-21 20:28:05

When strange things like this happen I try to go back to a point where the code was working as expected. For example, in my original code, there was no skipping of sheets. Then gradually introduce lines of code so that you can pinpoint where the unexpected behavior begins. If, for some reason a sheet doesn’t get named when you think it should, try naming the sheet with that text manually. – Tony M 21 авг. 17 2017-08-21 20:32:22

Как вставить в книгу 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