Remkomplekty.ru

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

Создать новую книгу excel vba

VBA в Excel Объект Excel.Workbook и программная работа с книгами Excel из VBA

10.4 Коллекция Workbooks и объект Workbook, их свойства и методы

Объект Excel.Workbook, программная работа с книгами Excel из VBA, создание и открытие книг Excel

Следующий по иерархии после Application объект в объектной модели Excel — это объект Workbook, который представляет книгу Excel. Можно сказать, что объект Workbook занимает в Excel примерно то же место, что и объект Document в Word — он нужен для получения ссылки на нужную нам книгу в наборе открытых книг Excel, а также для настройки общих свойств и выполнения общих действий со всеми листами книги. Получить этот объект можно очень просто:

  • первый способ — воспользоваться коллекцией Workbooks, которая доступна через свойство Workbooks объекта Application. Впрочем, применять это свойство совершенно не обязательно — коллекция Workbooks в Excel и так постоянно доступна. Найти нужную книгу в этой коллекции можно по ее имени или номеру в коллекции:
  • второй способ — использовать свойство Application.ActiveWorkbook. При помощи этого свойства мы обращаемся к активной в настоящей момент книге:
  • третий способ — использовать свойство Application.ThisWorkbook. При этом мы обращаемся к той книге, которой принадлежит данный программный модуль:

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

Dim oWbk As Workbook

Set oWbk = Workbooks.Add()

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

Открытие существующей книги выглядит так:

Dim oWbk As Workbook

Set oWbk = WorkBooks.Open(«C:mybook1.xls»)

Помимо стандартных, в коллекции Workbooks предусмотрено также три специальных метода:

  • OpenDatabase() — открыть базу данных, выполнить к ней запрос (или открыть таблицу/представление напрямую), а результаты запроса поместить как импортированные внешние данные в новую автоматически созданную рабочую книгу Excel;
  • OpenText() — почти то же самое, но в качестве источника здесь выступает текстовый файл. Дополнительные параметры позволяют определять его формат.
  • OpenXML() — в качестве источника данных будет выступать файл в формате XML.

Как и метод InsertDatabase() в Word, эти методы следует использовать только в самых простых случаях. Рекомендуется по возможности использовать более мощные и стандартные средства объектной модели ADO.

Теперь о самых важных свойствах объекта Workbook — самой рабочей книги:

  • Name, CodeName, FullName — разные имена этой книги. Самое простое имя — Name, это имя совпадает с именем файла книги. FullName — это имя файла книги вместе с полным путем к нему в операционной системе. CodeName — как эта книга будет называться в коде. CodeName можно посмотреть в окне Project Explorer или, если открыть свойства книги в окне Properties, кодовое имя книги будет представлено в строке (Name). Все три свойства доступны только для чтения, менять их можно другими способами (например, сохраняя файл под другим именем или прямо в окне Properties).

Определенное отношение к именам имеет также свойство Path (путь к файлу книги) .

  • Charts, Sheets, ActiveChart, ActiveSheet, CustomViews, BuiltinDocumentProperties и CustomDocumentProperties, Windows, WebOptions возвращают одноименные коллекции соответствующих объектов. Некоторые из этих объектов будут рассматриваться ниже.
  • ConflictResolution — как будут разрешаться конфликты изменения данных, если книга открыта несколькими пользователями сразу (shared workbook). Есть возможность сделать так, чтобы локальный пользователь автоматически выигрывал, автоматически проигрывал или возникало диалоговое окно с возможностью разобраться в конфликте вручную. Существует большое количество свойств, которые позволяют настроить параметры совместной работы с книгой, но по причине того, что такая работа не рекомендуется (данные для совместного доступа необходимо переносить в базу данных), рассматриваться они здесь не будут, за исключением:
    • запрещать/разрешать общий доступ к рабочей книге можно при помощи методов SaveAs() или ExclusiveAccess();
    • по умолчанию возможность совместного редактирования для книги отключена (проверить можно при помощи свойства MultiUserEditing);
    • получить список всех пользователей (а также когда они открыли файл и в каком режиме) можно при помощи свойства UserStatus.
  • FileFormat — формат книги (доступен напрямую только для чтения, можно изменять при сохранении). Форматов очень много: множество версий Excel, DBF, Lotus 1-2-3, форматы TXT, CSV, XML — всего несколько десятков.
  • свойство Names возвращает коллекцию всех именованных диапазонов в данной рабочей книге. Получить информацию о всех именованных диапазонах в данной книге можно, например, так:

