Manipuler les classeurs en VBA

Le classeur est un des principaux objets d’Excel. Il est assez simple à manipuler, pour peu qu’on utilise l’objet Workbook.

Désigner le classeur à manipuler

Préparer la manipulation d’un classeur

Créer un module (Insertion > Module) ou utiliser un module existant. Vous pouvez nommer ce module mClasseur.
Tout en haut du module, ajouter ces 2 lignes de code:

'La déclaration des variables (Dim) est obligatoire dans toutes les procédures de ce module.
'Sinon, une erreur de compilation "Variable non définie" se produit.
Option Explicit
'Permet de comparer des chaines quelque soit leur casse : "A" sera égal à "a"
Option Compare Text

On va créer une fonction chargée de vérifier qu’un classeur existe avant de tenter de l’ouvrir.

'Teste l'existence d'un fichier, puis renvoie Vrai si le test réussit et Faux sur le test échoue.
Function EstFichierExiste(ByVal sCheminCompletFichier As String) As Boolean
EstFichierExiste = (Dir(sCheminCompletFichier, vbNormal) &amp;lt;&amp;gt; "")
End Function

On crée également une fonction chargée de vérifier si un classeur est déjà ouvert (dans la session Excel) avant de tenter de l’ouvrir.

'Teste si un classeur est ouvert dans la session Excel actuel, puis renvoie Vrai si le test réussit et Faux sur le test échoue
Function EstClasseurOuvert(ByVal sNomClasseur As String) As Boolean
Dim wb As Workbook

For Each wb In Workbooks
If wb.Name = sNomClasseur Then
EstClasseurOuvert = True
Exit Function
End If
Next

End Function

Enfin, on crée une fonction qui va extraire le nom (ou le chemin) d’un chemin complet. Excel a besoin du chemin complet pour ouvrir un fichier, mais seulement du nom pour vérifier s’il est déjà ouvert.

'Analyse un chemin complet de fichier, puis retourne le nom (2d argument sur Faux ou non renseigné)
'ou le chemin (2d argument sur Vrai)
Function ExtraitNomFichier(ByVal sCheminComplet As String, Optional ByVal bRetourChemin As Boolean) As String
Dim Pos As Integer

