Много раз в месяц мне нужна книга Excel для создания множества своих копий. Обычно я сталкиваюсь с логической ошибкой, открывая * книгу * и начинаю писать код, чтобы просмотреть список и использовать «Файл», «Сохранить как», чтобы сохранить копию книги.
Вот блок-схема:
Вы видите проблему выше? Макрос запущен в WorkbookA. Когда я сохраняю файл как RegionEast.xlsx, а затем закрываю RegionEast.xlsx, макрос перестанет работать.
Обычно я глубоко погружаюсь в псевдокод, прежде чем увижу проблему.
Sub MakeCopies() Dim WBT As Workbook Dim WSD As Worksheet Dim WSR As Worksheet Set WBT = ThisWorkbook Set WSD = WBT.Worksheets("Data") Set WSR = WBT.Worksheets("Report") FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To FinalRow ' Copy this product to WSR.B2 WSR.Cells(2, 1).Value = WSD.Cells(i, 1).Value ' Save this workbook with a new name NewFN = "C:aaa " & WSD.Cells(i, 1).Value & ".xlsx" ' Save As a file type without macros WBT.SaveAs Filename:=NewFN, FileFormat:=xlOpenXMLWorkbook ' Close the new workbook ' But that won't work because I need this workbook to stay open End SubЕсли бы я думал наперед, я бы создал решение для двух книг. Поместите все макросы в WorkbookA. Поместите все данные в WorkbookB. Попросите WorkbookA многократно открывать WorkbookB, изменять данные, SaveAs, Close.
По мере того, как я становлюсь старше и капризнее, я обнаруживаю, что у меня все меньше настроения заниматься чуть более сложным. Тем более, что у меня большая часть кода работает с исходной командой SaveAs.
Сегодняшняя статья посвящена замечательному методу VBA для SaveAsCopy. Эта команда будет держать WorkbookA открытой и называться WorkbookA. Макрос может продолжать работать. Но он запишет текущее состояние книги в новую закрытую книгу под названием WorkbookB.
Это позволяет мне вернуться к исходной блок-схеме:
Однако я обнаружил одну проблему с SaveAsCopy. Когда я выполняю ThisWorkbook.SaveAs, я могу выбрать, хочу ли я сохранить как XLSX или XLSM. Если мне нужно, чтобы макросы были доступны в новой книге, я использую XLSM. В противном случае я использую XLSX и макросы пропадут.
К сожалению, если вы находитесь в книге XLSM, вы не можете успешно .SaveAsCopy и перейти на XLSX. Код будет работать. Но полученная книга не откроется, поскольку Excel обнаружит несоответствие между типом файла и расширением файла.
Мое решение - SaveAsCopy как XLSM. После сохранения копии я могу открыть книгу (создав две копии книги в памяти), а затем сохранить как XLSX.
Sub MakeCopies() Dim WBT As Workbook Dim WBN As Workbook Dim WSD As Worksheet Dim WSR As Worksheet Set WBT = ThisWorkbook Set WSD = WBT.Worksheets("Data") Set WSR = WBT.Worksheets("Report") FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To FinalRow ' Copy this product to WSR.B2 WSR.Cells(2, 1).Value = WSD.Cells(i, 1).Value ' Save this workbook with a new name FN = "C:aaa " & WSD.Cells(i, 1).Value & ".xlsx" ' Save a Temp Copy of this workbook NewFN = "C:aaaDeleteMe.xlsm" ' Delete any previous copy left over from before On Error Resume Next Kill (NewFN) On Error GoTo 0 ' **************************************** ' Use SaveCopyAs instead of SaveAs WBT.SaveCopyAs Filename:=NewFN ' Open the workbook we just saved Set WBN = Workbooks.Open(NewFN) ' Delete the worksheets that we don't need Application.DisplayAlerts = False For Each WS In WBN.Worksheets Select Case WS.Name Case "BuyTheBook", "Info", "Form", "Template", "Article", "NotesForApp", "Data" WS.Delete End Select Next WS Application.DisplayAlerts = True NewFN = FN WBN.Worksheets(1).Select On Error Resume Next Kill (NewFN) On Error GoTo 0 Application.DisplayAlerts = False WBN.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook Application.DisplayAlerts = True WBN.Close False ' Delete the temporary copy NewFN = "C:aaaDeleteMe.xlsm" On Error Resume Next Kill (NewFN) On Error GoTo 0 End SubСмотреть видео
Стенограмма видео
Изучите Excel из подкаста, эпизод 2213: «Сохранить как с помощью VBA, но оставить оригинал открытым».
Привет, добро пожаловать обратно в сетевую трансляцию. Я Билл Джелен. Что ж, вы могли заметить, что я недавно предлагал скачать каждый подкаст, потому что многие люди просили меня об этом, и поэтому я пытался сделать жизнь как можно проще. И вся цель заключалась в том, чтобы сохранить копию, которую вы можете скачать, но мне не нужны были лишние вещи - вы знаете, материалы, предназначенные для моего внутреннего использования - там, поэтому я хотел от них избавиться.
И, знаете, допустим, у меня была ситуация, когда мне пришлось написать 12 рабочих тетрадей, верно? У каждого свой продукт. Итак, я собираюсь пройтись по этим продуктам, и я собираюсь записать их в A2, а затем сохранить книгу и, возможно, очистить некоторые вещи. Хорошо. Итак, мой первый проход - это такой макрос, хорошо? Итак, мы определяем текущую книгу - рабочие листы («Данные), рабочие листы (« Отчет »), чтобы найти их - а затем выясняем, сколько строк данных у нас есть сегодня, мы собираемся пройти цикл от строки 2 до В последней строке скопируйте продукт из книги данных в книгу отчетов.
Хорошо, и вот здесь у меня проблемы. Итак, новая книга будет называться «C: aaa », а затем Apple.xlsx, и я собираюсь сохранить как a, вы знаете, с Apple.xlsx и перейти на XML - открыть книгу xml- - который удалит макросы. Хорошо. Но сейчас я бы хотел закрыть эту книгу, но, к сожалению, когда вы выполняете «Сохранить как» - смотрите прямо сейчас, я нахожусь в подкасте 2013 года - когда я выполняю «Сохранить как» после этого момента в коде, я не больше не будет в Podcast 2013; Я буду в Apple.xlsx. Хорошо? Итак, теперь, если я хочу начать удалять материал, я собираюсь удалить его в копии, но когда я закрываю копию, я не могу вернуться к исходному файлу. Хорошо? И этот макрос - на самом деле, моя голова вот-вот взорвется, пытаясь понять, будет ли цикл работать или нет,правильно? Поэтому я думаю, что «Сохранить как» - неправильный путь.
Ну вообще-то, погоди. Мы могли бы пойти двумя путями: во-первых, у меня может быть другая книга, которая откроет подкаст 2213, сделает все, а затем сохранит как с новым именем, или я пойду этим путем, хорошо, и это метод, которым я закончил с помощью - хорошо, и мы собираемся определить эту книгу, но затем также новую книгу. Правильно. И здесь все примерно так же, пока мы не дойдем до того момента, когда я собирался сделать WBT.SaveAs. Проверьте это: SaveCopyAs - сейчас этого не существует, насколько я могу судить, в обычном Excel … это только VBA. SaveCopyAs говорит: «Послушайте, мы находимся в файле с именем 2213, и я хочу, чтобы вы взяли этот файл 2213 в его текущем состоянии, сохранили его на диск и закрыли». Не закрывайте исходный файл - 2213 остается открытым, - но теперь у нас есть новый файл на диске под названием Apple.xlsm. На самом деле, сначала яm просто назову его DeleteMe.xlsm. Хорошо. Но он создает идентичную копию и сохраняет исходный файл - файл, в котором запущен макрос - открытым, и это важная часть, верно? Итак, теперь, когда у меня есть DeleteMe, я открываю его, назначаю WBN, делаю то, что мне нужно, избавляюсь от всех лишних листов - я знаю, что у меня есть. Обратите внимание: перед удалением листов вы хотите сделать DisplayAlerts = False, иначе он будет спрашивать вас: «Эй, вы не собираетесь вернуть лист». Я понял. И затем, наконец, выберите первый рабочий лист FN, который будет Apple.xlsx, а затем мы сможем выполнить WBN.SaveAs Apple как Open XMLWorkbook. Никаких макросов. А затем Клоуз - самое прекрасное в Клоузе то, что я вернулся в эту рабочую тетрадь, 2213.Но он создает идентичную копию и сохраняет исходный файл - файл, в котором запущен макрос - открытым, и это важная часть, верно? Итак, теперь, когда у меня есть DeleteMe, я открываю его, назначаю WBN, делаю то, что мне нужно, избавляюсь от всех лишних листов - я знаю, что у меня есть. Обратите внимание: перед удалением листов вы хотите сделать DisplayAlerts = False, иначе он будет спрашивать вас: «Эй, вы не собираетесь вернуть лист». Я понял. И затем, наконец, здесь выберите первый рабочий лист FN, который будет Apple.xlsx, а затем мы можем сделать WBN.SaveAs Apple как Open XMLWorkbook. Никаких макросов. А затем Клоуз - самое прекрасное в Клоузе то, что я вернулся в эту рабочую тетрадь, 2213.Но он создает идентичную копию и сохраняет исходный файл - файл, в котором запущен макрос - открытым, и это важная часть, верно? Итак, теперь, когда у меня есть DeleteMe, я открываю его, назначаю WBN, делаю то, что мне нужно, избавляюсь от всех лишних листов - я знаю, что у меня есть. Обратите внимание: перед удалением листов вы хотите сделать DisplayAlerts = False, иначе он будет спрашивать вас: «Эй, вы не собираетесь вернуть лист». Я понял. И затем, наконец, здесь выберите первый рабочий лист FN, который будет Apple.xlsx, а затем мы сможем выполнить WBN.SaveAs Apple как Open XMLWorkbook. Никаких макросов. А затем Клоуз - самое прекрасное в Клоузе то, что я вернулся в эту рабочую тетрадь, 2213.правильно? Итак, теперь, когда у меня есть DeleteMe, я открываю его, назначаю его WBN, делаю то, что мне нужно, избавляюсь от всех лишних листов - я знаю, что у меня есть. Обратите внимание: перед удалением листов вы хотите сделать DisplayAlerts = False, иначе он будет спрашивать вас: «Эй, вы не собираетесь вернуть лист». Я понял. И затем, наконец, выберите первый рабочий лист FN, который будет Apple.xlsx, а затем мы сможем выполнить WBN.SaveAs Apple как Open XMLWorkbook. Никаких макросов. А затем Клоуз - самое прекрасное в Клоузе то, что я вернулся в эту рабочую тетрадь, 2213.правильно? Итак, теперь, когда у меня есть DeleteMe, я открываю его, назначаю WBN, делаю то, что мне нужно, избавляюсь от всех лишних листов - я знаю, что у меня есть. Обратите внимание: перед удалением листов вы хотите сделать DisplayAlerts = False, иначе он будет спрашивать вас: «Эй, вы не собираетесь вернуть лист». Я понял. И затем, наконец, здесь выберите первый рабочий лист FN, который будет Apple.xlsx, а затем мы можем сделать WBN.SaveAs Apple как Open XMLWorkbook. Никаких макросов. А затем Клоуз - самое прекрасное в Клоузе то, что я вернулся в эту рабочую тетрадь, 2213.я не собираюсь вернуть лист ". Я понял. И затем, наконец, здесь выберите первый лист FN, который будет Apple.xlsx, и затем мы можем сделать WBN.SaveAs Apple, как Open XMLWorkbook. Никаких макросов А потом Клоуз - самое прекрасное в Клоузе то, что я вернулся в эту рабочую тетрадь, 2213.я не собираюсь вернуть лист ". Я понял. И затем, наконец, здесь выберите первый лист FN, который будет Apple.xlsx, и затем мы можем сделать WBN.SaveAs Apple, как Open XMLWorkbook. Никаких макросов А потом Клоуз - самое прекрасное в Клоузе - это то, что я вернулся к этой книге, 2213.
Alright, it's been working really, really well, and the key to this whole thing is SaveCopyAs-- SaveCopyAs. So, to me-- well that's been around for a long time-- I never used it, and now I realize there's probably been a lot of times in the past where I should have used it. And maybe, you know, you should be using it or consider using it as well.
Okay, I forgot to mention one thing: the important thing is, with Save As Copy-- Save As Copy-- so when I do the Save As Copy, if I try to change to an xslx file type, I cannot change the file type here just by changing the extension in NewFN and when trying to open that subsequent workbook, they'll detect that it has macros, and it has the wrong extension, and it will yell at you. Right? So, you have to save it as xlsm and then later come back, reopen it, and save it as xlsx. But that all works with this macro.
So, hey, when I'm updating this book, Excel 2016, this summer, for Excel 2019, I'll make sure to include this tip. I think it's a useful tip to SaveCopyAs.
Итоги сегодняшнего дня: вы хотите, чтобы VBA написал несколько копий текущей книги; Сохранить как вызывает проблемы, поскольку исходная книга больше не открыта; вместо этого вы используете .SaveAsCopy, чтобы сохранить копию книги. Если вы хотите загрузить книгу из сегодняшнего видео, включая макрос, посетите URL-адрес в описании YouTube.
Я хочу, чтобы вы заглянули, увидимся в следующий раз на другой сетевой трансляции от.
Скачать файл Excel
Чтобы загрузить файл Excel: save-as-keep-original-open.xlsm
Идея дня в Excel
Я попросил совета у моих друзей-мастеров Excel по поводу Excel. Сегодняшняя мысль задуматься:
«Даты - это числа, а не слова».
Дуэйн Обин
