Il y a plusieurs fois dans un mois où j'ai besoin d'un classeur Excel pour créer de nombreuses copies de lui-même. J'obtiens généralement un bogue de logique en ouvrant * le * classeur et je commence à écrire du code pour parcourir une liste et utiliser Fichier, Enregistrer sous pour enregistrer une copie du classeur.
Voici l'organigramme:
Voyez-vous le problème ci-dessus? La macro s'exécute dans WorkbookA. Lorsque j'enregistre le fichier en tant que RegionEast.xlsx, puis que je ferme RegionEast.xlsx, la macro s'arrête de s'exécuter.
Je suis généralement profondément dans le pseudocode avant de voir le problème.
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 SubSi j'avais réfléchi à l'avenir, j'aurais créé une solution à deux classeurs. Mettez toutes les macros dans WorkbookA. Mettez toutes les données dans WorkbookB. Demandez à WorkbookA d'ouvrir à plusieurs reprises WorkbookB, modifiez les données, Enregistrer sous, Fermer.
En vieillissant et de plus en plus grincheux, je constate que je suis moins d'humeur à aller avec un peu plus compliqué. D'autant plus que la plupart du code fonctionne pour la commande SaveAs d'origine.
L'article d'aujourd'hui porte sur l'impressionnante méthode VBA pour SaveAsCopy. Cette commande gardera WorkbookA ouvert et appelé WorkbookA. La macro peut continuer à fonctionner. Mais il écrira l'état actuel du classeur dans un nouveau classeur fermé appelé WorkbookB.
Cela me permet de revenir à l'organigramme d'origine:
Cependant, j'ai découvert un problème avec SaveAsCopy. Lorsque je fais ThisWorkbook.SaveAs, je peux choisir si je veux enregistrer au format XLSX ou XLSM. Si j'ai besoin que les macros soient disponibles dans le nouveau classeur, j'utilise XLSM. Sinon, j'utilise XLSX et les macros disparaîtront.
Malheureusement, si vous êtes dans un classeur XLSM, vous ne pouvez pas réussir .SaveAsCopy et passer à XLSX. Le code fonctionnera. Mais le classeur résultant ne s'ouvre pas car Excel détecte un décalage entre le type de fichier et l'extension de fichier.
Ma solution est de SaveAsCopy en XLSM. Une fois la copie enregistrée, je peux ouvrir le classeur (en créant deux copies du classeur en mémoire), puis 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 SubRegarder la vidéo
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2213: Enregistrer sous en utilisant VBA, mais gardez l'original ouvert.
Salut, bon retour sur le netcast. Je suis Bill Jelen. Eh bien, vous avez peut-être remarqué que j'ai proposé le téléchargement de chaque podcast récemment parce que beaucoup de gens me le demandaient, et j'essayais donc de rendre la vie aussi facile que possible. Et tout le but était d'enregistrer une copie que vous pouvez télécharger, mais je ne voulais pas les trucs supplémentaires - vous savez, les trucs qui sont pour mon propre usage interne - là, alors je voulais m'en débarrasser.
Et, vous savez, disons que j'ai eu une situation où j'ai dû écrire 12 cahiers, non? Chacun avec un produit différent. Donc, je vais parcourir ces produits et je vais l'écrire dans A2, puis enregistrer le classeur et peut-être nettoyer certaines choses. Bien. Donc mon premier passage ici est une macro comme celle-ci, d'accord? Donc, nous définissons le classeur actuel - Feuilles de travail ("Données), Feuilles de travail (" Rapport ") pour les trouver - puis déterminons le nombre de lignes de données dont nous disposons aujourd'hui, nous allons faire une boucle de la ligne 2 à la dernière ligne, copiez le produit du classeur de données vers le classeur de rapport.
D'accord, et maintenant c'est là que je suis sur le point d'avoir des ennuis. Le nouveau classeur s'appellera donc "C: aaa " puis Apple.xlsx, et je vais enregistrer sous, vous savez, avec Apple.xlsx, et passer en XML - ouvrir le classeur xml- - ce qui supprimera les macros. Bien. Mais maintenant, j'aimerais fermer ce classeur, mais malheureusement, lorsque vous faites un Enregistrer sous - voyez maintenant, je suis dans le podcast 2013 - lorsque je fais un Enregistrer sous après ce point dans le code, je ne ne sera plus dans Podcast 2013; Je vais être dans Apple.xlsx. Bien? Donc, maintenant, si je veux commencer à supprimer des éléments, je vais les supprimer dans la copie, mais lorsque je ferme la copie, eh bien, je ne peux pas revenir au fichier d'origine. Bien? Et cette macro - en fait, ma tête est sur le point d'exploser en essayant de comprendre si la boucle fonctionnera toujours ou ne fonctionnera pas,droite? Je pense donc que Enregistrer sous n'est pas la bonne façon de procéder.
Eh bien, en fait, attendez. Nous pourrions emprunter deux voies: d'abord, je pourrais avoir un autre classeur qui ouvre le podcast 2213, fait les choses, puis enregistre sous avec le nouveau nom, ou je vais suivre cette voie, d'accord, et c'est la méthode que j'ai finie en utilisant - d'accord, et nous allons définir ce classeur, mais aussi un nouveau classeur. Droite. Et tout est un peu la même ici jusqu'à ce que nous arrivions au point où j'allais faire WBT.SaveAs. Vérifiez ceci: SaveCopyAs - maintenant, cela n'existe pas, pour autant que je sache, dans Excel ordinaire … c'est VBA seulement. SaveCopyAs dit: "Hé, regardez, nous sommes dans un fichier appelé 2213 et je veux que vous preniez ce fichier 2213 dans son état actuel, que vous l'enregistriez sur le disque et que vous le fermiez." Gardez le fichier d'origine ouvert - 2213 reste ouvert - mais nous avons maintenant un tout nouveau fichier sur le disque appelé Apple.xlsm. En fait, au début, je 'Je vais simplement l'appeler DeleteMe.xlsm. Bien. Mais il crée une copie identique et garde le fichier original - le fichier dans lequel la macro s'exécute - ouvert, et c'est la partie importante, non? Alors maintenant que j'ai DeleteMe là-bas, je l'ouvre, l'assigne à WBN, fais ce que j'ai à faire, me débarrasse de toutes les feuilles supplémentaires - je sais ce que j'ai. Remarquez, avant de supprimer les feuilles, vous voulez faire DisplayAlerts = False, sinon il continue à vous demander, "Hé, vous n'allez pas récupérer la feuille." J'ai compris. Et puis, enfin ici, sélectionnez la première feuille de calcul FN va être Apple.xlsx, puis nous pouvons faire WBN.SaveAs Apple, en tant que Open XMLWorkbook. Pas de macros. Et puis Close - la belle chose à propos de Close est que je suis maintenant de retour dans ce classeur, 2213.Mais il crée une copie identique et garde le fichier original - le fichier dans lequel la macro s'exécute - ouvert, et c'est la partie importante, non? Alors maintenant que j'ai DeleteMe là-bas, je l'ouvre, l'assigne à WBN, fais ce que j'ai à faire, me débarrasse de toutes les feuilles supplémentaires - je sais ce que j'ai. Remarquez, avant de supprimer les feuilles, vous voulez faire DisplayAlerts = False, sinon il continue à vous demander, "Hé, vous n'allez pas récupérer la feuille." J'ai compris. Et puis, enfin ici, sélectionnez la première feuille de calcul FN va être Apple.xlsx, puis nous pouvons faire WBN.SaveAs Apple, en tant que Open XMLWorkbook. Pas de macros. Et puis Close - la belle chose à propos de Close est que je suis maintenant de retour dans ce classeur, 2213.Mais il crée une copie identique et garde le fichier original - le fichier dans lequel la macro s'exécute - ouvert, et c'est la partie importante, non? Alors maintenant que j'ai DeleteMe là-bas, je l'ouvre, l'assigne à WBN, fais ce que j'ai à faire, me débarrasse de toutes les feuilles supplémentaires - je sais ce que j'ai. Remarquez, avant de supprimer les feuilles, vous voulez faire DisplayAlerts = False, sinon il continue à vous demander, "Hé, vous n'allez pas récupérer la feuille." J'ai compris. Et puis, enfin ici, sélectionnez la première feuille de calcul FN va être Apple.xlsx, puis nous pouvons faire WBN.SaveAs Apple, en tant que Open XMLWorkbook. Pas de macros. Et puis Close - la belle chose à propos de Close est que je suis maintenant de retour dans ce classeur, 2213.droite? Alors maintenant que j'ai DeleteMe là-bas, je l'ouvre, l'assigne à WBN, fais ce que j'ai à faire, me débarrasse de toutes les feuilles supplémentaires - je sais ce que j'ai. Remarquez, avant de supprimer les feuilles, vous voulez faire DisplayAlerts = False, sinon il continue à vous demander, "Hé, vous n'allez pas récupérer la feuille." J'ai compris. Et puis, enfin ici, sélectionnez la première feuille de calcul FN va être Apple.xlsx, puis nous pouvons faire WBN.SaveAs Apple, en tant que Open XMLWorkbook. Pas de macros. Et puis Close - la belle chose à propos de Close est que je suis maintenant de retour dans ce classeur, 2213.droite? Alors maintenant que j'ai DeleteMe là-bas, je l'ouvre, l'assigne à WBN, fais ce que j'ai à faire, me débarrasse de toutes les feuilles supplémentaires - je sais ce que j'ai. Remarquez, avant de supprimer les feuilles, vous voulez faire DisplayAlerts = False, sinon il continue à vous demander, "Hé, vous n'allez pas récupérer la feuille." J'ai compris. Et puis, enfin ici, sélectionnez la première feuille de calcul FN va être Apple.xlsx, puis nous pouvons faire WBN.SaveAs Apple, en tant que Open XMLWorkbook. Pas de macros. Et puis Close - la belle chose à propos de Close est que je suis maintenant de retour dans ce classeur, 2213.Je ne vais pas récupérer la feuille. "Je comprends. Et puis, enfin ici, sélectionnez la première feuille de calcul FN va être Apple.xlsx, et ensuite nous pouvons faire WBN.SaveAs Apple, comme Open XMLWorkbook. Pas de macros Et puis Close - la belle chose à propos de Close est que je suis maintenant de retour dans ce classeur, 2213.Je ne vais pas récupérer la feuille. "Je comprends. Et puis, enfin ici, sélectionnez la première feuille de calcul FN va être Apple.xlsx, et ensuite nous pouvons faire WBN.SaveAs Apple, comme Open XMLWorkbook. Pas de macros Et puis Close - la belle chose à propos de Close est que je suis maintenant de retour dans ce classeur, 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.
Récapitulatif à partir d'aujourd'hui: vous voulez que VBA rédige plusieurs copies du classeur actuel; Enregistrer sous provoque des problèmes car le classeur d'origine n'est plus ouvert; à la place, vous utilisez .SaveAsCopy pour enregistrer une copie du classeur. Si vous souhaitez télécharger le classeur à partir de la vidéo d'aujourd'hui, y compris la macro, visitez l'URL dans la description YouTube.
Je veux que vous vous arrêtiez, je vous verrai la prochaine fois pour une autre diffusion sur Internet.
Télécharger le fichier Excel
Pour télécharger le fichier Excel: save-as-keep-original-open.xlsm
Excellente pensée du jour
J'ai demandé à mes amis Excel Master leurs conseils sur Excel. Pensée d'aujourd'hui à méditer:
"Les dates sont des nombres, pas des mots."
Duane Aubin
