Главная » Макросы для Excel » Макрос сохранения листа Excel в файл

Макрос сохранения листа Excel в файл

Данный макрос позволяет упростить процедуру сохранения активного листа в книге Excel в отдельный файл.

Для использования этого макроса на любом листе в книге Excel создайте кнопку, и назначьте ей макрос СохранитьЛистВФайл.

При запуске макроса (нажатии кнопки) будет выведено диалоговое окно выбора имени для сохраняемого файла, после чего текущий лист будет сохранён под заданным именем в выбранной папке.

Сохранение производится в формате XLS (формат Excel 2003)
Если пользователь отказался от ввода имени файла (нажал клавишу ESC или кнопку «Отмена» в диалоговом окне),
то сохранения листа в файл не происходит.

Sub СохранитьЛистВФайл()
    On Error Resume Next
    ' название подпапки, в которую по-умолчанию будет предложено сохранить файл
    Const REPORTS_FOLDER = "Отчёты\"
    ' создаём папку для файла, если её ещё нет
    MkDir ThisWorkbook.Path & "\" & REPORTS_FOLDER
    ' выбираем стартовую папку
    ChDrive Left(ThisWorkbook.Path, 1): ChDir ThisWorkbook.Path & "\" & REPORTS_FOLDER
 
    ' вывод диалогового окна для запроса имени сохраняемого файла
    Filename = Application.GetSaveAsFilename("отчёт.xls", "Отчёты Excel (*.xls),", , _
                                             "Введите имя файла для сохраняемого отчёта", "Сохранить")
    ' если пользователь отказался от выбора имени файла - отменяем сохранение листа в файл
    If VarType(Filename) = vbBoolean Then Exit Sub
 
    ' копируем активный лист (при этом создаётся новая книга)
    Err.Clear: ActiveSheet.Copy: DoEvents
    If Err Then Exit Sub    ' произошла какая-то ошибка при попытке копирования листа

    ' убеждаемся, что активной книгой является копия листа
    If ActiveWorkbook.Worksheets.Count = 1 And ActiveWorkbook.Path = "" Then
        ' сохраняем файл под заданным именем в формате Excel 2003
        ActiveWorkbook.SaveAs Filename, xlWorkbookNormal
 
        ' закрываем сохранённый файл
        ' (удалите следующую строку, если закрывать созданный файл не требуется)
        ActiveWorkbook.Close False
    End If
End Sub

 

PS: Кто-то может сказать, что для сохранения листа в файл в объектной модели Excel есть метод SaveAs, применимый к объекту Worksheet.

Но, как ни странно, выполнение кода ActiveSheet.SaveAs "<имя файла>" приводит к сохранению книги целиком, что равносильно использованию кода ActiveWorkbook.SaveAs "<имя файла>"

Почему этот метод сохранения работает так нелогично - лично мне не понятно (видимо, Microsoft что-то там перемудрил)

  • 187270 просмотров

Комментарии

Настройки просмотра комментариев

Выберите нужный метод показа комментариев и нажмите "Сохранить установки".
Гость, 4 Мар 2013 - 14:50.#1

галочку снял чтобы показывало расширение и при сохранении внизу показывает xlsx, но сохраняет все равно как файл без раширения... Удалить лишние столбцы вы имеете ввиду вручную? если нет, можно примерчик? очень благодарен вам за ответы.

Игорь (администратор сайта), 4 Мар 2013 - 14:40.#2

Что значит «без формата»? То, что вы не видите расширение XLSX у созданного файла?
Так, может, у вас отображение расширений файлов в Windows отключено?

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

Гость, 4 Мар 2013 - 14:14.#3

Я так и прописал, все равно сохраняет файл без формата...
Я имел ввиду можно ли как то задать какие колонки сохранять?

Игорь (администратор сайта), 4 Мар 2013 - 13:41.#4

По формату сохранения файлов: достаточно было чуть-чуть пролистать страницу вниз, чтобы увидеть решение:
http://excelvba.ru/code/SaveActiveSheet#comment-1460

Причем тут ячейки и фильтры - не понял.
Макрос сохраняет копию листа, в том виде, в каком он отображается на экране.
(если установлены фильтры - лист с этими фильтрами и сохранится)

Гость, 4 Мар 2013 - 13:16.#5

