Er zijn vaak in een maand dat ik een Excel-werkmap nodig heb om veel kopieën van zichzelf te maken. Ik kom meestal in een logische fout door * de * werkmap te openen en ik begin code te schrijven om door een lijst te lopen en gebruik Bestand, Opslaan als om een kopie van de werkmap op te slaan.
Hier is het stroomschema:
Zie je het bovenstaande probleem? De macro wordt uitgevoerd in WorkbookA. Wanneer ik het bestand opsla als RegionEast.xlsx en vervolgens RegionEast.xlsx sluit, wordt de macro niet meer uitgevoerd.
Ik ben meestal diep in de pseudocode voordat ik het probleem zie.
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 SubAls ik vooruit had nagedacht, had ik een oplossing met twee werkboeken gemaakt. Zet alle macro's in WorkbookA. Zet alle gegevens in WorkbookB. Laat WorkbookA herhaaldelijk WorkbookB openen, wijzig de gegevens, SaveAs, Close.
Naarmate ik ouder en crankier word, merk ik dat ik minder in de stemming ben om iets gecompliceerder te doen. Vooral omdat ik de meeste code heb die werkt voor het originele SaveAs-commando.
Het artikel van vandaag gaat over de geweldige VBA-methode voor SaveAsCopy. Deze opdracht houdt WorkbookA open en heet WorkbookA. De macro kan blijven draaien. Maar het schrijft de huidige status van de werkmap naar een nieuwe gesloten werkmap genaamd WorkbookB.
Hierdoor kan ik teruggaan naar het oorspronkelijke stroomschema:
Ik ontdekte echter een probleem met SaveAsCopy. Wanneer ik ThisWorkbook.SaveAs doe, kan ik kiezen of ik wil opslaan als XLSX of XLSM. Als ik de macro's nodig heb om beschikbaar te zijn in de nieuwe werkmap, dan gebruik ik XLSM. Anders gebruik ik XLSX en zullen de macro's verdwijnen.
Als u zich in een XLSM-werkmap bevindt, kunt u helaas niet met succes .SaveAsCopy en overschakelen naar XLSX. De code zal werken. Maar de resulterende werkmap kan niet worden geopend omdat Excel een verkeerde match detecteert tussen het bestandstype en de bestandsextensie.
Mijn oplossing is om SaveAsCopy als XLSM te gebruiken. Zodra de kopie is opgeslagen, kan ik de werkmap openen (twee kopieën van de werkmap in het geheugen maken) en vervolgens Opslaan als 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 SubBekijk video
Videotranscriptie
Leer Excel van Podcast, aflevering 2213: opslaan als met VBA, maar houd het origineel open.
Hé, welkom terug bij de netcast. Ik ben Bill Jelen. Nou, het is je misschien opgevallen dat ik de laatste tijd de download van elke podcast aanbood, omdat veel mensen me daarom hebben gevraagd, en dus probeerde ik het leven zo gemakkelijk mogelijk te maken. En het hele doel was om een kopie op te slaan die je kunt downloaden, maar ik wilde de extra dingen - weet je, de dingen die voor mijn eigen interne gebruik zijn - daar niet, dus ik wilde daar vanaf.
En, weet je, laten we zeggen dat ik een situatie had waarin ik 12 werkboeken moest schrijven, toch? Elk met een ander product. Dus ik ga deze producten doorlopen en ik ga het daar naar A2 schrijven en dan de werkmap opslaan en misschien wat dingen opruimen. Oké. Dus mijn eerste passage hier is een macro als deze, oké? Dus we definiëren de huidige werkmap - Werkbladen ("Gegevens), Werkbladen (" Rapport ") om die te vinden - en dan berekenen we hoeveel rijen gegevens we vandaag hebben, we gaan van Rij 2 naar kopieer in de laatste rij het product van de gegevenswerkmap naar de rapportwerkmap.
Oké, en nu kom ik hier in de problemen. Dus de nieuwe werkmap zal "C: aaa " heten en dan Apple.xlsx, en ik ga Opslaan als, weet je, met Apple.xlsx, en verander naar XML - open xml-werkmap- - waardoor de macro's worden verwijderd. Oké. Maar nu wil ik die werkmap sluiten, maar helaas als je een Opslaan als doet - zie nu, ik zit in podcast 2013 - als ik een Opslaan als doe na dat punt in de code, ben ik geen zal langer in Podcast 2013 zijn; Ik ga naar Apple.xlsx. Oké? Dus nu, als ik dingen wil verwijderen, ga ik het verwijderen in de kopie, maar als ik de kopie sluit, kan ik niet meer terug naar het originele bestand. Oké? En deze macro … eigenlijk staat mijn hoofd op exploderen om erachter te komen of de lus nog steeds werkt of niet,Rechtsaf? Dus ik denk dat Save As de verkeerde manier is om hier naartoe te gaan.
Nou, eigenlijk, wacht even. We zouden twee routes kunnen volgen: ten eerste zou ik een andere werkmap kunnen hebben die Podcast 2213 opent, de dingen doet en vervolgens opslaat zoals met de nieuwe naam, of ik ga deze kant op, oké, en dit is de methode die ik ben beland gebruiken … oké, en we gaan deze werkmap definiëren, maar dan ook een nieuwe werkmap. Rechtsaf. En alles is hier beneden min of meer hetzelfde, totdat we op het punt komen waarop ik op het punt stond WBT.SaveAs te doen. Bekijk dit eens: SaveCopyAs-- dit bestaat, voor zover ik kan nagaan, niet in gewone Excel … dit is alleen VBA. SaveCopyAs zegt: "Hé, kijk, we zitten in een bestand met de naam 2213 en ik wil dat je dat bestand 2213 in zijn huidige staat neemt, het op schijf opslaat en sluit." Houd het originele bestand open - 2213 blijft open - maar nu hebben we een gloednieuw bestand op schijf genaamd Apple.xlsm.Ik ga het gewoon DeleteMe.xlsm noemen. Oké. Maar het maakt een identieke kopie en houdt het originele bestand - het bestand waarin de macro wordt uitgevoerd - open, en dat is het belangrijkste, toch? Dus nu ik DeleteMe heb, open ik het, wijs het toe aan WBN, doe de dingen die ik moet doen, verwijder alle extra bladen - ik weet wat ik heb. Merk op dat je, voordat je de bladen verwijdert, DisplayAlerts = False wilt doen, anders blijft het je vragen: "Hé, je krijgt het blad niet terug." Ik snap het. En dan, eindelijk hier, selecteer het eerste werkblad dat FN Apple.xlsx wordt, en dan kunnen we WBN.SaveAs Apple doen, als een Open XMLWorkbook. Geen macro's. En dan Close - het mooie van Close is dat ik nu terug ben in dit werkboek, 2213.Maar het maakt een identieke kopie en houdt het originele bestand - het bestand waarin de macro wordt uitgevoerd - open, en dat is het belangrijkste, toch? Dus nu ik DeleteMe heb, open ik het, wijs het toe aan WBN, doe de dingen die ik moet doen, verwijder alle extra bladen - ik weet wat ik heb. Merk op dat je, voordat je de bladen verwijdert, DisplayAlerts = False wilt doen, anders blijft het je vragen: "Hé, je krijgt het blad niet terug." Ik snap het. En dan, eindelijk hier, selecteer het eerste werkblad dat FN Apple.xlsx wordt, en dan kunnen we WBN.SaveAs Apple doen, als een Open XMLWorkbook. Geen macro's. En dan Close - het mooie van Close is dat ik nu terug ben in dit werkboek, 2213.Maar het maakt een identieke kopie en houdt het originele bestand - het bestand waarin de macro wordt uitgevoerd - open, en dat is het belangrijkste, toch? Dus nu ik DeleteMe heb, open ik het, wijs het toe aan WBN, doe de dingen die ik moet doen, verwijder alle extra bladen - ik weet wat ik heb. Merk op dat je, voordat je de bladen verwijdert, DisplayAlerts = False wilt doen, anders blijft het je vragen: "Hé, je krijgt het blad niet terug." Ik snap het. En dan, eindelijk hier, selecteer het eerste werkblad dat FN Apple.xlsx wordt, en dan kunnen we WBN.SaveAs Apple doen, als een Open XMLWorkbook. Geen macro's. En dan Close - het mooie van Close is dat ik nu terug ben in dit werkboek, 2213.Rechtsaf? Dus nu ik DeleteMe heb, open ik het, wijs het toe aan WBN, doe de dingen die ik moet doen, verwijder alle extra bladen - ik weet wat ik heb. Merk op, voordat je de bladen verwijdert, wil je DisplayAlerts = False doen, anders blijft het je vragen: "Hé, je krijgt het blad niet terug." Ik snap het. En dan, eindelijk hier, selecteer het eerste werkblad dat FN Apple.xlsx wordt, en dan kunnen we WBN.SaveAs Apple doen, als een Open XMLWorkbook. Geen macro's. En dan Close - het mooie van Close is dat ik nu terug ben in dit werkboek, 2213.Rechtsaf? Dus nu ik DeleteMe heb, open ik het, wijs het toe aan WBN, doe de dingen die ik moet doen, verwijder alle extra bladen - ik weet wat ik heb. Merk op, voordat je de bladen verwijdert, wil je DisplayAlerts = False doen, anders blijft het je vragen: "Hé, je krijgt het blad niet terug." Ik snap het. En dan, eindelijk hier, selecteer het eerste werkblad dat FN Apple.xlsx wordt, en dan kunnen we WBN.SaveAs Apple doen, als een Open XMLWorkbook. Geen macro's. En dan Close - het mooie van Close is dat ik nu terug ben in dit werkboek, 2213.ik krijg het blad niet terug. "Ik snap het. En dan, eindelijk hier, selecteer het eerste werkblad FN wordt Apple.xlsx, en dan kunnen we WBN.SaveAs Apple doen, als een Open XMLWorkbook. Geen macro's En dan Close - het mooie van Close is dat ik nu terug ben in dit werkboek, 2213.ik krijg het blad niet terug. "Ik snap het. En dan, eindelijk hier, selecteer het eerste werkblad FN wordt Apple.xlsx, en dan kunnen we WBN.SaveAs Apple doen, als een Open XMLWorkbook. Geen macro's En dan Close - het mooie van Close is dat ik nu terug ben in dit werkboek, 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.
Afronding vanaf vandaag: u wilt dat VBA verschillende exemplaren van de huidige werkmap schrijft; Opslaan als veroorzaakt problemen omdat de originele werkmap niet meer open is; in plaats daarvan gebruikt u .SaveAsCopy om een kopie van de werkmap op te slaan. Als je de werkmap van de video van vandaag, inclusief de macro, wilt downloaden, ga dan naar de URL in de YouTube-beschrijving.
Ik wil dat je langskomt, ik zie je de volgende keer voor een nieuwe netcast van.
Download Excel-bestand
Om het Excel-bestand te downloaden: save-as-keep-original-open.xlsm
Excel-gedachte van de dag
Ik heb mijn Excel Master-vrienden om advies over Excel gevraagd. De gedachte van vandaag om na te denken:
"Datums zijn cijfers, geen woorden."
Duane Aubin
