Сохранение листов книги как отдельных файлов
Про сборку листов из нескольких книг в одну текущую я уже писал здесь. Теперь разберем решение обратной задачи: есть одна книга 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 а листы в них переименовались на "Данные"