Ajuster automatiquement la largeur des colonnes Excel via VBA depuis Access (AutoFit fiable et rapide)

Après un export Access → Excel, vos colonnes restent trop étroites ? Voici un guide complet, concret et robuste pour déclencher l’AutoFit au bon moment, sur le bon classeur, et éviter les pièges fréquents (objets mal référencés, timing, WrapText, fusions, etc.).

Sommaire

Vue d’ensemble de la question

Un export Access vers Excel fonctionne, mais le classeur ouvert présente des colonnes tronquées : le code VBA utilisé ne parvient pas à « autofit » les colonnes (erreurs d’objet, ajustement inopérant, portée ambiguë). L’objectif est de corriger ces soucis et de fiabiliser l’ajustement automatique des colonnes (et éventuellement des lignes), immédiatement après l’écriture des données.

Réponse & Solution

Causes principales repérées

Utilisation de ActiveWorkbook.Name (renvoie une chaîne) à la place d’un objet Workbook.
AutoFit exécuté trop tôt, avant que les données ne soient réellement écrites dans la feuille.
Références d’objets Excel imprécises (risque d’agir sur le mauvais classeur/feuille si plusieurs instances sont ouvertes).

Pattern fiable (Access VBA → Excel)

' Ouvrir / créer Excel proprement
Dim xl As Object        ' Excel.Application
Dim wb As Object        ' Excel.Workbook
Dim ws As Object        ' Excel.Worksheet
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim path As String
Dim created As Boolean

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl\_excel")   ' votre source de données
path = Environ\$("HOMEDRIVE") & Environ\$("HOMEPATH") & "\Documents\tbl\_excel.xlsx"

On Error Resume Next
Set xl = GetObject(, "Excel.Application")
On Error GoTo 0
If xl Is Nothing Then
Set xl = CreateObject("Excel.Application")
created = True
End If

' Ouvrir ou créer le classeur cible
If Dir(path) = "" Then
Set wb = xl.Workbooks.Add
wb.SaveAs path
Else
Set wb = xl.Workbooks.Open(path)
End If
Set ws = wb.Worksheets(1)

' (Option) Nettoyer et écrire les données
ws.Cells.Clear
If Not rs.EOF Then
' Écrit la table d’un coup, plus rapide que cellule par cellule
ws.Range("A1").CopyFromRecordset rs
End If

' Lancer l’autofit APRES l’écriture
With ws
.Cells.WrapText = False          ' évite que le retour à la ligne bloque l’autofit
.UsedRange.Columns.AutoFit       ' colonnes
.UsedRange.Rows.AutoFit          ' lignes (si besoin)
End With

xl.Visible = True
ws.Activate
wb.Save

' Ménage
rs.Close
Set rs = Nothing: Set db = Nothing
' Laissez Excel ouvert pour l’utilisateur si vous l’avez rendu Visible

Points clés

Toujours manipuler des objets : Set wb = xl.Workbooks.Open(path) puis Set ws = wb.Worksheets(1). Évitez ActiveWorkbook/ActiveSheet tant que possible.
Autofit après écriture : exécuter AutoFit une fois les données en place.
Bonne portée : ciblez ws.UsedRange.Columns.AutoFit ou une plage précise (ex. ws.Columns("A:M").AutoFit).

Tutoriel pas à pas : Access → Excel avec AutoFit fiable

Récupérer/ouvrir Excel avec GetObject (si déjà ouvert) ou CreateObject (si absent), puis conserver la référence xl.
Ouvrir/Créer le classeur cible, conserver l’objet wb.
Récupérer la feuille de travail ws, clarifier la cible (première feuille, nom explicite, etc.).
Écrire les données via CopyFromRecordset (rapide), ou DoCmd.TransferSpreadsheet (voir variantes).
Appliquer AutoFit après l’écriture : colonnes, puis lignes si souhaité. Désactiver WrapText avant, le réactiver éventuellement après.
Sauvegarder le classeur, activer la feuille pour l’utilisateur, et nettoyer proprement les objets DAO/COM.

Code prêt à l’emploi : fonction « tout-en-un » robuste