For Each Item In ThisWorkbook.Names

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

Методов у объекта Workbook также очень много, однако значения самых употребимых — Activate(), Close(), Save(), SaveAs(), PrintOut(), Protect() и Unprotect() очевидны и действуют аналогично одноименным методам объекта Document в Word.

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

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

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

Создание книги с заданным именем

Создание книги с заданным именем

Влад » 16.04.2004 (Пт) 9:52

Добрый день всем!
Подскажите, как создать книгу с заданным именем.
Более конкретно — формируются отчеты за выбранные дни как отдельные книги, которые должны иметь разные имена (в которые включена дата). Какие из них сохранять как файлы, выбирает пользователь (такое требование), то есть Workbooks.open использовать не получается, так как создаются файлы на диске.
По Workbooks.Add (1) создаются книги с именем «Книга1″,»Книга2».
При обходном варианте — временное создание файла-шаблона с произвольным именем и потом удалением этого шаблона

fname = «yyyy-mm-dd.xls»
Open fname For Random Access Read Write As #1
Close #1
Workbooks.Add fname
Kill fname

книга открывается с требуемым именем, но почему-то только для чтения (свойство .ReadOnly=True), а это свойство изменить нельзя.
При использовании как шаблона реальных файлов Excel — картина аналогичная, доступ к вновь открытой книге только по чтению.

Заранее благодарен за помощь.

GSerg » 16.04.2004 (Пт) 10:03

Влад » 16.04.2004 (Пт) 10:43

GSerg » 16.04.2004 (Пт) 10:48

Влад » 16.04.2004 (Пт) 15:29

Sorry, оказалось, что это не все.
При попытке сохранения книги с установленным по
Workbooks.Add(1).Windows(1).Caption = «yyyy-mm-dd.xls»
оказывается, что она имеет имя «ЛистNN»
WorkBooks(i).Name=»ЛистNN»
WorkBooks(i).FullName=»ЛистNN»
а эти свойства Read Only и не изменяются.
Что можно сделать здесь?

Grerg-у. Насчет сигнатуры получилось, конечно, еще то.
Что поделать, эклер проклятый замучил, как у той бабки, которая пыталась купить два склероза.

GSerg » 17.04.2004 (Сб) 6:45

Влад » 19.04.2004 (Пн) 13:32

GSerg » 20.04.2004 (Вт) 9:32

Влад » 21.04.2004 (Ср) 7:58

Re: Создание книги с заданным именем

уырекун » 04.05.2015 (Пн) 11:47

Re: Создание книги с заданным именем

pronto » 04.05.2015 (Пн) 16:41

Re: Создание книги с заданным именем

уырекун » 04.05.2015 (Пн) 17:29

Ваш код вызывает диалог, а можно ли программно задать это «имя файла», поясню, я программно создаю новую книгу, пользователь жмет в ней сохранить и уже в этом диалоге вместо «книга1» должно быть «Нужное_имя_при_сохранении»

Re: Создание книги с заданным именем

alibek » 04.05.2015 (Пн) 17:54

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

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

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

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

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

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

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

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

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

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

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

Данные.

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

Шаблон.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Управление рабочими книгами и листами в VBA

Настоящая заметка продолжает знакомство с VBA, в ней приводятся примеры управления рабочими книгами и листами Excel с помощью VBA.[1]

Рис. 1. Столбцы и строки скрыты за исключением рабочего диапазона

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

Сохранение всех рабочих книг

Следующая процедура циклически просматривает все рабочие книги в коллекции Workbooks и сохраняет каждый файл, который сохранялся ранее.

