Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Сохранение листов каждый в отдельный файл с именем из ячейки - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сохранение листов каждый в отдельный файл с именем из ячейки (Формулы/Formulas)
Сохранение листов каждый в отдельный файл с именем из ячейки
kot2012 Дата: Пятница, 27.11.2020, 17:11 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Добрый день!
Требуется помощь, ибо мозг уже взрывается %) В общем ситуация следующая, есть книга в которой макрос разбивает данные на отдельные листы (макрос "а"), спасибо отдельное за помощь в его написании bigor, теперь же нужно сохранить все эти листы отдельными файлами. Нашел макрос который это делает, но беда в том, что он сохраняет файл по имени листа, а имя листа ограничено 31 символом. Поэтому нашел выход, что можно сохранить с именем, взяв данные из ячейки. Но у меня не получается это сделать ((( либо выдаёт ошибку, либо сохраняет с одним и тем же именем. Прошу помощи у специалистов! А если еще и о бъясните где я затупил будет просто превосходно! :D
К сообщению приложен файл: 8288529.xlsm(33.6 Kb)


Сообщение отредактировал kot2012 - Пятница, 27.11.2020, 17:37
 
Ответить
СообщениеДобрый день!
Требуется помощь, ибо мозг уже взрывается %) В общем ситуация следующая, есть книга в которой макрос разбивает данные на отдельные листы (макрос "а"), спасибо отдельное за помощь в его написании bigor, теперь же нужно сохранить все эти листы отдельными файлами. Нашел макрос который это делает, но беда в том, что он сохраняет файл по имени листа, а имя листа ограничено 31 символом. Поэтому нашел выход, что можно сохранить с именем, взяв данные из ячейки. Но у меня не получается это сделать ((( либо выдаёт ошибку, либо сохраняет с одним и тем же именем. Прошу помощи у специалистов! А если еще и о бъясните где я затупил будет просто превосходно! :D

Автор - kot2012
Дата добавления - 27.11.2020 в 17:11
bigor Дата: Пятница, 27.11.2020, 17:37 | Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 365
Репутация: 66 ±
Замечаний: 0% ±

Excel 2016
Попробуйте так:
[vba]
Код
Sub SaveSheetsAsFiles()
    Application.ScreenUpdating = False 'Отключение обновления экрана
    Dim MySheet As Worksheet 'Ввод переменной MySheet типа "лист"
    Dim MyWorkbook As Workbook 'Ввод переменной MyWorkbook типа "книга"
      
    Set MyWorkbook = ActiveWorkbook
    For Each MySheet In MyWorkbook.Worksheets 'Цикл по каждому листу книги (файла)
        newName =MySheet.Cells(2,21).value & ".xlsx"
        MySheet.Copy 'Копирование листа в новую книгу (файл)
        ActiveWorkbook.SaveAs MyWorkbook.Path & "\" & newName  'Сохранение новой книги (файла)
        ActiveWorkbook.Close 'Закрытие новой книги (файла)
    Next
    Application.ScreenUpdating = True 'Включение обновления экрана
End Sub
[/vba]
Исправленные ошибки:
1. У Вас newName присваивалось до цикла, т.е. для всех листов оно было одинаковое
2. Изменил строку присваивания newName
 
Ответить
СообщениеПопробуйте так:
[vba]
Код
Sub SaveSheetsAsFiles()
    Application.ScreenUpdating = False 'Отключение обновления экрана
    Dim MySheet As Worksheet 'Ввод переменной MySheet типа "лист"
    Dim MyWorkbook As Workbook 'Ввод переменной MyWorkbook типа "книга"
      
    Set MyWorkbook = ActiveWorkbook
    For Each MySheet In MyWorkbook.Worksheets 'Цикл по каждому листу книги (файла)
        newName =MySheet.Cells(2,21).value & ".xlsx"
        MySheet.Copy 'Копирование листа в новую книгу (файл)
        ActiveWorkbook.SaveAs MyWorkbook.Path & "\" & newName  'Сохранение новой книги (файла)
        ActiveWorkbook.Close 'Закрытие новой книги (файла)
    Next
    Application.ScreenUpdating = True 'Включение обновления экрана
End Sub
[/vba]
Исправленные ошибки:
1. У Вас newName присваивалось до цикла, т.е. для всех листов оно было одинаковое
2. Изменил строку присваивания newName

Автор - bigor
Дата добавления - 27.11.2020 в 17:37
kot2012 Дата: Пятница, 27.11.2020, 17:53 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
bigor, ааааааа!!!!!! Вы мой спаситель!!!!! hands hands hands respect respect respect Хорошо когда есть такие люди! СПАСИБО ВАМ ОГРОМНЕЙШЕЕ!!!!!!
 
Ответить
Сообщениеbigor, ааааааа!!!!!! Вы мой спаситель!!!!! hands hands hands respect respect respect Хорошо когда есть такие люди! СПАСИБО ВАМ ОГРОМНЕЙШЕЕ!!!!!!

Автор - kot2012
Дата добавления - 27.11.2020 в 17:53
Hugo Дата: Пятница, 27.11.2020, 20:29 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3140
Репутация: 670 ±
Замечаний: 0% ±

2010, теперь уже с PQ
ОСТОРОЖНО - не всё что можно записать в ячейку можно использовать как имя файла!


excel@nxt.ru
webmoney: R418926282008 Z422237915069
 
Ответить
СообщениеОСТОРОЖНО - не всё что можно записать в ячейку можно использовать как имя файла!

Автор - Hugo
Дата добавления - 27.11.2020 в 20:29
kot2012 Дата: Понедельник, 30.11.2020, 12:35 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Hugo, вы были правы! В ячейках бывают символы "/" а я так понимаю с таким именем нельзя сохранять, есть ли возможность так же с помощью макроса заменить "/" на "_" ? Буду очень вам признателен за помощь
 
Ответить
СообщениеHugo, вы были правы! В ячейках бывают символы "/" а я так понимаю с таким именем нельзя сохранять, есть ли возможность так же с помощью макроса заменить "/" на "_" ? Буду очень вам признателен за помощь

Автор - kot2012
Дата добавления - 30.11.2020 в 12:35
bigor Дата: Понедельник, 30.11.2020, 15:14 | Сообщение № 6
Группа: Проверенные
Ранг: Обитатель
Сообщений: 365
Репутация: 66 ±
Замечаний: 0% ±

Excel 2016
kot2012, если дело только в "/", то достаточно
[vba]
Код
newName = Replace(MySheet.Cells(2, 21).Value & ".xlsx", "/", "_")
[/vba]
но чего то мне кажется, что там еще и "*", и "?", и т.д.
 
Ответить
Сообщениеkot2012, если дело только в "/", то достаточно
[vba]
Код
newName = Replace(MySheet.Cells(2, 21).Value & ".xlsx", "/", "_")
[/vba]
но чего то мне кажется, что там еще и "*", и "?", и т.д.

Автор - bigor
Дата добавления - 30.11.2020 в 15:14
Hugo Дата: Понедельник, 30.11.2020, 15:45 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3140
Репутация: 670 ±
Замечаний: 0% ±

2010, теперь уже с PQ
есть ли возможность так же с помощью макроса заменить "/" на "_" ?

[vba]
Код

Function Replace_symbols(ByVal txt As String) As String
    St$ = "~!@#$%^&*=/\|`'"""
    For i% = 1 To Len(St$)
        txt = Replace(txt, Mid(St$, i, 1), "_")
    Next
    Replace_symbols = txt
End Function

Пример использования:

' формируем путь к новому файлу
Путь = ThisWorkbook.Path & "\" & Replace_symbols(sh.Name) & _
"\" & Replace_symbols(cell) & "\" & Replace_symbols(cell.Next) & ".jpg"

[/vba]


excel@nxt.ru
webmoney: R418926282008 Z422237915069
 
Ответить
Сообщение
есть ли возможность так же с помощью макроса заменить "/" на "_" ?

[vba]
Код

Function Replace_symbols(ByVal txt As String) As String
    St$ = "~!@#$%^&*=/\|`'"""
    For i% = 1 To Len(St$)
        txt = Replace(txt, Mid(St$, i, 1), "_")
    Next
    Replace_symbols = txt
End Function

Пример использования:

' формируем путь к новому файлу
Путь = ThisWorkbook.Path & "\" & Replace_symbols(sh.Name) & _
"\" & Replace_symbols(cell) & "\" & Replace_symbols(cell.Next) & ".jpg"

[/vba]

Автор - Hugo
Дата добавления - 30.11.2020 в 15:45
bigor Дата: Понедельник, 30.11.2020, 15:53 | Сообщение № 8
Группа: Проверенные
Ранг: Обитатель
Сообщений: 365
Репутация: 66 ±
Замечаний: 0% ±

Excel 2016
"~!@#$%^&*=/\|`'"""
, а неужели "?" является допустимым
 
Ответить
Сообщение
"~!@#$%^&*=/\|`'"""
, а неужели "?" является допустимым

Автор - bigor
Дата добавления - 30.11.2020 в 15:53
Hugo Дата: Понедельник, 30.11.2020, 16:36 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3140
Репутация: 670 ±
Замечаний: 0% ±

2010, теперь уже с PQ
Никто не мешает добавить.
Вообще просили только / :)
Я просто взял в закромах первое что попалось...
Можно у мелкомягких спросить какие символы в винде запрещены, чтоб точно никого не забыть.


excel@nxt.ru
webmoney: R418926282008 Z422237915069


Сообщение отредактировал Hugo - Понедельник, 30.11.2020, 17:21
 
Ответить
СообщениеНикто не мешает добавить.
Вообще просили только / :)
Я просто взял в закромах первое что попалось...
Можно у мелкомягких спросить какие символы в винде запрещены, чтоб точно никого не забыть.

Автор - Hugo
Дата добавления - 30.11.2020 в 16:36
kot2012 Дата: Понедельник, 30.11.2020, 16:45 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
bigor, еще такой вопрос, информация при этом измениться в ячейке или в самой ячейке не измениться а только изменится имя листа для которого брались данные? И еще попутно, можно ли как-то избежать чтобы форматирование не слетало после копирования на новый лист?
 
Ответить
Сообщениеbigor, еще такой вопрос, информация при этом измениться в ячейке или в самой ячейке не измениться а только изменится имя листа для которого брались данные? И еще попутно, можно ли как-то избежать чтобы форматирование не слетало после копирования на новый лист?

Автор - kot2012
Дата добавления - 30.11.2020 в 16:45
bigor Дата: Понедельник, 30.11.2020, 17:03 | Сообщение № 11
Группа: Проверенные
Ранг: Обитатель
Сообщений: 365
Репутация: 66 ±
Замечаний: 0% ±

Excel 2016
В вариантах решения #6, #7 информация в ячейке не изменится. Про формат я не подскажу.
 
Ответить
СообщениеВ вариантах решения #6, #7 информация в ячейке не изменится. Про формат я не подскажу.

Автор - bigor
Дата добавления - 30.11.2020 в 17:03
kot2012 Дата: Понедельник, 30.11.2020, 17:14 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
bigor, дико извиняюсь pray просто всё смешалось в голове! ((( Не в той теме вопрос задал (( [vba]
Код
Sub a()
Dim Bg As Long, En As Long
Dim ws As Worksheet

Set ws = ActiveSheet
With ws
    En = .Cells(Rows.Count, 1).End(xlUp).Row
    Do Until En < 2
        Bg = En
        Do Until (.Cells(Bg, 21).Value = "Приложение")
            Bg = Bg - 1
                    
        Loop
        Sheets.Add after:=Worksheets(Sheets.Count)
        ActiveSheet.Name = Left(.Cells(Bg + 1, 21).Value, 31)
        .Range(.Rows(Bg), .Rows(En)).Copy Destination:=Rows(1)
        En = Bg - 1
    Loop
End With
End Sub
[/vba]
я про вот этот код. Тут получается что если в ячейке 21 содержаться непотребные символы, то макрос посылает соответственно. А в рабочих спецификациях как раз в названиях присутствует символ "/" соответственно чтобы сохранить новый лист нужно менять в самой ячейки "/" к примеру на "_" чтобы макрос отработал. Хорошо когда 6 спецификация а если их 200 ((( Суть задачи в следующем, как сделать так чтобы имя листа было с "_" и при этом информация в ячейки не менялась. Надеюсь доступно описал :)
 
Ответить
Сообщениеbigor, дико извиняюсь pray просто всё смешалось в голове! ((( Не в той теме вопрос задал (( [vba]
Код
Sub a()
Dim Bg As Long, En As Long
Dim ws As Worksheet

Set ws = ActiveSheet
With ws
    En = .Cells(Rows.Count, 1).End(xlUp).Row
    Do Until En < 2
        Bg = En
        Do Until (.Cells(Bg, 21).Value = "Приложение")
            Bg = Bg - 1
                    
        Loop
        Sheets.Add after:=Worksheets(Sheets.Count)
        ActiveSheet.Name = Left(.Cells(Bg + 1, 21).Value, 31)
        .Range(.Rows(Bg), .Rows(En)).Copy Destination:=Rows(1)
        En = Bg - 1
    Loop
End With
End Sub
[/vba]
я про вот этот код. Тут получается что если в ячейке 21 содержаться непотребные символы, то макрос посылает соответственно. А в рабочих спецификациях как раз в названиях присутствует символ "/" соответственно чтобы сохранить новый лист нужно менять в самой ячейки "/" к примеру на "_" чтобы макрос отработал. Хорошо когда 6 спецификация а если их 200 ((( Суть задачи в следующем, как сделать так чтобы имя листа было с "_" и при этом информация в ячейки не менялась. Надеюсь доступно описал :)

Автор - kot2012
Дата добавления - 30.11.2020 в 17:14
Hugo Дата: Понедельник, 30.11.2020, 17:24 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3140
Репутация: 670 ±
Замечаний: 0% ±

2010, теперь уже с PQ
Не нужно менять в самой ячейке, делайте с именем листа аналогично как с именем файла. Нужно тоже погуглить какие тут символы запрещены.
Там правда пара запрещённых слов кажется было. но надеюсь не попадутся :)
Пока нагуглил
[] */\?


excel@nxt.ru
webmoney: R418926282008 Z422237915069


Сообщение отредактировал Hugo - Понедельник, 30.11.2020, 17:27
 
Ответить
СообщениеНе нужно менять в самой ячейке, делайте с именем листа аналогично как с именем файла. Нужно тоже погуглить какие тут символы запрещены.
Там правда пара запрещённых слов кажется было. но надеюсь не попадутся :)
Пока нагуглил
[] */\?

Автор - Hugo
Дата добавления - 30.11.2020 в 17:24
kot2012 Дата: Понедельник, 30.11.2020, 17:39 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Hugo, аналогично это как? Я очень слабо разбираюсь в макросах, от слова - ни как ) подскажите пожалуйста, мне необходимо добавить в код из поста #12 [vba]
Код
newName = Replace(MySheet.Cells(2, 21).Value & ".xlsx", "/", "_")
[/vba] или же то что вы предложили?
 
Ответить
СообщениеHugo, аналогично это как? Я очень слабо разбираюсь в макросах, от слова - ни как ) подскажите пожалуйста, мне необходимо добавить в код из поста #12 [vba]
Код
newName = Replace(MySheet.Cells(2, 21).Value & ".xlsx", "/", "_")
[/vba] или же то что вы предложили?

Автор - kot2012
Дата добавления - 30.11.2020 в 17:39
Hugo Дата: Понедельник, 30.11.2020, 18:22 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3140
Репутация: 670 ±
Замечаний: 0% ±

2010, теперь уже с PQ
[vba]
Код

ActiveSheet.Name = Replace_symbols(Left(.Cells(Bg + 1, 21).Value, 31))
[/vba]
Ну и код функции должен быть доступен.


excel@nxt.ru
webmoney: R418926282008 Z422237915069


Сообщение отредактировал Hugo - Понедельник, 30.11.2020, 18:22
 
Ответить
Сообщение[vba]
Код

ActiveSheet.Name = Replace_symbols(Left(.Cells(Bg + 1, 21).Value, 31))
[/vba]
Ну и код функции должен быть доступен.

Автор - Hugo
Дата добавления - 30.11.2020 в 18:22
kot2012 Дата: Понедельник, 30.11.2020, 19:03 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Hugo, разобрался! да уж тяжко быть деревянным, хорошо что есть специалисты, Спасибо вам большое!!! Если я вас еще в край не достал, может подскажете как сделать так чтобы листы копировались с сохранением форматирования средствами VBA? Нашел на форуме только это: [vba]
Код
.PasteSpecial Paste:=xlPasteFormats
        .PasteSpecial Paste:=xlPasteComments
        .PasteSpecial Paste:=xlPasteValidation
[/vba] а куда это нужно вставить понятия не имею ))) не поможете?
 
Ответить
СообщениеHugo, разобрался! да уж тяжко быть деревянным, хорошо что есть специалисты, Спасибо вам большое!!! Если я вас еще в край не достал, может подскажете как сделать так чтобы листы копировались с сохранением форматирования средствами VBA? Нашел на форуме только это: [vba]
Код
.PasteSpecial Paste:=xlPasteFormats
        .PasteSpecial Paste:=xlPasteComments
        .PasteSpecial Paste:=xlPasteValidation
[/vba] а куда это нужно вставить понятия не имею ))) не поможете?

Автор - kot2012
Дата добавления - 30.11.2020 в 19:03
Hugo Дата: Понедельник, 30.11.2020, 19:34 | Сообщение № 17
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3140
Репутация: 670 ±
Замечаний: 0% ±

2010, теперь уже с PQ
Так если лист сохранять отдельно от книги - с его форматированием ничего не случится.


excel@nxt.ru
webmoney: R418926282008 Z422237915069
 
Ответить
СообщениеТак если лист сохранять отдельно от книги - с его форматированием ничего не случится.

Автор - Hugo
Дата добавления - 30.11.2020 в 19:34
kot2012 Дата: Понедельник, 30.11.2020, 20:16 | Сообщение № 18
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Hugo, смотрите какая ситуация, попытаюсь в кратце объяснить, что необходимо в конечном итоге получить и в связи с чем все эти пляски с макросами. Приходит эксел файл, в неё на одном листе идут спецификации, их нужно разбить на отдельные лист/файлы, с сохранением названия из ячейки в которой прописано имя спецификации. Как то так. Если можно сразу сохранять в файл без разбивания одного листа на несколько а потом сохранять каждый лист как отдельный файл, буду признателен если подскажите как это можно реализовать средствами vba
 
Ответить
СообщениеHugo, смотрите какая ситуация, попытаюсь в кратце объяснить, что необходимо в конечном итоге получить и в связи с чем все эти пляски с макросами. Приходит эксел файл, в неё на одном листе идут спецификации, их нужно разбить на отдельные лист/файлы, с сохранением названия из ячейки в которой прописано имя спецификации. Как то так. Если можно сразу сохранять в файл без разбивания одного листа на несколько а потом сохранять каждый лист как отдельный файл, буду признателен если подскажите как это можно реализовать средствами vba

Автор - kot2012
Дата добавления - 30.11.2020 в 20:16
Hugo Дата: Понедельник, 30.11.2020, 20:41 | Сообщение № 19
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3140
Репутация: 670 ±
Замечаний: 0% ±

2010, теперь уже с PQ
Листы ведь уже сделали, или это не устраивает?
Можно как вариант, но не факт что лучше - копировать первый лист, удалять из него лишние строки, сохранять сразу не в книгу, а в файл.


excel@nxt.ru
webmoney: R418926282008 Z422237915069
 
Ответить
СообщениеЛисты ведь уже сделали, или это не устраивает?
Можно как вариант, но не факт что лучше - копировать первый лист, удалять из него лишние строки, сохранять сразу не в книгу, а в файл.

Автор - Hugo
Дата добавления - 30.11.2020 в 20:41
kot2012 Дата: Понедельник, 30.11.2020, 20:46 | Сообщение № 20
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Hugo, да разнести листы получилось, но при разбивки на них слетает форматирование от исходного, что не есть гуд. Потом приходится каждый лист руками подгонять под первый с которого их разносили
 
Ответить
СообщениеHugo, да разнести листы получилось, но при разбивки на них слетает форматирование от исходного, что не есть гуд. Потом приходится каждый лист руками подгонять под первый с которого их разносили

Автор - kot2012
Дата добавления - 30.11.2020 в 20:46
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сохранение листов каждый в отдельный файл с именем из ячейки (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс цитирования Jobsora jooble
© 2010-2021 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!