Det er mange ganger i løpet av en måned hvor jeg trenger en Excel-arbeidsbok for å lage mange eksemplarer av seg selv. Jeg kommer vanligvis inn i en logisk feil ved å åpne * arbeidsboken, og jeg begynner å skrive kode for å gå gjennom en liste og bruke File, Save As for å lagre en kopi av arbeidsboken.
Her er flytskjemaet:
Ser du problemet ovenfor? Makroen kjører i arbeidsbok A. Når jeg lagrer som filen som RegionEast.xlsx og deretter lukker RegionEast.xlsx, slutter makroen å kjøre.
Jeg er vanligvis dypt inne i pseudokoden før jeg ser problemet.
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 SubHadde jeg tenkt fremover, hadde jeg laget en løsning med to arbeidsbøker. Sett alle makroene i arbeidsbok A. Plasser alle dataene i arbeidsbok B. Har WorkbookA gjentatte ganger åpne WorkbookB, endre data, SaveAs, Close.
Når jeg blir eldre og crankier, finner jeg ut at jeg er mindre i humør å gå med litt mer komplisert. Spesielt siden jeg har det meste av koden som fungerer for den opprinnelige SaveAs-kommandoen.
Dagens artikkel handler om den fantastiske VBA-metoden for SaveAsCopy. Denne kommandoen vil holde WorkbookA åpen og kalt WorkbookA. Makroen kan fortsette å gå. Men det vil skrive ut den nåværende tilstanden til arbeidsboken til en ny lukket arbeidsbok kalt WorkbookB.
Dette lar meg gå tilbake til det opprinnelige flytskjemaet:
Imidlertid oppdaget jeg ett problem med SaveAsCopy. Når jeg gjør ThisWorkbook.SaveAs, kan jeg velge om jeg vil lagre som XLSX eller XLSM. Hvis jeg trenger at makroene skal være tilgjengelige i den nye arbeidsboken, bruker jeg XLSM. Ellers bruker jeg XLSX og makroene forsvinner.
Dessverre, hvis du er i en XLSM-arbeidsbok, kan du ikke .SaveAsCopy og endre til XLSX. Koden vil fungere. Men den resulterende arbeidsboken åpnes ikke ettersom Excel oppdager feil samsvar mellom filtype og filtypen.
Min løsning er å SaveAsCopy som XLSM. Når kopien er lagret, kan jeg åpne arbeidsboken (lage to kopier av arbeidsboken i minnet) og deretter 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 SubSe på video
Videoutskrift
Lær Excel fra Podcast, episode 2213: Lagre som å bruke VBA, men hold originalen åpen.
Hei, velkommen tilbake til netcast. Jeg er Bill Jelen. Vel, du har kanskje lagt merke til at jeg nylig har tilbudt nedlasting av hver podcast fordi mange mennesker har bedt meg om det, og derfor prøvde jeg å gjøre livet så enkelt som mulig. Og hele målet var å lagre en kopi som du kan laste ned, men jeg ville ikke ha de ekstra tingene - du vet, tingene som er til min egen interne bruk - der, så jeg ville bli kvitt det.
Og du vet, la oss si at jeg hadde en situasjon der jeg måtte skrive 12 arbeidsbøker, ikke sant? Hver med et annet produkt. Så jeg kommer til å gå gjennom disse produktene, og jeg skal skrive det der til A2 og deretter lagre arbeidsboken, og kanskje rydde opp i noen ting. Ok. Så min første gjennomgang her er en makro som dette, ok? Så vi definerer den nåværende arbeidsboken - Regneark ("Data), Regneark (" Rapport ") for å finne disse - og deretter finne ut hvor mange rader med data vi har i dag, vi kommer til å løkke fra rad 2 ned til den siste raden kopierer du produktet fra dataarbeidsboken til rapportarbeidsboken.
OK, og nå er det her jeg er i ferd med å komme i trøbbel. Så den nye arbeidsboken skal hete "C: aaa " og deretter Apple.xlsx, og jeg skal lagre som en, vet du, med Apple.xlsx, og endre til XML - åpne xml arbeidsbok- - som vil fjerne makroene. Ok. Men nå vil jeg lukke arbeidsboken, men dessverre når du lager en Lagre som - se akkurat nå, er jeg i podcast 2013 - når jeg gjør en Lagre som etter det punktet i koden, er jeg nei lenger kommer til å være i Podcast 2013; Jeg kommer til å være i Apple.xlsx. Ok? Så hvis jeg vil begynne å slette ting, skal jeg slette det i kopien, men når jeg lukker kopien, kan jeg ikke komme tilbake til originalfilen. Ok? Og denne makroen - faktisk er hodet mitt i ferd med å eksplodere og prøver å finne ut om løkken fortsatt vil fungere eller ikke,Ikke sant? Så jeg tror Save As er feil vei å gå her.
Vel, heng deg på. Vi kunne gå to ruter: For det første kunne jeg ha en annen arbeidsbok som åpner Podcast 2213, gjør ting og deretter Lagrer som med det nye navnet, eller jeg kommer til å gå denne veien, ok, og dette er metoden jeg endte opp med bruker-- ok, og vi skal definere denne arbeidsboken, men så også en ny arbeidsbok. Ikke sant. Og alt er lik det samme her nede til vi kommer til det punktet hvor jeg var i ferd med å gjøre WBT.SaveAs. Sjekk dette ut: SaveCopyAs-- nå eksisterer dette ikke, så vidt jeg kan vite, i vanlig Excel … dette er bare VBA. SaveCopyAs sier, "Hei, se vi er i en fil som heter 2213, og jeg vil at du skal ta den filen 2213 i sin nåværende tilstand, og lagre den på disken og lukke." Hold originalfilen åpen - 2213 forblir åpen-- men nå har vi en helt ny fil på disken som heter Apple.xlsm. Egentlig, først, jeg 'Jeg skal bare kalle det DeleteMe.xlsm. Ok. Men det skaper en identisk kopi og holder originalfilen - filen som makroen kjører i - åpen, og det er den viktige delen, ikke sant? Så nå som jeg har DeleteMe der ute, åpner jeg den, tildeler den til WBN, gjør de tingene jeg trenger å gjøre, kvitter meg med alle de ekstra arkene - jeg vet hva jeg har. Legg merke til at før du sletter arkene, vil du gjøre DisplayAlerts = False, ellers blir det stadig spurt deg: "Hei, du kommer ikke til å få arket tilbake." Jeg forstår. Og så, endelig her, velg det første regnearket FN skal være Apple.xlsx, og så kan vi gjøre WBN.SaveAs Apple, som en Open XMLWorkbook. Ingen makroer. Og så Lukk - det vakre med Close er at jeg nå er tilbake i denne arbeidsboken, 2213.Men det skaper en identisk kopi og holder originalfilen - filen som makroen kjører i - åpen, og det er den viktige delen, ikke sant? Så nå som jeg har DeleteMe der ute, åpner jeg den, tildeler den til WBN, gjør de tingene jeg trenger å gjøre, kvitter meg med alle de ekstra arkene - jeg vet hva jeg har. Legg merke til at før du sletter arkene, vil du gjøre DisplayAlerts = False, ellers blir det stadig spurt deg: "Hei, du kommer ikke til å få arket tilbake." Jeg forstår. Og så, endelig her, velg det første regnearket FN skal være Apple.xlsx, og så kan vi gjøre WBN.SaveAs Apple, som en åpen XMLWorkbook. Ingen makroer. Og så Lukk - det vakre med Close er at jeg nå er tilbake i denne arbeidsboken, 2213.Men det skaper en identisk kopi og holder originalfilen - filen som makroen kjører i - åpen, og det er den viktige delen, ikke sant? Så nå som jeg har DeleteMe der ute, åpner jeg den, tilordner den til WBN, gjør de tingene jeg trenger å gjøre, kvitter meg med alle de ekstra arkene - jeg vet hva jeg har. Legg merke til at før du sletter arkene, vil du gjøre DisplayAlerts = False, ellers blir det stadig spurt deg: "Hei, du kommer ikke til å få arket tilbake." Jeg forstår. Og så, endelig her, velg det første regnearket FN skal være Apple.xlsx, og så kan vi gjøre WBN.SaveAs Apple, som en Open XMLWorkbook. Ingen makroer. Og så Lukk - det vakre med Close er at jeg nå er tilbake i denne arbeidsboken, 2213.Ikke sant? Så nå som jeg har DeleteMe der ute, åpner jeg den, tildeler den til WBN, gjør de tingene jeg trenger å gjøre, kvitter meg med alle de ekstra arkene - jeg vet hva jeg har. Legg merke til at før du sletter arkene, vil du gjøre DisplayAlerts = False, ellers blir det stadig spurt deg: "Hei, du kommer ikke til å få arket tilbake." Jeg forstår. Og så, endelig her, velg det første regnearket FN skal være Apple.xlsx, og så kan vi gjøre WBN.SaveAs Apple, som en Open XMLWorkbook. Ingen makroer. Og så Lukk - det vakre med Close er at jeg nå er tilbake i denne arbeidsboken, 2213.Ikke sant? Så nå som jeg har DeleteMe der ute, åpner jeg den, tildeler den til WBN, gjør de tingene jeg trenger å gjøre, kvitter meg med alle de ekstra arkene - jeg vet hva jeg har. Legg merke til at før du sletter arkene, vil du gjøre DisplayAlerts = False, ellers blir det stadig spurt deg: "Hei, du kommer ikke til å få arket tilbake." Jeg forstår. Og så, endelig her, velg det første regnearket FN skal være Apple.xlsx, og så kan vi gjøre WBN.SaveAs Apple, som en Open XMLWorkbook. Ingen makroer. Og så Lukk - det vakre med Close er at jeg nå er tilbake i denne arbeidsboken, 2213.kommer ikke til å få tilbake arket. "Jeg skjønner det. Og så, til slutt her, velg det første regnearket FN skal være Apple.xlsx, og så kan vi gjøre WBN.SaveAs Apple, som en åpen XMLWorkbook. Ingen makroer Og så Lukk - det vakre med Close er at jeg nå er tilbake i denne arbeidsboken, 2213.kommer ikke til å få tilbake arket. "Jeg skjønner det. Og så, til slutt her, velg det første regnearket FN skal være Apple.xlsx, og så kan vi gjøre WBN.SaveAs Apple, som en åpen XMLWorkbook. Ingen makroer Og så Lukk - det vakre med Close er at jeg nå er tilbake i denne arbeidsboken, 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.
Avslutning fra i dag: Du vil at VBA skal skrive flere eksemplarer av den aktuelle arbeidsboken; Lagre som gir problemer fordi den opprinnelige arbeidsboken ikke lenger er åpen; i stedet bruker du .SaveAsCopy for å lagre en kopi av arbeidsboken. Hvis du vil laste ned arbeidsboken fra dagens video, inkludert makroen, kan du gå til URL-en i YouTube-beskrivelsen.
Jeg vil ha deg for å ta turen innom, vi sees neste gang for en ny netcast fra.
Last ned Excel-fil
For å laste ned Excel-filen: lagre-som-holder-original-åpen.xlsm
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke å tenke på:
"Datoer er tall, ikke ord."
Duane Aubin
