Există de multe ori într-o lună când am nevoie de un registru de lucru Excel pentru a crea multe copii ale sale. De obicei, intru într-o eroare logică deschizând * registrul de lucru * și încep să scriu cod pentru a parcurge o listă și să folosesc File, Save As pentru a salva o copie a registrului de lucru.
Iată diagrama:
Vedeți problema de mai sus? Macro rulează în WorkbookA. Când Salvează ca fișier ca RegionEast.xlsx și apoi închid RegionEast.xlsx, macro-ul va înceta să ruleze.
De obicei sunt adânc în pseudocod înainte să văd problema.
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 SubDacă m-aș fi gândit înainte, aș fi creat o soluție cu două cărți de lucru. Puneți toate macrocomenzile în WorkbookA. Puneți toate datele în WorkbookB. Solicitați WorkbookA în mod repetat Workbook B, modificați datele, SaveAs, Close.
Pe măsură ce îmbătrânesc și mai ciudat, constat că sunt mai puțin dispus să merg cu ceva mai complicat. Mai ales că am cea mai mare parte a codului care funcționează pentru comanda SaveAs originală.
Articolul de astăzi este despre metoda minunată VBA pentru SaveAsCopy. Această comandă va menține WorkbookA deschis și numit WorkbookA. Macro-ul poate continua să ruleze. Dar va scrie starea actuală a registrului de lucru într-un nou registru de lucru închis numit WorkbookB.
Acest lucru îmi permite să mă întorc la diagrama originală:
Cu toate acestea, am descoperit o problemă cu SaveAsCopy. Când fac ThisWorkbook.SaveAs, pot alege dacă vreau să salvez ca XLSX sau XLSM. Dacă am nevoie ca macro-urile să fie disponibile în noul registru de lucru, atunci folosesc XLSM. În caz contrar, folosesc XLSX și macro-urile vor dispărea.
Din păcate, dacă vă aflați într-un registru de lucru XLSM, nu puteți reuși .SaveAsCopy și treceți la XLSX. Codul va funcționa. Dar registrul de lucru rezultat nu se va deschide deoarece Excel detectează o potrivire greșită între tipul de fișier și extensia de fișier.
Soluția mea este să SaveAsCopy ca XLSM. Odată ce copia este salvată, pot deschide registrul de lucru (creând două copii ale registrului de lucru în memorie) și apoi 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 SubUrmăriți videoclipul
Transcriere video
Aflați Excel din Podcast, episodul 2213: Salvați ca folosind VBA, dar păstrați originalul deschis.
Hei, bine ai revenit pe netcast. Eu sunt Bill Jelen. Ei bine, s-ar putea să fi observat că am oferit descărcarea fiecărui podcast recent pentru că mulți oameni mi-au cerut acest lucru și așa că am încercat să fac viața cât mai ușoară posibil. Și întregul obiectiv era să salvez o copie pe care o poți descărca, dar nu am vrut lucrurile suplimentare - știi, lucrurile care sunt pentru uzul meu intern - acolo, așa că am vrut să scap de asta.
Și, știi, să spunem că am avut o situație în care trebuia să scriu 12 cărți de lucru, nu? Fiecare cu un produs diferit. Deci, voi face o buclă prin aceste produse și o voi scrie acolo în A2 și apoi voi salva registrul de lucru și poate curăț ceva. Bine. Deci, prima mea trecere de aici este o macro de acest gen, bine? Deci, definim registrul de lucru curent - Foi de lucru („Date), Foi de lucru („ Raport ”) pentru a le găsi - și apoi ne dăm seama câte rânduri de date avem astăzi, vom face o buclă de la rândul 2 până la ultimul rând, copiați produsul din registrul de lucru al datelor în registrul de lucru al raportului.
Bine, și acum iată că sunt pe punctul de a intra în necazuri. Deci, noul registru de lucru va fi numit „C: aaa ” și apoi Apple.xlsx, iar eu voi salva ca, știi, cu Apple.xlsx și voi schimba în XML - deschide registrul de lucru xml- - care va elimina macro-urile. Bine. Dar acum aș dori să închid acel registru de lucru, dar, din păcate, când faceți o Salvare ca - vezi chiar acum, sunt în podcast 2013 - când fac o Salvare ca după acel punct din cod, nu sunt va mai fi în Podcast 2013; Voi fi în Apple.xlsx. Bine? Deci, acum, dacă vreau să încep să șterg lucruri, o voi șterge în copie, dar când închid copia, ei bine, nu pot să revin la fișierul original. Bine? Și această macro - de fapt, capul meu este pe punctul de a exploda încercând să-mi dau seama dacă bucla va funcționa sau nu va funcționa,dreapta? Deci, cred că Salvați ca este calea greșită de a merge aici.
Ei bine, de fapt, stai. Am putea merge pe două rute: În primul rând, aș putea avea un alt registru de lucru care deschide Podcast 2213, face lucrurile și apoi Salvează ca și cu noul nume, sau voi merge pe acest drum, bine, și aceasta este metoda pe care am ajuns folosind - bine, și vom defini acest registru de lucru, dar apoi și un nou registru de lucru. Dreapta. Și totul este cam la fel aici jos până ajungem la punctul în care eram pe punctul de a face WBT.SaveAs. Verificați acest lucru: SaveCopyAs - acum, acest lucru nu există, din câte îmi dau seama, în Excel obișnuit … acesta este doar VBA. SaveCopyAs spune: „Hei, uite că suntem într-un fișier numit 2213 și vreau să iei fișierul 2213 în starea actuală și să-l salvezi pe disc și să-l închizi”. Păstrați fișierul original deschis - 2213 rămâne deschis - dar acum avem un fișier nou pe disc numit Apple.xlsm. De fapt, la început,O voi numi DeleteMe.xlsm. Bine. Dar creează o copie identică și păstrează fișierul original - fișierul în care rulează macroul-- deschis, și asta este partea importantă, nu? Așa că acum, când am DeleteMe acolo, îl deschid, îl atribuiesc WBN, fac lucrurile pe care trebuie să le fac, scap de toate foile suplimentare - Știu ce am. Observați, înainte de a șterge foile, doriți să faceți DisplayAlerts = False, altfel vă întreabă în continuare „Hei, nu veți recupera foaia”. Înțeleg. Și apoi, în sfârșit aici, selectați prima foaie de lucru FN va fi Apple.xlsx și apoi putem face WBN.SaveAs Apple, ca Open XMLWorkbook. Fără macrocomenzi. Și apoi Închidere - frumosul despre Închidere este acum că mă întorc în acest registru de lucru, 2213.Dar creează o copie identică și păstrează fișierul original - fișierul în care rulează macroul-- deschis, și asta este partea importantă, nu? Așa că acum, când am DeleteMe acolo, îl deschid, îl atribuiesc WBN, fac lucrurile pe care trebuie să le fac, scap de toate foile suplimentare - Știu ce am. Observați, înainte de a șterge foile, doriți să faceți DisplayAlerts = False, altfel vă întreabă în continuare „Hei, nu veți recupera foaia”. Înțeleg. Și apoi, în sfârșit aici, selectați prima foaie de lucru FN va fi Apple.xlsx și apoi putem face WBN.SaveAs Apple, ca Open XMLWorkbook. Fără macrocomenzi. Și apoi Închidere - frumosul despre Închidere este acum că mă întorc în acest registru de lucru, 2213.Dar creează o copie identică și păstrează fișierul original - fișierul în care rulează macroul-- deschis, și asta este partea importantă, nu? Deci, acum, când am DeleteMe acolo, îl deschid, îl atribuiesc WBN, fac lucrurile pe care trebuie să le fac, scap de toate foile suplimentare - Știu ce am. Observați, înainte de a șterge foile, doriți să faceți DisplayAlerts = False, altfel vă întreabă în continuare „Hei, nu veți recupera foaia”. Înțeleg. Și apoi, în sfârșit aici, selectați prima foaie de lucru FN va fi Apple.xlsx și apoi putem face WBN.SaveAs Apple, ca Open XMLWorkbook. Fără macrocomenzi. Și apoi Închidere - frumosul despre Închidere este acum că mă întorc în acest registru de lucru, 2213.dreapta? Așa că acum, când am DeleteMe acolo, îl deschid, îl atribuiesc WBN, fac lucrurile pe care trebuie să le fac, scap de toate foile suplimentare - Știu ce am. Observați, înainte de a șterge foile, doriți să faceți DisplayAlerts = False, altfel vă întreabă în continuare „Hei, nu veți recupera foaia”. Înțeleg. Și apoi, în sfârșit aici, selectați prima foaie de lucru FN va fi Apple.xlsx și apoi putem face WBN.SaveAs Apple, ca Open XMLWorkbook. Fără macrocomenzi. Și apoi Închidere - frumosul despre Închidere este acum că mă întorc în acest registru de lucru, 2213.dreapta? Așa că acum, când am DeleteMe acolo, îl deschid, îl atribuiesc WBN, fac lucrurile pe care trebuie să le fac, scap de toate foile suplimentare - Știu ce am. Observați, înainte de a șterge foile, doriți să faceți DisplayAlerts = False, altfel vă întreabă în continuare „Hei, nu veți recupera foaia”. Înțeleg. Și apoi, în sfârșit aici, selectați prima foaie de lucru FN va fi Apple.xlsx și apoi putem face WBN.SaveAs Apple, ca Open XMLWorkbook. Fără macrocomenzi. Și apoi Închidere - frumosul despre Închidere este acum că mă întorc în acest registru de lucru, 2213.Nu o să recuperez foaia. "Îl înțeleg. Și apoi, în cele din urmă aici, selectați prima foaie de lucru FN va fi Apple.xlsx și apoi vom putea face WBN.SaveAs Apple, ca Open XMLWorkbook. Fără macrocomenzi Și apoi Închidere - frumosul despre Închidere este acum că mă întorc în acest registru de lucru, 2213.Nu o să recuperez foaia. "Îl înțeleg. Și apoi, în cele din urmă aici, selectați prima foaie de lucru FN va fi Apple.xlsx și apoi vom putea face WBN.SaveAs Apple, ca Open XMLWorkbook. Fără macrocomenzi Și apoi Închidere - frumosul despre Închidere este acum că mă întorc în acest registru de lucru, 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.
Rezumat de astăzi: doriți ca VBA să scrie mai multe copii ale registrului de lucru curent; Salvare ca cauzează probleme deoarece registrul de lucru original nu mai este deschis; în schimb, utilizați .SaveAsCopy pentru a salva o copie a registrului de lucru. Dacă doriți să descărcați registrul de lucru din videoclipul de astăzi, inclusiv macro-ul, accesați adresa URL din descrierea YouTube.
Vreau să te oprești, ne vedem data viitoare pentru un alt netcast de la.
Descărcați fișierul Excel
Pentru a descărca fișierul Excel: salvați-ca-păstrare-original-deschis.xlsm
Gândul Excel al zilei
Le-am cerut prietenilor mei Excel Master sfatul lor despre Excel. Gândul de astăzi să medităm:
„Datele sunt cifre, nu cuvinte”.
Duane Aubin
