Wiele razy w miesiącu potrzebuję skoroszytu programu Excel do tworzenia wielu kopii samego siebie. Zwykle popadam w błąd logiki, otwierając * skoroszyt * i zaczynam pisać kod, aby zapętlić listę i użyć polecenia Plik, Zapisz jako, aby zapisać kopię skoroszytu.
Oto schemat blokowy:
Czy widzisz problem powyżej? Makro działa w programie WorkbookA. Kiedy zapiszę jako plik jako RegionEast.xlsx, a następnie zamknę RegionEast.xlsx, makro przestanie działać.
Zwykle zagłębiam się w pseudokod, zanim zobaczę problem.
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 SubGdybym myślał z wyprzedzeniem, stworzyłbym rozwiązanie z dwoma skoroszytami. Umieść wszystkie makra w folderze WorkbookA. Umieść wszystkie dane w WorkbookB. Miej WorkbookA wielokrotnie otwierany WorkbookB, zmień dane, Zapisz jako, Zamknij.
W miarę jak jestem starszy i bardziej zepsuty, stwierdzam, że jestem mniej w nastroju na nieco bardziej skomplikowane. Zwłaszcza, że większość kodu działa dla oryginalnego polecenia SaveAs.
Dzisiejszy artykuł dotyczy niesamowitej metody VBA dla SaveAsCopy. To polecenie spowoduje, że WorkbookA będzie otwarte i nazwane WorkbookA. Makro może nadal działać. Ale zapisze aktualny stan skoroszytu do nowego zamkniętego skoroszytu o nazwie WorkbookB.
To pozwala mi wrócić do pierwotnego schematu blokowego:
Jednak odkryłem jeden problem z SaveAsCopy. Kiedy robię ThisWorkbook.SaveAs, mogę wybrać, czy chcę zapisać jako XLSX czy XLSM. Jeśli potrzebuję makr, aby były dostępne w nowym skoroszycie, używam XLSM. W przeciwnym razie używam XLSX i makra znikną.
Niestety, jeśli jesteś w skoroszycie XLSM, nie możesz pomyślnie .SaveAsCopy i zmienić na XLSX. Kod zadziała. Ale wynikowy skoroszyt nie zostanie otwarty, ponieważ program Excel wykryje niezgodność między typem pliku a rozszerzeniem pliku.
Moje rozwiązanie to SaveAsCopy jako XLSM. Po zapisaniu kopii mogę otworzyć skoroszyt (tworząc dwie kopie skoroszytu w pamięci), a następnie SaveAs 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 SubObejrzyj wideo
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2213: Zapisz jako używając VBA, ale zachowaj oryginał otwarty.
Hej, witaj ponownie w netcastie. Jestem Bill Jelen. Cóż, być może zauważyłeś, że ostatnio oferowałem pobranie każdego podcastu, ponieważ wiele osób prosiło mnie o to, więc starałem się ułatwić życie, jak to tylko możliwe. Cały cel polegał na zapisaniu kopii, którą możesz pobrać, ale nie chciałem tam dodatkowych rzeczy - wiesz, rzeczy, które są do mojego własnego użytku wewnętrznego - tam, więc chciałem się tego pozbyć.
I wiesz, powiedzmy, że miałem sytuację, w której musiałem napisać 12 zeszytów ćwiczeń, prawda? Każdy z innym produktem. Więc mam zamiar zapętlić te produkty i napiszę je tam do A2, a następnie zapiszę skoroszyt i może posprzątam trochę rzeczy. W porządku. Więc moje pierwsze przejście to takie makro, dobrze? Zatem definiujemy bieżący skoroszyt - Arkusze robocze („Dane”), Arkusze robocze („Raport”), aby je znaleźć - a następnie określamy, ile wierszy danych mamy dzisiaj, będziemy przechodzić od wiersza 2 do ostatni wiersz, skopiuj produkt ze skoroszytu danych do skoroszytu raportu.
W porządku, a teraz będę miał kłopoty. Więc nowy skoroszyt będzie się nazywał „C: aaa ”, a następnie Apple.xlsx, a ja zamierzam zapisać jako, wiesz, z Apple.xlsx i zmienić na XML - otwórz skoroszyt xml- - co spowoduje usunięcie makr. W porządku. Ale teraz chciałbym zamknąć ten skoroszyt, ale niestety, kiedy robisz Zapisz jako - zobacz teraz, jestem w podcastie 2013 - kiedy robię Zapisz jako po tym punkcie w kodzie, nie jestem już będzie w Podcast 2013; Będę w Apple.xlsx. W porządku? Więc teraz, jeśli chcę zacząć usuwać rzeczy, zamierzam usunąć je z kopii, ale kiedy ją zamknę, cóż, nie mogę wrócić do oryginalnego pliku. W porządku? I to makro - właściwie to moja głowa zaraz eksploduje, próbując dowiedzieć się, czy pętla będzie nadal działać, czy nie,dobrze? Więc myślę, że Zapisz jako to zły sposób, aby przejść tutaj.
Cóż, właściwie, poczekaj. Moglibyśmy iść dwiema ścieżkami: Po pierwsze, mógłbym mieć inny skoroszyt, który otwiera Podcast 2213, robi wszystko, a następnie Zapisuje tak, jak z nową nazwą, albo mam zamiar pójść tą drogą, w porządku, i to jest metoda, którą skończyłem używając - w porządku, zdefiniujemy ten skoroszyt, ale także nowy skoroszyt. Dobrze. I tutaj na dole wszystko wygląda mniej więcej tak samo, dopóki nie dojdziemy do punktu, w którym zamierzałem zrobić WBT. Sprawdź to: SaveCopyAs - o ile wiem, to nie istnieje, o ile wiem, w zwykłym Excelu… to jest tylko VBA. SaveCopyAs mówi: „Hej, spójrz, jesteśmy w pliku o nazwie 2213 i chcę, abyś wziął ten plik 2213 w jego obecnym stanie, zapisz go na dysku i zamknij”. Pozostaw oryginalny plik otwarty - 2213 pozostanie otwarty - ale teraz mamy na dysku nowy plik o nazwie Apple.xlsm. Właściwie na początkumam zamiar nazwać to DeleteMe.xlsm. W porządku. Ale tworzy identyczną kopię i zachowuje oryginalny plik - plik, w którym działa makro - otwarty i to jest ważna część, prawda? Więc teraz, gdy mam tam DeleteMe, otwieram go, przypisuję do WBN, robię to, co muszę, pozbywam się wszystkich dodatkowych arkuszy - wiem, co mam. Zauważ, że zanim usuniesz arkusze, chcesz zrobić DisplayAlerts = False, w przeciwnym razie będzie cię pytać „Hej, nie odzyskasz arkusza”. Rozumiem. I wreszcie tutaj, wybierz pierwszy arkusz roboczy FN będzie Apple.xlsx, a następnie możemy zrobić WBN.SaveAs Apple, jako Open XMLWorkbook. Brak makr. A potem Zamknij - piękną rzeczą w Zamknięciu jest to, że wróciłem do tego zeszytu, 2213.Ale tworzy identyczną kopię i zachowuje oryginalny plik - plik, w którym działa makro - otwarty, i to jest ważna część, prawda? Więc teraz, gdy mam tam DeleteMe, otwieram go, przypisuję do WBN, robię to, co muszę, pozbywam się wszystkich dodatkowych arkuszy - wiem, co mam. Zauważ, że zanim usuniesz arkusze, chcesz zrobić DisplayAlerts = False, w przeciwnym razie będzie cię pytać „Hej, nie odzyskasz arkusza”. Rozumiem. I wreszcie tutaj, wybierz pierwszy arkusz roboczy FN będzie Apple.xlsx, a następnie możemy zrobić WBN.SaveAs Apple, jako Open XMLWorkbook. Brak makr. A potem Zamknij - piękną rzeczą w Zamknięciu jest to, że wróciłem do tego zeszytu, 2213.Ale tworzy identyczną kopię i zachowuje oryginalny plik - plik, w którym działa makro - otwarty, i to jest ważna część, prawda? Więc teraz, gdy mam tam DeleteMe, otwieram go, przypisuję do WBN, robię to, co muszę, pozbywam się wszystkich dodatkowych arkuszy - wiem, co mam. Zauważ, że zanim usuniesz arkusze, chcesz zrobić DisplayAlerts = False, w przeciwnym razie będzie cię pytać „Hej, nie odzyskasz arkusza”. Rozumiem. I wreszcie tutaj, wybierz pierwszy arkusz roboczy FN będzie Apple.xlsx, a następnie możemy zrobić WBN.SaveAs Apple, jako Open XMLWorkbook. Brak makr. A potem Zamknij - piękną rzeczą w Zamknięciu jest to, że wróciłem do tego zeszytu, 2213.dobrze? Więc teraz, gdy mam tam DeleteMe, otwieram go, przypisuję do WBN, robię to, co muszę, pozbywam się wszystkich dodatkowych arkuszy - wiem, co mam. Zauważ, że zanim usuniesz arkusze, chcesz zrobić DisplayAlerts = False, w przeciwnym razie będzie cię pytać „Hej, nie odzyskasz arkusza”. Rozumiem. I wreszcie tutaj, wybierz pierwszy arkusz roboczy FN będzie Apple.xlsx, a następnie możemy zrobić WBN.SaveAs Apple, jako Open XMLWorkbook. Brak makr. A potem Zamknij - piękną rzeczą w Zamknięciu jest to, że wróciłem do tego zeszytu, 2213.dobrze? Więc teraz, gdy mam tam DeleteMe, otwieram go, przypisuję do WBN, robię to, co muszę, pozbywam się wszystkich dodatkowych arkuszy - wiem, co mam. Zauważ, że zanim usuniesz arkusze, chcesz zrobić DisplayAlerts = False, w przeciwnym razie będzie cię pytać „Hej, nie odzyskasz arkusza”. Rozumiem. I wreszcie tutaj, wybierz pierwszy arkusz roboczy FN będzie Apple.xlsx, a następnie możemy zrobić WBN.SaveAs Apple, jako Open XMLWorkbook. Brak makr. A potem Zamknij - piękną rzeczą w Zamknięciu jest to, że wróciłem do tego zeszytu, 2213.nie otrzymam arkusza z powrotem. ”Rozumiem. I wreszcie tutaj, wybierz pierwszy arkusz roboczy FN będzie Apple.xlsx, a następnie możemy zrobić WBN.SaveJak Apple, jako Open XMLWorkbook. Żadnych makr A potem Close - najpiękniejszą rzeczą w Close jest to, że jestem z powrotem w tym skoroszycie, 2213.nie otrzymam arkusza z powrotem. ”Rozumiem. I w końcu tutaj, wybierz pierwszy arkusz roboczy FN będzie Apple.xlsx, a następnie możemy zrobić WBN.SaveJak Apple, jako Open XMLWorkbook. Żadnych makr A potem Close - najpiękniejszą rzeczą w Close jest to, że jestem z powrotem w tym skoroszycie, 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.
Podsumowanie od dzisiaj: Chcesz, aby VBA napisał kilka kopii bieżącego skoroszytu; Zapisz jako powoduje problemy, ponieważ oryginalny skoroszyt nie jest już otwarty; zamiast tego używasz .SaveAsCopy, aby zapisać kopię skoroszytu. Jeśli chcesz pobrać skoroszyt z dzisiejszego wideo, w tym makro, odwiedź adres URL w opisie YouTube.
Chcę, żebyś wpadł. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobierz plik Excel
Aby pobrać plik Excela: save-as-keep-original-open.xlsm
Myśl dnia Excela
Poprosiłem moich znajomych z programu Excel Master o radę dotyczącą programu Excel. Dzisiejsza myśl do rozważenia:
„Daty to liczby, a nie słowa”.
Duane Aubin
