Existuje mnohokrát za mesiac, kedy potrebujem zošit programu Excel, aby som vytvoril veľa svojich kópií. Spravidla sa dostanem do logickej chyby otvorením * zošita * a začnem písať kód, aby som prešiel zoznamom a pomocou súborov, uloženia ako uložil kópiu zošita.
Tu je vývojový diagram:
Vidíte vyššie uvedený problém? Makro je spustené v WorkbookA. Keď uložím súbor ako RegionEast.xlsx a potom zavriem RegionEast.xlsx, makro sa zastaví.
Než uvidím problém, som zvyčajne hlboko v pseudokode.
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 SubKeby som premýšľal dopredu, vytvoril by som riešenie pre dva zošity. Vložte všetky makrá do WorkbookA. Vložte všetky údaje do WorkbookB. Nechajte WorkbookA opakovane otvárať WorkbookB, meniť údaje, SaveAs, Close.
S pribúdajúcimi rokmi a zúrivejšou náladou zisťujem, že mám menšiu náladu ísť na niečo komplikovanejšie. Najmä preto, že väčšinu kódu používam pre pôvodný príkaz SaveAs.
Dnešný článok je o úžasnej metóde VBA pre SaveAsCopy. Tento príkaz ponechá WorkbookA otvorený a nazvaný WorkbookA. Makro môže bežať ďalej. Ale vypíše aktuálny stav zošita do nového uzavretého zošita s názvom WorkbookB.
To mi umožňuje vrátiť sa k pôvodnému vývojovému diagramu:
Pri SaveAsCopy som však objavil jeden problém. Keď urobím ThisWorkbook.SaveAs, môžem si vybrať, či chcem uložiť ako XLSX alebo XLSM. Ak potrebujem, aby boli makrá dostupné v novom zošite, použijem XLSM. Inak používam XLSX a makrá zmiznú.
Bohužiaľ, ak sa nachádzate v zošite XLSM, nemôžete úspešne .SaveAsCopy a zmeniť na XLSX. Kód bude fungovať. Výsledný zošit sa však neotvorí, pretože program Excel zistí nesprávnu zhodu medzi typom súboru a príponou súboru.
Moje riešenie je SaveAsCopy ako XLSM. Po uložení kópie môžem otvoriť zošit (vytvorenie dvoch kópií zošita v pamäti) a potom 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 SubPozeraj video
Prepis videa
Naučte sa Excel z Podcastu, epizóda 2213: Uložiť ako pomocou VBA, pôvodný text však ponechajte otvorený.
Ahoj, vitaj späť na netcaste. Som Bill Jelen. Možno ste si všimli, že v poslednej dobe ponúkam stiahnutie každého podcastu, pretože veľa ľudí ma o to žiada, a preto som sa snažil čo najviac uľahčiť život. Celým cieľom bolo uložiť kópiu, ktorú si môžete stiahnuť, ale ja som nechcel ďalšie veci - viete, veci, ktoré sú pre moje vlastné interné použitie -, takže som sa toho chcel zbaviť.
A viete, povedzme, že som mal situáciu, že som musel napísať 12 pracovných zošitov, však? Každá s iným produktom. Idem teda prechádzať tieto produkty a napíšem to tam do A2 a potom uložím zošit a možno niečo vyčistím. Dobre. Takže môj prvý prechod sem je takýmto makrom, dobre? Definujeme teda aktuálny zošit - Pracovné listy ("Údaje"), Pracovné listy ("Správa"), aby sme ich našli - a potom zistíme, koľko riadkov údajov dnes máme. Budeme prechádzať z riadku 2 dole do posledný riadok, skopírujte produkt zo zošita s údajmi do zošita s prehľadom.
Dobre, a teraz som tu, kde sa chystám dostať do problémov. Nový zošit sa teda bude volať „C: aaa “ a potom Apple.xlsx a ja sa chystám na Save As a, viete, s Apple.xlsx a prejsť na XML - otvoriť zošit xml- - ktorý odstráni makrá. Dobre. Ale teraz by som chcel tento zošit uzavrieť, ale bohužiaľ, keď urobíte Uložiť ako-- pozri teraz, som v podcaste 2013 - keď urobím Uložiť ako po tomto bode v kóde, nie som už bude Podcast 2013; Budem v Apple.xlsx. Dobre? Takže teraz, ak chcem začať mazať veci, budem ich mazať z kópie, ale keď kópiu zavriem, dobre, nemôžem sa vrátiť k pôvodnému súboru. Dobre? A toto makro - vlastne moja hlava exploduje a snaží sa zistiť, či slučka bude stále fungovať alebo nebude fungovať,správny? Takže si myslím, že Save As je nesprávna cesta.
No, vlastne, vydrž. Mohli by sme ísť dvoma cestami: Najprv by som mohol mať ďalší zošit, ktorý otvorí Podcast 2213, urobí veci a potom uloží Rovnako ako pri novom názve, alebo pôjdem touto cestou, dobre, a toto je metóda, ktorú som skončil using-- v poriadku, a budeme definovať tento zošit, ale potom aj nový zošit. Správny. A všetko je tu dole trochu rovnaké, až kým sa nedostaneme do bodu, keď som sa chystal urobiť WBT.SaveAs. Skontrolujte toto: SaveCopyAs - teraz, pokiaľ viem, v bežnom programe Excel neexistuje … toto je iba VBA. SaveCopyAs hovorí: „Hej, pozrite sa do súboru s názvom 2213 a chcem, aby ste tento súbor 2213 zobrali v súčasnom stave, uložili na disk a zavreli.“ Ponechajte pôvodný súbor otvorený - 2213 zostáva otvorených - ale teraz máme na disku úplne nový súbor s názvom Apple.xlsm. Vlastne najskôrLen to nazvem DeleteMe.xlsm. Dobre. Ale vytvorí identickú kópiu a ponechá pôvodný súbor - súbor, v ktorom je spustené makro - otvorený, a to je dôležitá súčasť, nie? Takže teraz, keď tam mám DeleteMe, otvorím to, priradím to WBN, urobím veci, ktoré musím urobiť, zbavím sa všetkých tých ďalších hárkov-- Viem, čo mám. Všimnite si, že predtým, ako odstránite hárky, chcete urobiť DisplayAlerts = False, inak sa vás stále pýta: „Hej, hárok nedostaneš späť.“ Chápem. A potom konečne tu vyberte prvý pracovný hárok FN, ktorý bude Apple.xlsx, a potom môžeme urobiť WBN.SaveAs Apple ako Open XMLWorkbook. Žiadne makrá. A potom Zatvorte - krásne na Zatvorení je, že som teraz späť v tomto zošite, 2213.Ale vytvorí identickú kópiu a ponechá pôvodný súbor - súbor, v ktorom je spustené makro - otvorený, a to je dôležitá súčasť, nie? Takže teraz, keď tam mám DeleteMe, otvorím to, priradím to WBN, urobím veci, ktoré musím urobiť, zbavím sa všetkých tých ďalších hárkov-- viem, čo mám. Všimnite si, že predtým, ako odstránite hárky, chcete urobiť DisplayAlerts = False, inak sa vás stále pýta: „Hej, hárok nedostaneš späť.“ Chápem. A potom konečne tu vyberte prvý pracovný hárok FN, ktorý bude Apple.xlsx, a potom môžeme urobiť WBN.SaveAs Apple ako Open XMLWorkbook. Žiadne makrá. A potom Zatvorte - krásne na Zatvorení je, že som teraz späť v tomto zošite, 2213.Ale vytvorí identickú kópiu a ponechá pôvodný súbor - súbor, v ktorom je spustené makro - otvorený, a to je dôležitá súčasť, nie? Takže teraz, keď tam mám DeleteMe, otvorím to, priradím to WBN, urobím veci, ktoré musím urobiť, zbavím sa všetkých tých ďalších hárkov-- Viem, čo mám. Všimnite si, že predtým, ako odstránite hárky, chcete urobiť DisplayAlerts = False, inak sa vás stále bude pýtať: „Hej, hárok nedostaneš späť.“ Chápem. A potom konečne tu vyberte prvý pracovný hárok FN, ktorý bude Apple.xlsx, a potom môžeme urobiť WBN.SaveAs Apple ako Open XMLWorkbook. Žiadne makrá. A potom Zatvorte - krásne na Zatvorení je, že som teraz späť v tomto zošite, 2213.správny? Takže teraz, keď tam mám DeleteMe, otvorím to, priradím to WBN, urobím veci, ktoré musím urobiť, zbavím sa všetkých tých ďalších hárkov-- viem, čo mám. Všimnite si, že predtým, ako odstránite hárky, chcete urobiť DisplayAlerts = False, inak sa vás stále pýta: „Hej, hárok nedostaneš späť.“ Chápem. A potom konečne tu vyberte prvý pracovný hárok FN, ktorý bude Apple.xlsx, a potom môžeme urobiť WBN.SaveAs Apple ako Open XMLWorkbook. Žiadne makrá. A potom Zatvorte - krásne na Zatvorení je, že som teraz späť v tomto zošite, 2213.správny? Takže teraz, keď tam mám DeleteMe, otvorím to, priradím to WBN, urobím veci, ktoré musím urobiť, zbavím sa všetkých tých ďalších hárkov-- viem, čo mám. Všimnite si, že predtým, ako odstránite hárky, chcete urobiť DisplayAlerts = False, inak sa vás stále pýta: „Hej, hárok nedostaneš späť.“ Chápem. A potom konečne tu vyberte prvý pracovný hárok FN, ktorý bude Apple.xlsx, a potom môžeme urobiť WBN.SaveAs Apple ako Open XMLWorkbook. Žiadne makrá. A potom Zatvorte - krásne na Zatvorení je, že som teraz späť v tomto zošite, 2213.„Chystám sa získať späť hárok.“ Chápem to. A potom konečne tu vyberte prvý pracovný hárok FN, ktorý bude Apple.xlsx, a potom môžeme urobiť WBN.SaveAs Apple ako Open XMLWorkbook. Žiadne makrá .A potom Zatvorte - krásna vec na Zatvorení je, že som teraz späť v tomto zošite, 2213.„Chystám sa získať späť hárok.“ Chápem to. A potom konečne tu vyberte prvý pracovný hárok FN, ktorý bude Apple.xlsx, a potom môžeme urobiť WBN.SaveAs Apple ako Open XMLWorkbook. Žiadne makrá .A potom Zatvorte - krásna vec na Zatvorení je, že som teraz späť v tomto zošite, 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.
Zbalenie z dnešného dňa: Chcete, aby VBA napísala niekoľko kópií aktuálneho zošita; Uložiť ako spôsobuje problémy, pretože pôvodný zošit už nie je otvorený; namiesto toho použijete .SaveAsCopy na uloženie kópie zošita. Ak si chcete stiahnuť zošit z dnešného videa vrátane makra, navštívte adresu URL v popise služby YouTube.
Chcem, aby ste sa zastavili, uvidíme sa nabudúce pri ďalšom netcaste z.
Stiahnite si súbor Excel
Stiahnutie súboru programu Excel: save-as-keep-original-open.xlsm
Excel myslel dňa
Požiadal som svojich priateľov programu Excel Master o radu o programe Excel. Dnešná myšlienka na zamyslenie:
„Dátumy sú čísla, nie slová.“
Duane Aubin