Voici une fonction Access VBA réutilisable qui exporte une table ou une requête, ajoute les en-têtes, applique AutoFit et sécurise le nettoyage des objets.

Public Function ExporterAvecAutoFit( _
    ByVal TableOuRequete As String, _
    ByVal CheminFichier As String, _
    Optional ByVal NomFeuille As String = "Feuil1", _
    Optional ByVal LimiteLargeur As Double = 50, _
    Optional ByVal RendreExcelVisible As Boolean = True) As Boolean

```
Dim xl As Object ' Excel.Application
Dim wb As Object ' Excel.Workbook
Dim ws As Object ' Excel.Worksheet
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long, lastRow As Long, lastCol As Long
Dim aCreeExcel As Boolean
Dim plage As Object

On Error GoTo EH

Set db = CurrentDb
Set rs = db.OpenRecordset(TableOuRequete, dbOpenSnapshot)

' Lancer ou récupérer Excel
On Error Resume Next
Set xl = GetObject(, "Excel.Application")
On Error GoTo EH
If xl Is Nothing Then
    Set xl = CreateObject("Excel.Application")
    aCreeExcel = True
End If

' Ouvrir ou créer le classeur
If Dir(CheminFichier) = "" Then
    Set wb = xl.Workbooks.Add
    wb.SaveAs CheminFichier
Else
    Set wb = xl.Workbooks.Open(CheminFichier)
End If

' Récupérer/ajouter la feuille
On Error Resume Next
Set ws = wb.Worksheets(NomFeuille)
On Error GoTo EH
If ws Is Nothing Then
    Set ws = wb.Worksheets(1)
    ws.Name = NomFeuille
End If

' Ecrire headers + données
ws.Cells.Clear
If Not (rs.BOF And rs.EOF) Then
    ' En-têtes
    For i = 0 To rs.Fields.Count - 1
        ws.Cells(1, i + 1).Value = rs.Fields(i).Name
        ws.Cells(1, i + 1).Font.Bold = True
    Next i

    ' Données
    ws.Range("A2").CopyFromRecordset rs

    ' Déterminer la plage utile exacte
    lastRow = ws.Cells(ws.Rows.Count, 1).End(-4162).Row ' xlUp = -4162
    lastCol = rs.Fields.Count
    Set plage = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

    ' Préparer AutoFit
    ws.Cells.WrapText = False

    ' AutoFilter optionnel pour lisibilité
    plage.AutoFilter

    ' AutoFit colonnes + lignes
    plage.Columns.AutoFit
    plage.Rows.AutoFit

    ' Limite de largeur optionnelle
    If LimiteLargeur &gt; 0 Then
        For i = 1 To lastCol
            If ws.Columns(i).ColumnWidth &gt; LimiteLargeur Then
                ws.Columns(i).ColumnWidth = LimiteLargeur
            End If
        Next i
    End If
Else
    ws.Cells(1, 1).Value = "Aucune donnée."
End If

' Finalisation
xl.Visible = RendreExcelVisible
ws.Activate
wb.Save

ExporterAvecAutoFit = True
```

CleanExit:
On Error Resume Next
If Not rs Is Nothing Then rs.Close
Set rs = Nothing
Set db = Nothing

```
' Laisser Excel ouvert pour l'utilisateur si visible ou si on ne l'a pas créé
' Si vous voulez forcer la fermeture, testez aCreeExcel et fermez wb/xl ici.
Exit Function
```

EH:
ExporterAvecAutoFit = False
' Option : journaliser Err.Number & Err.Description
Resume CleanExit
End Function

Pourquoi ce code est fiable : chaque objet (Application, Workbook, Worksheet) est référencé explicitement ; l’écriture des données est faite d’un bloc (CopyFromRecordset) ; l’AutoFit n’est déclenché qu’après l’écriture ; le nettoyage d’objets est systématique pour éviter les instances « fantômes » d’Excel.

Variantes & alternatives utiles

Autofit ciblé + limite de largeur max

Dim c As Long
ws.UsedRange.Columns.AutoFit
For c = 1 To ws.UsedRange.Columns.Count
    If ws.Columns(c).ColumnWidth &gt; 50 Then ws.Columns(c).ColumnWidth = 50
Next c

