Muitas vezes em um mês preciso de uma pasta de trabalho do Excel para criar várias cópias dela mesma. Normalmente, entro em um bug de lógica ao abrir * a * pasta de trabalho e começo a escrever código para percorrer uma lista e usar Arquivo, Salvar como para salvar uma cópia da pasta de trabalho.
Aqui está o fluxograma:
Você vê o problema acima? A macro está sendo executada no WorkbookA. Quando eu salvo o arquivo como RegionEast.xlsx e, a seguir, fecho RegionEast.xlsx, a macro para de ser executada.
Normalmente, estou profundamente envolvido no pseudocódigo antes de ver o 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 SubSe eu estivesse pensando no futuro, teria criado uma solução com duas pastas de trabalho. Coloque todas as macros na pasta de trabalhoA. Coloque todos os dados na pasta de trabalhoB. Faça com que a Pasta de Trabalho A abra repetidamente a Pasta de Trabalho B, altere os dados, Salvar Como, Fechar.
À medida que envelheço e fico mais mal-humorado, estou descobrindo que estou menos com vontade de fazer algo um pouco mais complicado. Especialmente porque tenho a maior parte do código funcionando para o comando SaveAs original.
O artigo de hoje é sobre o incrível método VBA para SaveAsCopy. Este comando manterá o WorkbookA aberto e denominado WorkbookA. A macro pode continuar em execução. Mas ele gravará o estado atual da pasta de trabalho em uma nova pasta de trabalho fechada chamada WorkbookB.
Isso me permite voltar ao fluxograma original:
No entanto, descobri um problema com SaveAsCopy. Quando eu faço ThisWorkbook.SaveAs, posso escolher se quero salvar como XLSX ou XLSM. Se eu precisar que as macros estejam disponíveis na nova pasta de trabalho, uso o XLSM. Caso contrário, eu uso XLSX e as macros desaparecerão.
Infelizmente, se você estiver em uma pasta de trabalho XLSM, não será possível .SaveAsCopy e alterar para XLSX com êxito. O código funcionará. Mas a pasta de trabalho resultante não será aberta, pois o Excel detecta uma incompatibilidade entre o tipo de arquivo e a extensão do arquivo.
Minha solução é SaveAsCopy como XLSM. Depois que a cópia for salva, posso abrir a pasta de trabalho (criando duas cópias da pasta de trabalho na memória) e Salvar como 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 SubAssistir vídeo
Transcrição de vídeo
Aprenda Excel com o Podcast, Episódio 2213: Salvar como usando VBA, mas mantenha o original aberto.
Ei, bem-vindo de volta ao netcast. Sou Bill Jelen. Bem, você deve ter notado que eu tenho oferecido o download de cada podcast recentemente porque muitas pessoas têm me pedido isso, então eu estava tentando tornar a vida o mais fácil possível. E o objetivo era salvar uma cópia que você pudesse baixar, mas eu não queria o material extra - você sabe, o material que é para meu próprio uso interno - ali, então eu queria me livrar disso.
E, você sabe, digamos que eu tive uma situação em que tive que escrever 12 pastas de trabalho, certo? Cada um com um produto diferente. Então, vou percorrer esses produtos e gravá-los lá no A2 e, em seguida, salvar a pasta de trabalho e talvez limpar algumas coisas. Tudo bem. Então minha primeira passagem por aqui é uma macro assim, certo? Então, definimos a pasta de trabalho atual - planilhas ("Dados), planilhas (" Relatório ") para encontrá-las - e então descobrir quantas linhas de dados temos hoje, vamos fazer um loop da linha 2 para baixo a última linha, copie o produto da pasta de trabalho de dados para a pasta de trabalho de relatório.
Tudo bem, e agora é aqui que estou prestes a ter problemas. Portanto, a nova pasta de trabalho se chamará "C: aaa " e, em seguida, Apple.xlsx, e vou Salvar como, você sabe, com Apple.xlsx, e mudar para XML - pasta de trabalho xml aberta- - o que removerá as macros. Tudo bem. Mas agora gostaria de fechar essa pasta de trabalho, mas, infelizmente, quando você faz um Salvar como - veja agora, estou no podcast 2013 - quando faço um Salvar como após esse ponto no código, não mais no Podcast 2013; Eu estarei em Apple.xlsx. Tudo bem? Então, agora, se eu quiser começar a deletar coisas, irei deletar na cópia, mas quando eu fechar a cópia, bem, não consigo voltar ao arquivo original. Tudo bem? E esta macro - na verdade, minha cabeça está prestes a explodir tentando descobrir se o loop ainda funcionará ou não,certo? Portanto, acho que Salvar como é o caminho errado aqui.
Bem, na verdade, espere. Poderíamos seguir dois caminhos: primeiro, eu poderia ter outra pasta de trabalho que abre o Podcast 2213, faz as coisas e depois salva como com o novo nome, ou vou por aqui, certo, e este é o método que acabei usando-- tudo bem, e vamos definir esta pasta de trabalho, mas também uma nova pasta de trabalho. Certo. E tudo é quase o mesmo aqui até chegarmos ao ponto em que eu estava prestes a fazer WBT.SaveAs. Verifique isso: SaveCopyAs-- agora, isso não existe, pelo que eu posso dizer, no Excel regular … isso é apenas VBA. SaveCopyAs diz: "Olha, estamos em um arquivo chamado 2213 e quero que você pegue esse arquivo 2213 em seu estado atual, salve-o no disco e feche". Mantenha o arquivo original aberto - 2213 permanece aberto - mas agora temos um novo arquivo em disco chamado Apple.xlsm. Na verdade, a princípio, eu 'Vou chamá-lo de DeleteMe.xlsm. Tudo bem. Mas ele cria uma cópia idêntica e mantém o arquivo original - o arquivo em que a macro está sendo executada - aberto, e essa é a parte importante, certo? Agora que tenho DeleteMe, eu abro, atribuo ao WBN, faço as coisas que preciso fazer, me livre de todas as folhas extras - eu sei o que tenho. Observe, antes de excluir as planilhas, você deseja fazer DisplayAlerts = False, caso contrário, ele continuará perguntando: "Ei, você não receberá a planilha de volta." I obtê-lo. E então, finalmente aqui, selecione a primeira planilha FN que vai ser Apple.xlsx, e então podemos fazer WBN.SaveAs Apple, como um Open XMLWorkbook. Sem macros. E então Close - a coisa bonita sobre Close é que agora estou de volta a este livro de exercícios, 2213.Mas ele cria uma cópia idêntica e mantém o arquivo original - o arquivo em que a macro está sendo executada - aberto, e essa é a parte importante, certo? Agora que tenho DeleteMe, eu o abro, atribuo ao WBN, faço as coisas que preciso fazer, me livre de todas as folhas extras - eu sei o que tenho. Observe, antes de excluir as planilhas, você deseja fazer DisplayAlerts = False, caso contrário, ele continuará perguntando: "Ei, você não receberá a planilha de volta." I obtê-lo. E então, finalmente aqui, selecione a primeira planilha FN que vai ser Apple.xlsx, e então podemos fazer WBN.SaveAs Apple, como um Open XMLWorkbook. Sem macros. E então Close - a coisa bonita sobre Close é que agora estou de volta a este livro de exercícios, 2213.Mas ele cria uma cópia idêntica e mantém o arquivo original - o arquivo em que a macro está sendo executada - aberto, e essa é a parte importante, certo? Agora que tenho DeleteMe, eu o abro, atribuo ao WBN, faço as coisas que preciso fazer, me livre de todas as folhas extras - eu sei o que tenho. Observe, antes de excluir as planilhas, você deseja fazer DisplayAlerts = False, caso contrário, ele continuará perguntando: "Ei, você não receberá a planilha de volta." I obtê-lo. E então, finalmente aqui, selecione a primeira planilha FN que vai ser Apple.xlsx, e então podemos fazer WBN.SaveAs Apple, como um Open XMLWorkbook. Sem macros. E então Close - a coisa bonita sobre Close é que agora estou de volta a este livro de exercícios, 2213.certo? Agora que tenho DeleteMe, eu abro, atribuo ao WBN, faço as coisas que preciso fazer, me livre de todas as folhas extras - eu sei o que tenho. Observe, antes de excluir as planilhas, você deseja fazer DisplayAlerts = False, caso contrário, ele continuará perguntando: "Ei, você não receberá a planilha de volta." I obtê-lo. E então, finalmente aqui, selecione a primeira planilha FN que vai ser Apple.xlsx, e então podemos fazer WBN.SaveAs Apple, como um Open XMLWorkbook. Sem macros. E então Close - a coisa bonita sobre Close é que agora estou de volta a este livro de exercícios, 2213.certo? Agora que tenho DeleteMe, eu abro, atribuo ao WBN, faço as coisas que preciso fazer, me livre de todas as folhas extras - eu sei o que tenho. Observe que, antes de excluir as planilhas, você deseja fazer DisplayAlerts = False, caso contrário, ele continuará perguntando: "Ei, você não receberá a planilha de volta." I obtê-lo. E então, finalmente aqui, selecione a primeira planilha FN que vai ser Apple.xlsx, e então podemos fazer WBN.SaveAs Apple, como um Open XMLWorkbook. Sem macros. E então Close - a coisa bonita sobre Close é que agora estou de volta a este livro de exercícios, 2213.não vamos receber a planilha de volta. "Eu entendi. E então, finalmente aqui, selecione a primeira planilha FN que será Apple.xlsx, e então podemos fazer WBN.SaveAs Apple, como um Open XMLWorkbook. Sem macros .E então Close - a coisa bonita sobre Close é que agora estou de volta a este livro de exercícios, 2213.não vamos receber a planilha de volta. "Eu entendi. E então, finalmente aqui, selecione a primeira planilha FN que será Apple.xlsx, e então podemos fazer WBN.SaveAs Apple, como um Open XMLWorkbook. Sem macros .E então Close - a coisa bonita sobre Close é que agora estou de volta a este livro de exercícios, 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.
Conclusão a partir de hoje: você deseja que o VBA escreva várias cópias da pasta de trabalho atual; Salvar como causa problemas porque a pasta de trabalho original não está mais aberta; em vez disso, você usa .SaveAsCopy para salvar uma cópia da pasta de trabalho. Se você deseja baixar a pasta de trabalho do vídeo de hoje, incluindo a macro, visite o URL na descrição do YouTube.
Eu quero que você passe por aqui, vejo você na próxima vez para outro netcast de.
Baixar arquivo Excel
Para baixar o arquivo do Excel: save-as-holding-original-open.xlsm
Excel Pensamento do Dia
Pedi conselhos aos meus amigos do Excel Master sobre o Excel. O pensamento de hoje para ponderar:
"Datas são números, não palavras."
Duane Aubin
