Создание файлов Excel методами Workbooks.Add, Worksheet.Copy и текстовых файлов с помощью оператора Open и метода CreateTextFile из кода VBA Excel. Создание документов Word рассмотрено в отдельной статье.
Создание файлов Excel
Метод Workbooks.Add
Описание
Файлы Excel можно создавать из кода VBA с помощью метода Add объекта Workbooks.
Ссылку на новую книгу Excel, созданную методом Workbooks.Add, можно присвоить объектной переменной с помощью оператора Set или обращаться к ней, как к активной книге: ActiveWorkbook.
Синтаксис
Workbooks.Add (Template)
Template – параметр, который определяет, как создается новая книга.
| Значение Template | Параметры новой книги |
|---|---|
| Отсутствует | Новая книга с количеством листов по умолчанию. |
| Полное имя существующего файла Excel | Новая книга с указанным файлом в качестве шаблона. |
| xlWBATChart | Новый файл с одним листом диаграммы. |
| xlWBATWorksheet | Новый файл с одним рабочим листом. |
Примеры
Пример 1
Создание новой книги Excel с количеством листов по умолчанию и сохранение ее в папку, где расположен файл с кодом VBA:
|
1 2 3 4 5 6 7 8 |
Sub Primer1() 'Создаем новую книгу Workbooks.Add 'Сохраняем книгу в папку, где расположен файл с кодом ActiveWorkbook.SaveAs (ThisWorkbook.Path & "\Моя новая книга.xlsx") 'Закрываем файл ActiveWorkbook.Close End Sub |
Файл «Моя новая книга.xlsx» понадобится для следующего примера.
Пример 2
Создание новой книги по файлу «Моя новая книга.xlsx» в качестве шаблона с присвоением ссылки на нее объектной переменной, сохранение нового файла с новым именем и добавление в него нового рабочего листа:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Sub Primer2() 'Объявляем объектную переменную с ранней привязкой Dim MyWorkbook As Workbook 'Создаем новую книгу по шаблону файла "Моя новая книга.xlsx" Set MyWorkbook = Workbooks.Add(ThisWorkbook.Path & "\Моя новая книга.xlsx") With MyWorkbook 'Смотрим какое имя присвоено новому файлу по умолчанию MsgBox .Name '"Моя новая книга1" 'Сохраняем книгу с новым именем .SaveAs (ThisWorkbook.Path & "\Моя самая новая книга.xlsx") 'Смотрим новое имя файла MsgBox .Name '"Моя самая новая книга" 'Добавляем в книгу новый лист с именем "Мой новый лист" .Sheets.Add.Name = "Мой новый лист" 'Сохраняем файл .Save End With End Sub |
Метод Worksheet.Copy
Описание
Если в коде VBA Excel применить метод Worksheet.Copy без указания параметра Before или After, будет создана новая книга с копируемым листом (листами). Новая книга станет активной.
Примеры
Пример 3
Создание новой книги с помощью копирования одного листа (в этом примере используется книга, созданная в первом примере):
|
1 2 3 4 5 6 7 8 9 |
Sub Primer3() 'Если книга источник не открыта, ее нужно открыть Workbooks.Open (ThisWorkbook.Path & "\Моя новая книга.xlsx") 'Создаем новую книгу копированием одного листа Workbooks("Моя новая книга.xlsx").Worksheets("Лист1").Copy 'Сохраняем новую книгу с именем "Еще одна книжица.xlsx" в папку, 'где расположен файл с кодом ActiveWorkbook.SaveAs (ThisWorkbook.Path & "\Еще одна книжица.xlsx") End Sub |
Также, как и при создании нового файла Excel методом Workbooks.Add, при создании новой книги методом Worksheet.Copy, можно ссылку на нее присвоить объектной переменной.
Пример 4
Создание новой книги, в которую включены копии всех рабочих листов из файла с кодом VBA:
|
1 2 3 |
Sub Primer4() ThisWorkbook.Worksheets.Copy End Sub |
Пример 5
Создание новой книги, в которую включены копии выбранных рабочих листов из файла с кодом VBA:
|
1 2 3 |
Sub Primer5() ThisWorkbook.Sheets(Array("Лист1", "Лист3", "Лист7")).Copy End Sub |
Создание текстовых файлов
Оператор Open
При попытке открыть несуществующий текстовый файл с помощью оператора Open, такой файл будет создан. Новый файл будет создан при открытии его в любом режиме последовательного доступа, кроме Input (только для чтения).
Пример
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Sub Primer6() Dim ff As Integer, ws As Object 'Получаем свободный номер для открываемого файла ff = FreeFile 'Создаем новый текстовый файл путем открытия 'несуществующего в режиме чтения и записи Open ThisWorkbook.Path & "\Мой-новый-файл.txt" For Output As ff 'Записываем в файл текст Write #ff, "Этот файл создан при его открытии оператором " & _ "Open по несуществующему адресу (полному имени)." 'Закрываем файл Close ff 'Открываем файл для просмотра Set ws = CreateObject("WScript.Shell") ws.Run ThisWorkbook.Path & "\Мой-новый-файл.txt" Set ws = Nothing End Sub |
В имени текстового файла пробелы заменены дефисами (знаками минус), так как метод Run объекта Wscript.Shell не способен открывать файлы с именами, содержащими пробелы.
Рекомендую открывать файлы для просмотра методом ThisWorkbook.FollowHyperlink. Пример и преимущества этого метода в статье VBA Excel. Открыть файл другой программы.
Метод FileSystemObject.CreateTextFile
Для создания нового текстового файла из кода VBA Excel по указанному имени, можно использовать метод CreateTextFile объекта FileSystemObject.
Пример
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Sub Primer7() Dim fso, fl, ws 'Создаем новый экземпляр объекта FileSystemObject Set fso = CreateObject("Scripting.FileSystemObject") 'Присваиваем переменной fl новый объект TextStream, 'связанный с созданным и открытым для записи файлом Set fl = fso.CreateTextFile(ThisWorkbook.Path & "\Еще-один-текстовый-файл.txt") 'Записываем в файл текст fl.Write ("Этот текстовый файл создан методом CreateTextFile объекта FileSystemObject.") 'Закрываем файл fl.Close 'Открываем файл для просмотра Set ws = CreateObject("WScript.Shell") ws.Run ThisWorkbook.Path & "\Еще-один-текстовый-файл.txt" End Sub |
Стоит отметить, что новый текстовый файл может быть создан и с помощью метода OpenTextFile объекта FileSystemObject при условии присвоения параметру create значения True.
А как сделать, чтобы имя файла бралось из текстбокса, и вообще, чтобы имя файла было динамическим, чтобы можно было дату, счетчик ставить?
Привет, Роберт!
Полное имя файла или его часть можно заменить переменной или текстовым содержимым элемента управления формы:
Спасибо! Работает, но только теперь сохраняет не в указанную папку, а в «Документы», и название рабочей папки вставляет в имя файла. К примеру, если у меня рабочий файл находится в папке «Проекты», то при сохранении появляется файл с именем «ПроектыИмя_проекта.xlsx»
Роберт, перед именем файла нужна обратная косая черта (\), как в примере со счетчиком. Я добавил ее в первый пример, чтобы в текстовом поле можно было ее не указывать.
Доброго времени суток! Сохраняю файл следующей командой
сохраняет книгу с наименованием содержащим расширение .xlsm (1_ПРОБА МАКРОСОВ.xlsm.xlsx) не очень красиво.
Подскажите как подавить данное расширение в имени.
И если не трудно, поясните как сохранить файл с именем из двух ячеек книги? С подавлением неприемлемых символов («, / и т.д.)?
Привет, Павел!
Выражение ThisWorkbook.Name возвращает имя книги с расширением, в вашем случае: 1_ПРОБА МАКРОСОВ.xlsm. Поэтому, если вы хотите сохранить ActiveWorkbook с расширением .xlsx, просто замените расширение:
но имейте в виду, что ActiveWorkbook — это не ThisWorkbook, а другая книга без макросов, так как книгу с макросами невозможно сохранить с расширением .xlsx.
Перед использованием ячеек, избавьтесь от неприемлемых символов с помощью функции Replace, заменив их на пустые строки. Код с ячейками может выглядеть так:
ws.Run ThisWorkbook.Path & "\Мой-новый-файл.txt"даёт ошибку если Excel файл находится в папке а не на рабочем столе.
Привет, Самвел!
Если вы используете в адресе
ThisWorkbook.Path, файл Excel и текстовый файл должны находиться в одной папке. (Ответ не соответствует ситуации, так как текстовый файл создается кодом VBA в той же папке, где находится файл Excel.)Добрый вечер, Евгений!
Я скопировал Ваш код в excel файл на рабочем столе. всё работает.
Затем поместил файл в папку и происходит ошибка при попытке просмотра txt файла:
Здесь ws.Run ThisWorkbook.Path & «\Мой-новый-файл.txt»
Ниже показан код.
Спасибо.
Похоже если название папки состоит их 2х слов с пробелом, то возникает ошибка.
А как возможно открыть для просмотра txt создавая его в папку с произвольным названием?
Если в адресе есть пробелы, нужны дополнительные кавычки:
Большое спасибо!
Всё работает.
Самвел, есть еще более простой вариант, не требующий дополнительных кавычек:
Здравствуйте!
Евгений, подскажите пожалуйста как создать файл в фоновом режиме. Чтобы во время создания не выводилась на экран.
Здравствуйте, Андрей!
Если речь идет о файле Excel, создайте новый экземпляр приложения, который после его создания по умолчанию будет находиться в фоновом режиме, как и созданная в нем книга:
thank you very much
Обсуждение закрыто.