Добрый день, вопрос следующий! Как сохранить excel в формате xlsx и сохранить ячейки отобранные после фильтра? Прошу прощения, если задавался вопрос, не получилось поменять формат, сохраняет файл не определяя что он excel(

Павел, 21 Фев 2013 - 14:57.#6

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

Гость, 1 Фев 2013 - 20:30.#7

Спасибо. А если нужно указать конкретный диапазон ячеек?

Игорь (администратор сайта), 1 Фев 2013 - 20:20.#8

Надо перед строкой

ActiveWorkbook.SaveAs Filename, xlWorkbookNormal

дописать код удаления пустых строк, который можно взять здесь:
http://excelvba.ru/code/ConditionalRowsDeleting

Добавляемый код будет выглядеть примерно так:

Dim ra As Range, delra As Range
' перебираем все строки в используемом диапазоне листа
For Each ra In ActiveSheet.UsedRange.Rows
    ' если строка полностью пустая
    If Not IsNull(ra.Text) Then
        ' добавляем строку в диапазон для удаления
        If delra Is Nothing Then Set delra = ra Else Set delra = Union(delra, ra)
    End If
Next
' если подходящие строки найдены - удаляем их
If Not delra Is Nothing Then delra.EntireRow.Delete

(будут удалены строки, в которых ни одна ячейка не заполнена)

Гость, 1 Фев 2013 - 20:05.#9

Здравствуйте. Помогите, пожалуйста. Что нужно написать в вашем макросе, чтобы в копии файла (листа) пустые строки были скрыты (или удалялись)? Спасибо.

Игорь (администратор сайта), 14 Сен 2012 - 14:04.#10

Я не Владимир, но отвечу)

Если у вас Excel 2007 (или новее), то делается то просто - вместо расширения XLS ставите везде XLSX,

    ' вывод диалогового окна для запроса имени сохраняемого файла
   Filename = Application.GetSaveAsFilename("отчёт.xlsx", "Отчёты Excel (*.xlsx*),", , _
                                             "Введите имя файла для сохраняемого отчёта", "Сохранить")

и меняете константу xlWorkbookNormal на xlOpenXMLWorkbook

  ' сохраняем файл под заданным именем в формате Excel 2007 (без макросов)
       ActiveWorkbook.SaveAs Filename, xlOpenXMLWorkbook

Александр, 14 Сен 2012 - 12:04.#11

Владимир, добрый день!
Может Вы и уже такой вариант где-то описали, но не нашёл пока: как сохранить всю книгу без макросов?

Владимир, 24 Июл 2012 - 16:41.#12

3) - тут вопрос был вот в чем: что кнопка находится на листе 1, а надо сохранить указанные листы 2 и 3, а не текущий лист. А за остальное, конечно большое спасибо

Игорь (администратор сайта), 24 Июл 2012 - 16:03.#13

Владимир, с вопросами лучше обращаться на форумы по Excel,
а ко мне - только с оплачиваемыми заказами)

1) задать путь к папке можно так:

Folder$ = "D:\" & worksheets(1).range("a1") & "\"
MkDir Folder$

2) Ну так присвойте переменной сразу нужное имя, без лишних диалоговых окон:

Filename = Folder & "Ваше имя файла.xls"

3) а в чем, собственно, вопрос?

Итого получится что-то вроде такого:

Sub СохранитьЛистВФайл()
    On Error Resume Next
    Folder$ = "D:\" & Worksheets(1).Range("a1") & "\"
    MkDir Folder$    ' создаем папку, если её ещё нет

    Filename = Folder & "Ваше имя файла.xls"    ' полный путь к создаваемому файлу

    ' копируем активный лист (при этом создаётся новая книга)
    Err.Clear: ActiveSheet.Copy: DoEvents
    If Err Then Exit Sub    ' произошла какая-то ошибка при попытке копирования листа

    ' сохраняем файл под заданным именем в формате Excel 2003
    ActiveWorkbook.SaveAs Filename, xlWorkbookNormal
    ' закрываем сохранённый файл
    ActiveWorkbook.Close False
End Sub

Владимир, 24 Июл 2012 - 12:01.#14

Спасибо, это работает. Возникли еще парочка вопросов: 1) с помощью этого мы указываем путь, где будет сохранен файл
' путь к папке, в которую по-умолчанию будет предложено сохранить файл
Folder$ = "D:\": MkDir Folder$
ChDrive Left(Folder$, 1): ChDir Folder$ ' выбираем стартовую папку
как сделать так чтобы файл сохранялся по адресу "D:\папка" где название "папка" бралось бы из ячейки А1 листа 1.