Pratique quand des textes très longs « explosent » la mise en page : vous conservez un AutoFit global puis vous plafonnez.

Autofit simple par macro Excel « indépendante »

Sub AutoFitAllColumns()
    Cells.EntireColumn.AutoFit
End Sub

Exporter plus vite avec `DoCmd.TransferSpreadsheet`, puis AutoFit

Vous pouvez d’abord créer le fichier Excel depuis Access, puis l’ouvrir pour appliquer l’AutoFit :

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
    "tbl_excel", Environ$("USERPROFILE") & "\Documents\tbl_excel.xlsx", True

' Ensuite, ouvrez le fichier via Excel.Application et appliquez:
' ws.UsedRange.Columns.AutoFit : même logique que ci-dessus.

Autofit d’une plage nommée ou de colonnes précises

' Plage nommée
xl.Names.Add Name:="ZoneDonnees", RefersTo:=ws.Range("A1").CurrentRegion
xl.Range("ZoneDonnees").Columns.AutoFit

' Colonnes spécifiques
ws.Range("A\:A,D\:F").Columns.AutoFit

Performance et fiabilité : bonnes pratiques

Écriture en bloc : CopyFromRecordset est nettement plus rapide que les boucles cellule par cellule.
Références explicites : bannissez ActiveWorkbook/ActiveSheet hors débogage.
Affichage : vous pouvez accélérer en mettant xl.ScreenUpdating = False avant l’écriture, puis True après. Ne l’oubliez pas en cas d’erreur.
Formatage avant AutoFit : appliquer polices/tailles/format nombre avant l’AutoFit (la largeur dépend de la police).
Sauvegarde cohérente : si vous ciblez .xlsx, laissez Excel inférer le format via l’extension ; si vous spécifiez SaveAs avec un format, restez cohérent.
Grand volume : si la source peut dépasser 1 048 576 lignes, segmentez ou exportez en CSV.
Nettoyage DAO/COM : fermez Recordset, relâchez les objets (Set ... = Nothing). En cas de création d’Excel par votre code, décidez s’il faut fermer l’application à la fin.

Dépannage : symptômes courants et correctifs

Symptôme	Cause probable	Correctif
AutoFit n’a aucun effet	Exécuté avant l’écriture ou mauvaise portée (mauvaise feuille)	Déplacer l’AutoFit après `CopyFromRecordset` et cibler `ws` explicitement
Erreur « Objet requis »	Usage de `ActiveWorkbook.Name` (string) au lieu d’un `Workbook`	Stocker le `Workbook` : `Set wb = xl.Workbooks.Open(...)`
Texte ne s’élargit pas	`WrapText=True` ou cellules fusionnées	`WrapText=False` avant AutoFit ; dé-fusionner les cellules
AutoFit > largeur écran	Valeurs très longues	Plafonner `ColumnWidth` (ex. 50) après AutoFit
Agit sur « le mauvais » classeur	Plusieurs Excel ouverts, références implicites	Conserver `xl`, `wb`, `ws` et ne pas utiliser d’objets actifs
Lignes tronquées	Hauteurs fixées manuellement	`UsedRange.Rows.AutoFit` après écriture
Feuille protégée	Protection bloque l’ajustement	Déprotéger avant AutoFit, reprotéger ensuite

Snippets ciblés très utiles

AutoFit « double passe » (formatage → AutoFit)

Quand on applique une police plus large ou un format nombre spécifique, refaites un AutoFit ensuite.

With ws
    .Range("A1").CurrentRegion.NumberFormat = "@" ' exemple: texte
    .UsedRange.Columns.AutoFit
End With

AutoFit sur un bloc précis (plus rapide que UsedRange sur des feuilles « sales »)

Dim r As Object
Set r = ws.Range("A1").CurrentRegion ' ou votre plage exacte
r.Columns.AutoFit
r.Rows.AutoFit

Figer la première ligne (après AutoFit)

xl.ActiveWindow.SplitRow = 1
xl.ActiveWindow.FreezePanes = True

En-têtes lisibles + filtre auto