Pos = InStrRev(sCheminComplet, "\")
If Pos &amp;gt; 0 Then
If bRetourChemin Then
ExtraitNomFichier = Left(sCheminComplet, Pos - 1)
Else
ExtraitNomFichier = Mid(sCheminComplet, Pos + 1)
End If
Else
ExtraitNomFichier = sCheminComplet
End If

End Function

Déclarer un classeur

Nous allons manipuler un classeur contenant les ventes. Vous devez créer cette variable pour que le code de cet article fonctionne.

Dim wbVente As Workbook 'Déclare la variable wbvente de type Classeur
Dim sCheminComplet As String 'Déclare la variable sFichier de type Chaine de texte
sFichier = "C:\Mes documents\Ventes.xlsx" 'Affecte à la variable sFichier la valeur entre guillemets.

Ouvrir un classeur

2 versions possible pour ouvrir un classeur : une fonction très simple, mais qui ne gére pas les erreurs, et une fonction complète.
La fonction simple :

Function OuvrirClasseur_v1(ByVal sCheminComplet As String) As Workbook
Set OuvrirClasseur_v1 = Workbooks.Open(Filename:=sCheminComplet, ReadOnly:=True)
End Function

Pour tester la fonction, afficher la fenêtre Exécution (CTRL + G) puis saisir :

?OuvrirClasseur_v1("C:\Mes documents\Ventes.xlsx")

Appuyer sur Entrée dans la ligne pour l’exécuter.

Voici la fonction complète, plus longue, mais qui gère toutes les erreurs possibles (fichier déjà ouvert, fichier inexistant, fichier inaccessible, etc.) :

'Ouvre un classeur puis retourne-le si l'ouverture réussit.
Function OuvrirClasseur(ByVal sCheminComplet As String) As Workbook
Dim wbRetour As Workbook 'Déclare la variable wbRetour de type Classeur
Dim sFichier As String 'Déclare la variable sFichier de type Chaine de texte

sFichier = ExtraitNomFichier(sCheminComplet) 'Appelle la fonction personnalisée ExtraitNomFichier pour affecter à la variable le nom du fichier

If EstClasseurOuvert(sFichier) Then 'Appelle la fonction personnalisée EstClasseurOuvert pour v
Set OuvrirClasseur = Workbooks(sFichier) 'La fonction retourne le classeur déjà ouvert
Exit Function 'Quitte la fonction
End If

If Not EstFichierExiste(sCheminComplet) Then 'Appelle la fonction personnalisée pour tester si le fichier existe bien
MsgBox "Le classeur '" &amp;amp; sCheminComplet &amp;amp; "' n'existe pas.", vbExclamation 'N'existe pas : affiche un message d'erreur
Exit Function 'Quitte la fonction
End If

On Error Resume Next 'Masque (jusqu'à la fin de la fonction ou jusqu'à la prochaine intruction On Error GoTo 0) désormais toute les erreurs
Set OuvrirClasseur = Workbooks.Open(Filename:=sFichier, ReadOnly:=True)
If Err &amp;lt;&amp;gt; 0 Then 'Une erreur s'est produite lors de l'ouverture du fichier
MsgBox "Le classeur '" &amp;amp; sCheminComplet &amp;amp; "' n'a pu être ouvert : " &amp;amp; Err.Description, vbExclamation 'Affiche un message d'erreur
End If
On Error GoTo 0 'Ré-affiche toutes les erreurs qui se produiraient jusqu'à la fin de la fonction

End Function

Pour utiliser la fonction, créer une macro (ou une fonction), sur ce modèle :

Sub MaMacro()
Dim sCheminCompletFichierVente As String
Dim wbVente As Workbook

sCheminCompletFichierVente = "C:\Mes documents\Vente.xlsx"

Set wbVente = OuvrirClasseur(sCheminCompletFichierVente)
If wbVente Is Nothing Then
MsgBox "Pb ouverture"
End If

End Sub

Créer un classeur

Nous créons un classeur (Fichier > Nouveau) et le plaçons dans la variable wbVente

Set wbVente = Workbooks.Add

Vous pouvez créer un classeur en prenant un autre classeur comme modèle :

Set wbVente = Workbooks.Add(sFichier)

La variable wbVente contient alors un nouveau classeur dont le contenu est copié depuis le fichier sFichier.

Le classeur est déjà ouvert

Un classeur est ouvert dès le démarrage de la macro :

Dim sNomClasseur As String
sNomClasseur="Ventes.xlsx"
Set wbVente = Workbooks(sNomClasseur)

Notez que le classeur doit être désigné par son nom simple (Ventes.xlsx), sans le chemin (C:\Mes documents)
Si le classeur à manipuler est le classeur actif, voici la syntaxe :

Set wbVente = ActiveWorkbook

Si le classeur à manipuler est le premier à avoir été ouvert, utilisez cette syntaxe :

Set wbVente = Workbooks(1)

Principales actions sur un classeur

Enregistrer sous un classeur

wbVente.SaveAs Filename:="Ventes2013.xlsx"

Enregistrer un classeur

wbVente.Save

Notez qu’à la différence du fonctionnement d’Excel, la méthode Save sur un classeur sans nom ni emplacement ne provoque pas l’ouverture de la boîte de dialogue Enrgistrer sous : le classeur est enregistré avec son nom actuel (souvent Classeur1) et à son emplacement actuel (habituellement Mes documents).

Activer un classeur

wbVente.Activate

Notez qu’il n’est pas nécessaire d’activer un classeur pour le manipuler par sa variable.

Fermer un classeur

wbVente.Close SaveChanges:=True