Public Sub SaveAllWorkbooks()
Dim Book As Workbook
For Each Book In Workbooks
If Book.Path <> » » Then Book.Save
Next Book
End Sub

Обратите внимание на то, как используется свойство Path. Если для какой-либо рабочей книги свойство Path не задано, значит, файл еще не сохранялся (это новая рабочая книга). Данная процедура игнорирует такие рабочие книги и сохраняет только те из них, свойство Path которых имеет ненулевое значение.

Сохранение и закрытие всех рабочих книг

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

Sub CloseAllWorkbooks()
Dim Book As Workbook
For Each Book In Workbooks
If Book.Name <> ThisWorkbook.Name Then
Book.Close savechanges:=True
End If
Next Book
ThisWorkbook.Close savechanges:=True
End Sub

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

Частичное сокрытие элементов рабочего листа

В примере из этого раздела скрываются все строки и столбцы рабочего листа за исключением тех из них, которые находятся в текущем выделенном диапазоне (рис. 1).

Sub HideRowsAndColumns()
Dim row1 As Long, row2 As Long
Dim col1 As Long, col2 As Long
If TypeName(Selection) <> » Range » Then Exit Sub
‘ Если последняя строка либо последний столбец скрыты,
‘ отобразить все и выйти
If Rows(Rows.Count).EntireRow.Hidden Or _
Columns(Columns.Count).EntireColumn.Hidden Then
Cells.EntireColumn.Hidden = False
Cells.EntireRow.Hidden = False
Exit Sub
End If
row1 = Selection.Rows(1).Row
row2 = row1 + Selection.Rows.Count — 1
col1 = Selection.Columns(1).Column
col2 = col1 + Selection.Columns.Count — 1
Application.ScreenUpdating = False
On Error Resume Next
‘ Скрыть строки
Range(Cells(1, 1), Cells(row1 — 1, 1)).EntireRow.Hidden = True
Range(Cells(row2 + 1, 1), Cells(Rows.Count, _
1)).EntireRow.Hidden = True
‘ Скрыть столбцы
Range(Cells(1, 1), Cells(1, col1 — 1)).EntireColumn.Hidden = True
Range(Cells(1, col2 + 1), Cells(1, _
Columns.Count)).EntireColumn.Hidden = True
End Sub

Чтобы показать все строки и столбцы, встаньте на перекрестие строк и столбцов (выделено красным на рис. 1), и пройдите по меню: Главная –> Ячейки –> Формат –> Скрыть или отобразить –> Отобразить строки. Повторите этот путь, чтобы отобразить столбцы (рис. 2).

Рис. 2. Показать все строки и столбцы

Если выделенный диапазон включает несвязанные диапазоны, видимым останется только первый из них.

Синхронизация выделенного диапазона на листах рабочей книги

Если вы работаете с рабочими книгами, состоящими из нескольких листов, то, вероятно, знаете, что Excel не может «синхронизировать» листы в рабочей книге. Другими словами, не существует автоматического способа сделать так, чтобы все листы имели одинаковые выделенные диапазоны и верхние левые ячейки. Макрос VBA, показанный ниже, берет за основу активный рабочий лист и выполняет следующие действия со всеми остальными рабочими листами в книге:

  • выделяет тот же диапазон, что и в активном листе;
  • задает ту же левую верхнюю ячейку, что и на активном листе (рис. 3).

Рис. 3. Синхронизация выделенного диапазона на листах рабочей книги

Excel VBA — UsingVBA для создания новой отформатированной книги

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

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

Читать еще:  Excel rows count

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

4 Ответов

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

Если вы хотите вывести данные из Access to Excel, вы должны выполнить следующие действия:

  1. Создание (или открытие) новой книги
  2. Считывание ваших данных
  3. Запишите свои данные в рабочую книгу
  4. Форматирование данных в рабочей книге

Я сосредоточусь на выводе данных и оставлю форматирование (часть данных является сложной. форматирование очень простое)

Во-первых, вам нужно включить объекты Excel в вашем файле доступа: меню Сервис > ссылки. Найдите библиотеку объектов Microsoft Excel 12.0 и активируйте checkbox. Теперь у вас есть полная библиотека Excel к вашим услугам 🙂

