Supposons que nous ayons un fichier excel, avec des macros, et un onglet (une feuille), qui contient des données que nous voudrions placer dans un autre fichier excel, facile à utiliser et imprimer.
Dans ce script, nous réalisons les actions suivantes :
- Définir une variable texte à partir d’éléments présents dans le fichier ou calculés.
- partage des variable entre fichier
- créer une copie sans macros d’une feuille qui en contient
- enregistrer cette nouvelle feuille dans un autre fichier avec un nom correspondant à la variable texte ci-dessus ;
- fermer le fichier
Définir une variable texte à partir d’éléments divers
Voici l’ensemble du script VBA
Public extension As String
Public chemin As String, nomfichier As String
Sub Créer_fichier_excel()
Dim Sh As Worksheet
'les variables extension, chemin et nomfichier sont déclarées Public pour passer d'un classeur à l'autre
'il faut d'abord créer une copie de la feuille SANS MACROS
' source https://www.developpez.net/forums/d1044048/logiciels/microsoft-office/excel/macros-vba-excel/ajouter-feuille-n-existe/
On Error Resume Next
Set Sh = ThisWorkbook.Sheets("modeleSANS")
'On vérifie l'existance de la feuille "modeleSANS"
If Not Sh Is Nothing Then
' la feuille existe, on la vide
Sh.Select
Sh.UsedRange.ClearContents
Cells.Select
Selection.UnMerge
Else
' La feuille modeleSANS n'existe pas, on la crée
Set Sh = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
Sh.Name = "modeleSANS"
End If
' Maintenant on copie le contenu et le format de modele mais PAS les macros
Sheets("modele").Select
Cells.Select
Selection.Copy
Sheets("modeleSANS").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
' tant qu'on est dans le bon fichier on va chercher le nom du futur fichier
Sheets("TabDyn").Select
Range("H38:K39").Select
Selection.Copy
Range("H40").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H40").Select
Application.CutCopyMode = False
nomfichier = "Inventaire-" & Sheets("TabDyn").Range("H40").Value & "-"
nomfichier = nomfichier & Format(Date, "yy-mm-dd") & extension
' nomfichier = nomfichier & Year(Now) & Month(Now) & Day(Now) & extension
' et maintenant on peut enfin copier la feuille dans le fichier !
Sheets("modeleSANS").Select
ThisWorkbook.ActiveSheet.Copy
extension = ".xlsx"
chemin = ThisWorkbook.Path
' Actions dans le fichier nouvellement crée
Par exemple définir la zone d’impression
With ActiveWorkbook
.SaveAs Filename:=chemin & "\" & nomfichier
.Close
End With
End Sub
Variables partagées entre fichiers
Les variables ci-dessus extension, chemin et nomfichier doivent être partagées entre le fichier source (qui contient ce qui va définir les contenus) et le fichier cible qui va prendre ce nom. Si ces variables ne sont pas déclarées à l’extérieur de la macro, en mode Public, le fichier créé n’a pas de nom et une fenêtre nous demande le nom à attribuer.
créer une variable composée d’éléments du fichier
Les deux lignes ci-dessous créent une variable nomfichier qui est une chaine de caractères sous la forme Inventaire-XXX-AA-MM-JJZZZ, avec
- XXX qui correspond à ce qui est dans la cellule H40 de l’onglet TabDyn
- AA-MM-JJ qui est créé à partir de la date du jour
- ZZZ qui est défini comme « .xlsx »
nomfichier = "Inventaire-" & Sheets("TabDyn").Range("H40").Value & "-"
nomfichier = nomfichier & Format(Date, "yy-mm-dd") & extension
créer une copie sans macros d’une feuille qui en contient
Dans le script final, on regarde si l’onglet modeleSANS existe. S’il n’existe pas, on le crée.
Ensuite, on copie le contenu de cellules de l’onglet modele et on le colle sous différentes formes dans l’onglet modeleSANS : valeurs uniquement (Paste:=xlPasteValues), puis formats (Paste:=xlPasteAllUsingSourceTheme). On a ainsi un onglet qui contient tout sauf les macros.
Copier un onglet dans un autre fichier
les deux lignes ci-dessous enregistrent la feuille modeleSANS dans un autre fichier qui n’a pas de nom à ce stade.
Sheets("modeleSANS").Select
ThisWorkbook.ActiveSheet.Copy
Enregistrer le nouveau fichier excel
Les lignes suivantes enregistrent le nouveau fichier sous un nom prédéfini, le ferment et ramènent dans le fichier d’origine
With ActiveWorkbook
.SaveAs Filename:=chemin & "\" & nomfichier
.Close
End With
' Revenir à la cellule A1 de l'onglet Modele
Sheets("modele").Select
Range("A1").Select
MsgBox "Fichier enregistré : " & chemin & " " & nomfichier
Et maintenant ?
Il faudrait certainement savoir aussi comment enregistrer plusieurs onglets successivement dans un même fichier !