Dim lastCol As Long
lastCol = ws.Cells(1, ws.Columns.Count).End(-4159).Column ' xlToLeft = -4159
ws.Range(ws.Cells(1,1), ws.Cells(1,lastCol)).Font.Bold = True
ws.Range("A1").CurrentRegion.AutoFilter

Appliquer AutoFit puis plafonner certaines colonnes seulement

ws.Columns("A:D").AutoFit
If ws.Columns("C").ColumnWidth &gt; 35 Then ws.Columns("C").ColumnWidth = 35

Cas particuliers à connaître

Cellules fusionnées : l’AutoFit ignore la largeur « effective » des fusions. Dé-fusionnez ou ajustez manuellement.
Texte à la ligne (WrapText=True) : empêche l’élargissement. Désactivez avant AutoFit, réactivez si besoin après.
Formats conditionnels très denses : ils peuvent ralentir. Appliquez-les après AutoFit si possible.
Feuille protégée : ôtez la protection avant l’ajustement, puis remettez-la.
Données collées depuis le presse-papiers : certaines mises en forme peuvent « polluer » UsedRange. Nettoyez via ws.Cells.ClearFormats si nécessaire avant d’écrire.

Early-binding vs Late-binding

Le code ci-dessus utilise le late-binding (types Object), ce qui évite d’ajouter des références à « Microsoft Excel xx.0 Object Library ». Pour un développement interne contrôlé, vous pouvez préférer l’early-binding (intellisense, constantes nommées) :

' Nécessite d'ajouter la référence Excel dans Access (Outils > Références)
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet

Set xl = New Excel.Application
Set wb = xl.Workbooks.Add
Set ws = wb.Worksheets(1)

' ... même logique, avec constantes lisibles (xlUp, xlToLeft, etc.)

Exemple condensé « copier-coller »

Vous voulez le minimum viable pour tester ?

Sub Export_AutoFit_Minimal()
    Dim xl As Object, wb As Object, ws As Object
    Dim rs As DAO.Recordset
    Dim p As String

```
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_excel", dbOpenSnapshot)
Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Add
Set ws = wb.Worksheets(1)

ws.Range("A2").CopyFromRecordset rs
' En-têtes
Dim i As Long
For i = 0 To rs.Fields.Count - 1
    ws.Cells(1, i + 1).Value = rs.Fields(i).Name
    ws.Cells(1, i + 1).Font.Bold = True
Next i

ws.Cells.WrapText = False
ws.UsedRange.Columns.AutoFit
ws.UsedRange.Rows.AutoFit

p = Environ$("USERPROFILE") &amp; "\Documents\export_minimal.xlsx"
wb.SaveAs p
xl.Visible = True
```

End Sub

FAQ

Faut-il faire AutoFit avant ou après le formatage ?
Après. La largeur dépend de la police/du format. Si vous changez la mise en forme, refaites un AutoFit.

Pourquoi UsedRange ?
Il cible la zone réellement occupée (ou formatée). Sur des feuilles « polluées », préférez une plage exacte (CurrentRegion ou limites calculées).

Peut-on limiter la hauteur des lignes ?
Oui, comme pour les colonnes : If ws.Rows(r).RowHeight > 90 Then ws.Rows(r).RowHeight = 90.

Mon AutoFit est lent sur des fichiers lourds
Définissez une plage précise (évitez Cells global), coupez l’écran (ScreenUpdating=False) le temps du traitement, et évitez les formats conditionnels excessifs avant AutoFit.

Résultat

En corrigeant la référence au classeur (utiliser l’objet Workbook) et en exécutant AutoFit après l’écriture des données, l’ajustement des colonnes (et des lignes) devient fiable et reproductible. Les exemples fournis constituent une base solide : écriture en bloc (CopyFromRecordset), portée explicite (ws, wb, xl), gestion du WrapText, variantes de ciblage et plafonnement, et nettoyage rigoureux des objets pour éviter les effets de bord.

Récap express des bonnes pratiques

Références explicites : xl → wb → ws.
Écrire d’abord, puis AutoFit (colonnes & lignes si besoin).
Désactiver WrapText avant l’ajustement.
Cibler une plage précise quand c’est possible (plus rapide/fiable).
Limiter la largeur si nécessaire pour la lisibilité.
Nettoyer les objets et sauvegarder correctement.