2) Filename = Application.GetSaveAsFilename("отчёт.xls".... тут мы указываем заданное имя файла, а как сделать чтобы имя было свободно указываемое?

3) На листе 1 расчеты и кнопка "сохранить результат", на листе 2 и листе 3 таблицы с результатами вычислений. При нажатии на кнопку происходит сохранение значений листа 2 и листа 3 в отдельный файл в папку, имя которой указано в ячейке А1

Игорь (администратор сайта), 24 Июл 2012 - 10:36.#15

Надо в созданном файле заменить формулы значениями из ИСХОДНОГО листа
(после копирования листа, формулы в созданном файле могут пересчитаться неверно)

Для этого, перед строкой

' копируем активный лист (при этом создаётся новая книга)

добавляем строку
 arr = activesheet.usedrange.value ' записываем в массив значения ячеек до копирования



а после строки
If ActiveWorkbook.Worksheets.Count = 1 And ActiveWorkbook.Path = "" Then

вставляем сохранённые значения при помощи такой строки кода:
activesheet.usedrange.value = arr

Владимир, 24 Июл 2012 - 09:29.#16

Здравствуйте.
Подскажите, пожалуйста, что нужно изменить в вашем макросе, чтобы сохранить не активные листы (к примеру лист2 и лист3), но чтобы все выражения в скопированных листах были сохранены как значения

Игорь (администратор сайта), 18 Июн 2012 - 11:02.#17

Здравствуйте, Денис.
Проще будет в этом случае скопировать лист целиком, а потом очистить значения ячеек ниже 3-й строки, например, при помощи следующего кода:

activesheet.copy ' создаём копию листа
[4:1000].clearContents ' стираем только значения, начиная с 4-й строки листа-копии

Денис, 18 Июн 2012 - 10:58.#18

Здравствуйте.
Мне нужно скопировать с ЛИСТа, только ШАПКУ, т.е. из Листа первые ТРИ строки и оставить ФОРМАТИРОВАНИЕ! (по столбцам и по строкам)
P.S.: Можно скопировать и ЧЕТЫРЕ строки, а с последней удалить контент.
Заранее, Спасибо.

Игорь (администратор сайта), 15 Май 2012 - 05:16.#19

Заменить ActiveSheet.Copy на Worksheets("ИмяЛиста").Copy или Worksheets(НомерЛиста).Copy

Гость, 14 Май 2012 - 15:22.#20

Здравствуйте! Подскажите, пожалуйста, что нужно изменить в вашем макросе, чтобы сохранить не активный лист?

Гость, 25 Апр 2012 - 12:27.#21

Спасибо. Попробую. Хотя там используется ф-ция ".SaveCopyAs ", использоание которой ранее не привело к ожидаемому результату.

Игорь (администратор сайта), 25 Апр 2012 - 12:03.#22

Может, вам подойдёт макрос создания копии файла?
http://excelvba.ru/code/CreateBackup

Уберите строку, запрашивающую имя файла, и вместо нее задайте нужное имя файла в коде.
И всё будет работать без лишних диалоговых окон.

Гость, 25 Апр 2012 - 11:06.#23

Столкнулся с аналогичной задачей. Но основная сложность в сохранение копии файл. Как правильно отметили метод сохранить как не работает. Проблема в том, что всё автоматизировано (человек указывает папку с файлами исходниками, выходную папку, задает параметры), но для сохранения копии ему необходимо регулярно подтверждать сохранение. Можно ли доработать макрос, что бы при сохранении файла он не выдавал окна (остальные параметры можно расчитать)?
Спасибо

Гость, 23 Апр 2012 - 09:19.#24

Спасибо вам огромное! Всё работает как нужно! Очень помогли! Сам пробовал писать не получалось!=)

Игорь (администратор сайта), 23 Апр 2012 - 08:46.#25

Прочитайте, как сохранить файл Excel в другом формате
(как раз то, что вам нужно)

Гость, 23 Апр 2012 - 08:08.#26

А можно сделать так что бы не указывать путь. так как он всегда разный, что не удобно. то есть так что бы открыл из любого места файл книги, нажал выполнить макрос, и он бы сохранился в другом формате (двоичная книга) в той же папке с заменой старого! объединение листов в один файл не нужно, просто сохранение в другом формате с такой же структурой книги. Ну очень нужно... Спасибо!))

Игорь (администратор сайта), 18 Апр 2012 - 16:36.#27

Для сохранения книги в другом формате, достаточно внести изменения в эти 2 строки кода:

' изменяем расширение в 2 местах
Filename = Application.GetSaveAsFilename("отчёт.xlsb", "Отчёты Excel (*.xlsb),", , _
                                             "Введите имя файла для сохраняемого отчёта", "Сохранить")

' меняем xlWorkbookNormal (XLS) на xlExcel12 (XLSB)
ActiveWorkbook.SaveAs Filename, xlExcel12

Это если вам нужен макрос сохранения листа в файл.

Если же надо сохранить всю книгу - так запишите макрос при помощи макрорекордера.
Получится макрос из одной строки:

activeworkbook.saveas "<полный путь к создаваемому файлу>", xlExcel12

Гость, 18 Апр 2012 - 14:01.#28

Здравствуйте, подскажите пожалуйста макрос который бы сохранял книгу в другом формате, для уменьшения её объема, а именно в формат Двоичная книга Exel. в книге несколько листов.

Игорь (администратор сайта), 9 Апр 2012 - 08:45.#29

Замените начало функции на это:

Sub СохранитьЛистВФайл()
    On Error Resume Next
    ' путь к папке, в которую по-умолчанию будет предложено сохранить файл
    Folder$ = "D:\Distr\Моя папка\": MkDir Folder$
    ChDrive Left(Folder$, 1): ChDir Folder$ ' выбираем стартовую папку

    ' вывод диалогового окна для запроса имени сохраняемого файла
    ...

maxirk, 9 Апр 2012 - 08:11.#30

Огромное спасибо за скрипт! Сам бы не смог такое сделать!

Только я не понял, а как сделать что бы он сохранял в определенную папку.
А то у меня сохраняет в
C:\Users\bogdanov\AppData\Roaming\Microsoft\Excel\XLSTART\April

"April" - я создал папку и описал в макросе

Даниил, 15 Фев 2012 - 23:38.#31

Все сумел сам :)

Игорь (администратор сайта), 15 Фев 2012 - 23:19.#32

Этот макрос сохранит текущий лист в файл, взяв имя файла из ячейки:

Sub СохранитьЛистВФайл()
    On Error Resume Next
    ' название подпапки, в которую будет сохранён файл
    Const REPORTS_FOLDER = "Отчёты\"
    MkDir ThisWorkbook.Path & "\" & REPORTS_FOLDER    ' создаём папку для файла, если её ещё нет

    ' формируем имя файла из текста ячеек
    Filename = [c2] & [e2] & ".xlsx"
 
    ' копируем активный лист (при этом создаётся новая книга)
    Err.Clear: ActiveSheet.Copy: DoEvents
    If Err Then Exit Sub    ' произошла какая-то ошибка при попытке копирования листа

    ' убеждаемся, что активной книгой является копия листа
    If ActiveWorkbook.Worksheets.Count = 1 And ActiveWorkbook.Path = "" Then
        ' сохраняем файл под заданным именем в формате XLSX (xlOpenXMLWorkbook - Excel 2007)
        ActiveWorkbook.SaveAs Filename, xlOpenXMLWorkbook
        ' закрываем сохранённый файл
        ActiveWorkbook.Close False
    End If
End Sub

Даниил, 15 Фев 2012 - 21:52.#33

Здравствуйте, подскажите как изменить этот макрос для того чтобы он сохранял в формате excel 2007, а имя файла брал из ячейки C2 и E2. Заранее спасибо!

Игорь (администратор сайта), 9 Фев 2012 - 10:56.#34

А если все листы сохранять, то для каждого листа запрашивать имя файла?

PS: На форумах по Excel есть множество готовых макросов, сохраняющий все листы книги в отдельные файлы.
Не вижу смысла писать заново макрос, который написан уже тысячу раз.

Игорь, 9 Фев 2012 - 09:42.#35

Добрый день. А если надо все листы в книге? Спасибо

Отправить комментарий

Содержание этого поля является приватным и не предназначено к показу.
CAPTCHA
Подтвердите, пожалуйста, что вы - человек:
  _       ____                       ____    __  __ 
 | |__   | __ )  __  __  _ __ ___   | ___|  |  \/  |
 | '_ \  |  _ \  \ \/ / | '_ ` _ \  |___ \  | |\/| |
 | | | | | |_) |  >  <  | | | | | |  ___) | | |  | |
 |_| |_| |____/  /_/\_\ |_| |_| |_| |____/  |_|  |_|
Введите код, изображенный в стиле ASCII-арт.

Не получается применить макрос? Не удаётся изменить код под свои нужды?

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