Теперь настало время для хруста данных. Я буду считать, что вам нужно создать новую рабочую книгу:

Если вы хотите отформатировать ячейки, вы можете использовать свойства WKS.cells(i,j) (или WKS.range(. ) ).

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

Я надеюсь, что это поможет вам

— добавление одной колонки

‘извлечение строк из access и помещение их в excel

-тогда откройте этот excel и скопируйте строки

‘делая первый 6-й ряд, чтобы быть смелым

Вы не даете много подробностей, поэтому я не могу дать вам много деталей взамен. Но вот как бы я это сделал:

  1. Создайте новую книгу вручную с двумя листами
  2. На одном листе добавьте таблицу внешних данных, которая возвращает список имени поставщика, например SELECT SupplierName FROM tblSuppliers WHERE Active=True; или что-то в этом роде.
  3. Создайте именованный диапазон уровня рабочей книги, который динамически расширяется с помощью этой таблицы запросов
  4. На втором листе добавьте внешнюю таблицу данных, например SELECT * из заказов, где SupplierName=? (Это будет запрос параметров). Запустите эту таблицу внешних данных в строке 3
  5. Я строю, ставлю флажок combobox, который указывает на список поставщиков.

Теперь VBA-это просто

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

  • Создание внешней таблицы данных
  • Создание запроса параметров (старая ссылка http://www.dicks-clicks.com/excel/ExternalData6.htm )
  • Создание динамически расширяющегося имени диапазона
  • Добавьте combobox или проверку данных, которая указывает на диапазон на другом листе
  • То, что SQL выше, очевидно, не правильно, но я предполагаю, что вы можете написать правильное утверждение SQL

Вы должны быть в состоянии найти подробную информацию обо всем этом, но если нет, напишите еще один вопрос.

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

Вы также можете сделать шаблон электронной таблицы, скопировать содержимое в шаблон и сохранить как. Ваш пост не указывает, сколько на самом деле нужно сделать форматирования.

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

Я использую Excel 2007 для создания сложной книги с несколькими вкладками (лист). Я хотел бы сохранить это как PDF из VBA. Я пытаюсь использовать ExportAsFixedFormat. Конечно же, этот вызов.

Как получить текущую дату создания файла рабочей книги, используя VBA в excel 2010? Я просмотрел все свойства ThisWorkBook и, кажется, ничего там не нашел.

Следующий vba открывает книгу excel проверяет, находится ли она в режиме только для чтения, если она затем ждет, пока чтение/запись активна, а затем запускает код. Простой Мои вопросы в том, что у.

Я знаю, как добавить дочернюю страницу в существующую книгу, с url, как это: /node/add/book?parent=9228 Последний номер-это идентификатор страницы книги. Как я могу настроить его для создания новой.

Я отвечаю за очень большую таблицу Excel 2010 со ссылками на все виды внешних источников данных, включая Bloomberg, 65 листов с модулями vba и ссылками на другие надстройки vba. Я заметил, что.

Меня только что познакомили с VBA. Однако при сохранении моего первого файла excel с кодом VBA, С.расширение xlsm (мне посоветовали это сделать), а затем снова открыв этот сохраненный файл, я понял.

(Excel VBA) Как бы я объявил активную переменную рабочей книги. или вообще переменная рабочей книги. У меня есть программа, которая листает взад и вперед между 2 excel рабочими книгами, и в.

Влияет ли размер книги excel на выполнение кода VBA? Я написал код VBA в Книгу excel, размер которой составляет 200 MB.The excel книга имеет кнопку обзора, и эта кнопка обзора выбирает другой файл.

Я работаю над сценарием Excel VBA, который требует, чтобы я копировал и вставлял различные детали из исходной книги excel в 5 новых книг excel упорядоченным образом. Кроме того, я добавляю Macros в.

Я использую Access 2007. В моем файле доступа у меня есть код VBA, который открывает Excel книга, когда начинается программа доступа. Книга Excel остается открытой пока программа работает. Я.

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