Сохранение листов книги как отдельных файлов
Про сборку листов из нескольких книг в одну текущую я уже писал здесь. Теперь разберем решение обратной задачи: есть одна книга Excel, которую нужно "разобрать", т.е. сохранить каждый лист как отдельный файл для дальнейшего использования.
Примеров подобного из реальной жизни можно привести массу. Например, файл-отчет с листами-филиалами нужно разделить на отдельные книги по листам, чтобы передать затем данные в каждый филиал и т.д.
Если делать эту процедуру вручную, то придется для каждого листа выполнить немаленькую цепочку действий (выбрать лист, правой кнопкой по ярлычку листа, выбрать Копировать, указать отдельный предварительно созданный пустой файл и т.д.) Гораздо проще использовать короткий макрос, автоматизирующий эти действия.
Способ 1. Простое разделение
Нажмите сочетание Alt+F11 или выберите в меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor), вставьте новый модуль через меню Insert - Module и скопируйте туда текст этого макроса:
Sub SplitSheets1()
Dim s As Worksheet
For Each s In ActiveWorkbook.Worksheets 'проходим по всем листам в активной книге
s.Copy 'копируем каждый лист в новый файл
Next
End Sub
Если теперь выйти из редактора Visual Basic и вернуться в Excel, а затем запустить наш макрос (Alt+F8), то все листы из текущей книги будут разбиты по отдельным новым созданным книгам.
Способ 2. Разделение с сохранением
При необходимости, можно созданные книги сразу же сохранять под именами листов. Для этого макрос придется немного изменить, добавив команду сохранения в цикл:
Sub SplitSheets2()
Dim s As Worksheet
Dim wb as Workbook
Set wb = ActiveWorkbook
For Each s In wb.Worksheets 'проходим во всем листам активной книги
s.Copy 'сохраняем лист как новый файл
ActiveWorkbook.SaveAs wb.Path & "\" & s.Name & ".xlsx" 'сохраняем файл
Next
End Sub
Этот макрос сохраняет новые книги-листы в ту же папку, где лежал исходный файл. При необходимости сохранения в другое место, замените wb.Path на свой путь в кавычках, например "D:\Отчеты\2012" и т.п.
Если нужно сохранять файлы не в стандартном формате книги Excel (xlsx), а в других (xls, xlsm, xlsb, txt и т.д.), то кроме очевидного изменения расширения на нужное, потребуется добавить еще и уточнение формата файла - параметр FileFormat:
ActiveWorkbook.SaveAs wb.Path & "\" & s.Name & ".xlsb", FileFormat:=50
Для основных типов файлов значения параметра FileFormat следующие:
- XLSX = 51
- XLSM = 52
- XLSB = 50
- XLS = 56
- TXT = 42
Способ 3. Сохранение в новые книги только выделенных листов
Если вы хотите раскидать по файлам не все листы в вашей книге, а только некоторые, то макрос придется немного изменить. Выделите нужные вам листы в книге, удерживая на клавиатуре клавишу Ctrl или Shift и запустите приведенный ниже макрос:
Sub SplitSheets3()
Dim AW As Window
Set AW = ActiveWindow
For Each s In AW.SelectedSheets
Set TempWindow = AW.NewWindow 'создаем отдельное временное окно
s.Copy 'копируем туда лист из выделенного диапазона
TempWindow.Close 'закрываем временное окно
Next
End Sub
Создавать новое окно и копировать через него, а не напрямую, приходится потому, что Excel не умеет копировать группу листов, если среди них есть листы с умными таблицами. Копирование через новое окно позволяет такую проблему обойти.
Способ 4. Сохранение только выделенных листов в новый файл
Во всех описанных выше способах каждый лист сохранялся в свой отдельный файл. Если же вы хотите сохранить в отдельный новый файл сразу группу выделенных предварительно листов, то нам потребуется слегка видоизменить наш макрос:
Sub SplitSheets4()
Dim CurW As Window
Dim TempW As Window
Set CurW = ActiveWindow
Set TempW = ActiveWorkbook.NewWindow
CurW.SelectedSheets.Copy
TempW.Close
End Sub
Способ 5. Сохранение листов как отдельных PDF-файлов
Этот способ чем-то похож на второй, но листы сохраняются не как отдельные книги Excel, а в формате PDF, что часто требуется, если никто не должен менять документ и увидеть ваши формулы. Обратите внимание, что:- для этого используется уже другой метод (ExportAsFixedFormat а не Copy)
- листы выводятся в PDF с параметрами печати, настроенными на вкладке Разметка страницы (Page Layout)
- книга должна быть сохранена на момент экспорта
Нужный нам код будет выглядеть следующим образом:
Sub SplitSheets5()
Dim s As Worksheet
For Each s In ActiveWorkbook.Worksheets
s.ExportAsFixedFormat Filename:=ThisWorkbook.Path & "\" & s.Name & ".pdf", Type:=xlTypePDF
Next
End Sub
Способ 6. Готовый макрос из надстройки PLEX
Если лень или нет времени внедрять все вышеописанное, то можно воспользоваться готовым макросом из моей надстройки PLEX:
Ссылки по теме
- Сборка листов из нескольких книг в одну
- Что такое макросы, куда вставлять код на Visual Basic, как их использовать.
Путь для каждого листа разный
А еще лучше - скачайте пример в заголовке статьи и посмотрите как все работает вживую.
Вопрос собственно такой: как сохранить лист или диапазон ячеек в формате html?
пробовал таким способом - сохраняет в каком-то формате, который не понимает браузер.
ThisWorkbook.Sheets("SHEET3").Copy ActiveWorkbook.SaveAs "C:\table.htm", fileformat:=44 ActiveWorkbook.Closep = ActiveWorkbook.Path Sheets("Лист2").Copy ActiveWorkbook.SaveAs Filename:= p & "\Лист2.csv", FileFormat:=xlCSV ActiveWindow.CloseВообще ничего не понимаю в макросах, что я делаю не так из предложенного?
Задача: необходимо Лист2 всегда сохранять в отдельный файл с именем из ячейки A5 именно из Листа2. Как это прописать?
Спасибо.
Для полного счастья в 3 способе не хватает возможности "разорвать связи" в новых листах. Вроде задача простая, а сообразить не могу
Выше речь шла о замене формул на значения... мне как раз формулы необходимо оставить (в рамках листа), а связи с книгой разорвать.
Большое спасибо за очень полезную информацию!
Сработали все 3 способа (первый не пробовала), за исключением 2 моментов:
при способе №2 из 40 с лишним вкладок открылось только 25 шт в новых документах с сохранением названия вкладки, как и должно было быть. Остальные вкладки даже не показались, мб есть какое-то ограничение по кол-ву вкладок?
Второй момент: Подскажите пожалуйста,как нужно дописать макрос, чтобы способ номер 3 сохранял вкладки в документы с названием вкладок, как в способе 2?
Спасибо!
Марина
Подскажите, как реализовать копирование диапазона листа в новую книгу. С листом понятно, но на нём много лишних данных, которые в новой книге не нужны.
С ув., Василий
Range("A1:B2").Copy Workbooks.Add ActiveSheet.PasteИ еще может подскажете, мне нужно чтобы после того как сохранился лист в TXT, он после этого менял своё расширение на HTML, вообщем сайт пишу в XL, каждый лист у меня это страничка, на листах я пишу HTML код в чистом виде, вот мне и нужно чтобы макрос сначало в TXT перегонял без форматирования и формул, потом сохранял как HTML страничку. Надеюсь я понятно описал то что хочу сделать, спасибо)
Дописал:
Sub SplitSheets2() Dim s As Worksheet Dim wb as Workbook Set wb = ActiveWorkbook For Each s In wb.Worksheets 'проходим во всем листам активной книги s.Copy 'сохраняем лист как новый файл ActiveWorkbook.SaveAs wb.Path & "\" & s.Name & s.Range("A22") & ".xlsx" 'сохраняем файл Next End Subподскажите пожалуйста а возможно сделать так что бы он выгружал из листа только не скрытые ячейки
У меня вопрос почти такой же, как у Юлии, но только про формат .pdf. Пробовал в Способе 2 менять расширение на ".pdf" - конечно, файлы создавались, но не открывались, затем пробовал прикрутить в код такую строку:
Sub SplitSheets5()
ActiveWorkbook.RefreshAll
Dim s As Worksheet
For Each s In ActiveWindow.SelectedSheets
s.ExportAsFixedFormat Filename:="C:\doc\" & "\" & s.Name & ".pdf", Type:=xlTypePDF
Next
End Sub
Sub SplitSheets5()
Dim s As Worksheet
For Each s In ActiveWorkbook.Worksheets
s.ExportAsFixedFormat Filename:=ThisWorkbook.Path & "\" & s.Name & ".pdf", Type:=xlTypePDF
Next
End Sub
У меня ситуация, что нужно сохранить файл в формате (Tab delimited) *.txt
в таком случае каким будет значение File Format: ?
или примерно так:
ActiveWorkbook.SaveAs Filename:= p & "\Лист2.txt", FileFormat:= ???
Спасибо!!!
А также сохранение только выделенных листов в новый файл.
Sub invoice()
Sheets(Array("INVOICE Nr. 0200", "Specificatie 0200", "DVI" [IMG] ).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"G:\KW 5\invoice 200.pdf", Quality:=xlQualityStandard, IncludeDocProperties _
:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End Sub
1 можна изменить (INVOICE Nr. 0200", "Specificatie 0200", "DVI" ))на проста (Select Sheet) выделиные листы
2 адрес сохранения вместо (G:\KW 5\invoice 200.pdf" [IMG] в папку где находится книга с названием PDF по ячейки с Sheet XXXX B9.
Огромнае спосибо
Ну например была книга с листами, 1,2,3 ... сформировались книги 1,2,3 а листы в них переименовались на "Данные"
"название листа" № "номер ячейки" от "номер ячейки" (чтобы получилось например счет №23 от 10.04.2017)
в формате PDF и чтобы файл после сохранения не открывался, а просто сохранялся в ту же папку откуда документ?
пытался компоновать макросы в примерах но не получилось((
В файле около 10 листов (есть скрытые). Мне нужно скопировать не удаляя оригиналы ТОЛЬКО выделенные.
Сохранить файлы с названиями листов в ту же папку где и основной файл.
Пробовал компоновать коды. Ни так, ни сяк.
Спасибо.
Николай, прошу вашей помощи! Макрос просто волшебный и очень помог мне с одним фалом, а со вторым есть проблемы. Вопрос такой уже был, но то решение, которое вы предложили мне не помогло. Использую вот такой макрос
Но он не работает выдает ошибку 1004. При просмотре Debug подсвечивает то, что я выделила жирным. Что не так? Задача такова. Есть файл, где много страниц и необходимо каждый лист сделать отдельной книгой, как значение. Можно даже не сохранять, а просто открыть, но такой метод тоже не работает
Проверьте Рецензирование - Защитить книгу (Review - Protect Workbook).
Поможете с задачкой? Надергал строчек из разных способов, но где то все равно закралась ошибка.
Модифицирую способ 5.
Требуется: Выделенные листы сохранить в pdf (Имя файла + имя листа).
Ооооочень долго сохранял!!! От чего это зависит, как ускорить этот процесс?
Есть ли возможность сделать макрос, чтобы сохранять в PDF в фоновом процессе?
В результате получается 1 стр. PDF.
Попробовал на работе - тоже самое. 10 секунд на сохранение файла *.pdf.
Может надо использовать какой-то определённый PDF драйвер/принтер?
Office без обновлений.
p.s. Только что заметил - PDF записались в папку, откуда был взят xls файл. А так же копии этих файлов были записаны в папку автостарта, где хранится personal.xlsb...
Ту миллион долларс (с)
Есть второй вариант - можно пройтись по всем листам и сохранить каждый из них отдельно. Это работает. Но удалять лишние листы не охота, а "прикрутить" условие, благодаря которому макрос будет сохранят лишь выбранные листы книги - у меня не получилось.
Не нашли ли вы какое либо решение?
Макросы шикарные, но к своему файлу приделать с более сложной задачей моих навыков не хватает, поэтому надеюсь на вашу помощь!
Суть задачи такова:
Есть книга, в ней около 60 листов. Есть лист "смещение Х", он защищён известным мне паролем от изменений. На листе в диапазоне A1:I645 есть 20 таблиц (разделено так для удобства печати), А1-I37 - первая таблица, А38-I69- вторая и так далее через 31 строку. В ячейках I31, I63, I95....I639 через 32 строки в каждой таблице подитог. Есть лист "смещение Н", он тоже защищён известным мне, но другим паролем от изменений. На листе А1-I37 первая таблица, А38-I74 вторая таблица. В ячейках I31 и I68 подитог. Заполнение таблиц последовательное сверху в низ.Таблицы заполняется как правило не все. Как сохранить в один файл эти 2 листа, в новом файле снять пароль с них, формулы заменить значениями? Причем что бы скопировались заполненные таблицы, т.е. с не нулевым подитогом, это особенно актуально в листе с 20 таблицами. Или их наверное проще удалить в новом файле нижние незаполненные таблицы?
А можно на примере этого макроса сделать перевод в *pdf одним файлом?
Sub SplitSheets5()
Dim s As Worksheet
For Each s In ActiveWorkbook.Worksheets
s.ExportAsFixedFormat Filename:=ThisWorkbook.Path & "\" & s.Name & ".pdf", Type:=xlTypePDF
Next
End Sub
1. При использовании готового макроса из надстройки PLEX "Сохранение листов как файлов" во вновь созданных файлах слетает галочка с позиции "Точность как на экране" (Задать указанную точность). Поскажите, есть какой-нибудь способ оптом вернуть это свойство в книгу, не меняя в каждом файле индивидуально?
2. В надстройке не сработало заполнение префиксов и суффиксов.