Microsoft 365 Ouvrir un fichier Excel rapidement en VBA

iliess

XLDnaute Occasionnel
Je suis actuellement chargé de la gestion comptable sur un logiciel de comptabilité qui me donne la capacité de générer des états financiers sous forme de fichiers Excel. Cependant, un inconvénient notable est la lenteur d'ouverture de ces rapports.
Est-il possible de réduire le temps d’ouverture du classeur en VBA en important, par exemple, l’intégralité de la feuille 1 sans ouvrir le fichier source pour un traitement personnalisé ?
Cordialement.

1712352797545.png


Est-il possible de joindre mon fichier de démonstration qui fait 12 Mo en taille ?
 
Dernière édition:
Solution
Voici un code ("Power Query") avec un temps de 6,48 secondes.
VB:
sub Macro1()
Dim t
t = Timer
    ActiveWorkbook.Queries.Add Name:="JournalAuxExcel", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""C:\Users\ilies\Desktop\JournalAux.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & "    JournalAuxExcel_Sheet = Source{[Item=""JournalAuxExcel"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(JournalAuxExcel_Sheet,{{""Column1"", type any}, {""Column2"", type any}, {""Column3"", type text}, {""Column4"", type" & _
        " text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type any}, {""Column10""...

Laurent78

XLDnaute Occasionnel
Bonjour à Tous,

Je viens de faire un petit test.
J'ai ouvert le fichier, cela prend énormément de temps en effet, pour 200000 lignes et 11 colonnes environ, c'est très très long. Je travaille sur des données ayant plus de lignes/colonnes, ce n'est pas si long à ouvrir.
Une fois ouvert, j'ai supprimé tous les formats (sélection de la feuille entière, puis "effacer les formats".
Le résultat obtenu n'a plus rien à voir avec le fichier d'origine, l'ouverture est quasi instantanée.
Conclusion : le fichier Excel fourni par l'appli comptable est, comment dire, bof bof .... bourré de fusion entre autre.

Idées :
- est-il possible de choisir un autre format d'exportation ? Existe-t-il des API ?
- est-il envisageable de faire une première ouverture, supprimer les mise en forme et travailler sur cette copie ?
- est-il envisageable de faire une première ouverture et d'enregistrer la feuille en format csv ?

NB : le fichier, pour moi, n'est pas un état financier, mais un export d'un grand livre comptable (auxiliaire dans le cas présent).

@iliess : quel est le but final recherché ?
Bon après midi.
 

iliess

XLDnaute Occasionnel
bonjour @Laurent78
Mon but final est d'ouvrir ce fichier dans le délai le plus court possible, parce que j'ai plusieurs fichiers à ouvrir par jour.
Il n'est pas approprié de lancer un fichier et de rester les bras croisés pendant 10 minutes ou plus en attendant qu'il s'ouvre.
 

Laurent78

XLDnaute Occasionnel
bonjour @Laurent78
Mon but final est d'ouvrir ce fichier dans le délai le plus court possible, parce que j'ai plusieurs fichiers à ouvrir par jour.
Il n'est pas approprié de lancer un fichier et de rester les bras croisés pendant 10 minutes ou plus en attendant qu'il s'ouvre.
J'entendais pour "but final" : "pour en faire quoi"; je suppose que le but final n'est pas juste les ouvrir ;)
En plus si c'est pour traiter plusieurs fichiers, je vois Power Query en embuscade ! 🤣

Ne peut on pas exporter dans un autre format ?
 

iliess

XLDnaute Occasionnel
J'entendais pour "but final" : "pour en faire quoi"; je suppose que le but final n'est pas juste les ouvrir ;)
En plus si c'est pour traiter plusieurs fichiers, je vois Power Query en embuscade ! 🤣

Ne peut on pas exporter dans un autre format ?
c'est la seul format
Bien sûr, le but final n'est pas juste de les ouvrir.
je suis un financier Mr et un grand travail après l'ouverture
 

iliess

XLDnaute Occasionnel
Voici un code ("Power Query") avec un temps de 6,48 secondes.
VB:
sub Macro1()
Dim t
t = Timer
    ActiveWorkbook.Queries.Add Name:="JournalAuxExcel", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""C:\Users\ilies\Desktop\JournalAux.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & "    JournalAuxExcel_Sheet = Source{[Item=""JournalAuxExcel"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Type modifié"" = Table.TransformColumnTypes(JournalAuxExcel_Sheet,{{""Column1"", type any}, {""Column2"", type any}, {""Column3"", type text}, {""Column4"", type" & _
        " text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type any}, {""Column10"", type datetime}, {""Column11"", type any}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Type modifié"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=JournalAuxExcel;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [JournalAuxExcel]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "JournalAuxExcel"
        .Refresh BackgroundQuery:=False
    End With
MsgBox "Durée " & Format(Timer - t, "0.00 \sec")
End Sub

Dans ce code, le chemin du fichier est fixe. Comment je peux modifier le choix du fichier ?
 

Laurent78

XLDnaute Occasionnel
Je ne suis pas ici pour perdre votre temps en ouvrant le fichier.
je n'ai pas compris.

Je suggère de faire une macro qui ouvre les fichiers d'un dossier et les enregistre ensuite soit dans un format csv, soit en format Excel après avoir supprimé toutes les mises en forme. Ensuite utiliser Power Query pour la travail sur les fichiers du dossier. En procédant ainsi, il y a juste un premier travail automatique qui peut être long, mais qui se fait tout seul, pas besoin de rester le nez sur le PC et ensuite, Power Query sera performant.
 

Laurent78

XLDnaute Occasionnel
c'est la seul format
Bien sûr, le but final n'est pas juste de les ouvrir.
je suis un financier Mr et un grand travail après l'ouverture
Pas d'API, pas de serveur SQL ou un truc du genre ?
Quel est ce logiciel de comptabilité ?

Ce qui m'énerve, c'est de devoir trouver, en 2024, une solution de contournement parce que le logiciel de comptabilité n'est pas capable de fournir un export propre. Quel dommage. Bon courage à vous.
 

iliess

XLDnaute Occasionnel
Pas d'API, pas de serveur SQL ou un truc du genre ?
Quel est ce logiciel de comptabilité ?

Ce qui m'énerve, c'est de devoir trouver, en 2024, une solution de contournement parce que le logiciel de comptabilité n'est pas capable de fournir un export propre. Quel dommage. Bon courage à vous.
Le logiciel de comptabilité n'est pas exécutable en travail directement dans le web Mozilla avec un nom d'utilisateur et un mot de passe.
 

Cousinhub

XLDnaute Barbatruc
Bonjour,
En modifiant le début du code ainsi :
VB:
....
Dim Fichier As String
Fichier = Application.GetOpenFilename("XL* Files (*.xls;*.xlsm;*.xlsx), *.xls;*.xlsm;*.xlsx", 1, "ouvrir un fichier")
If Fichier = "" Then Exit Sub
ActiveWorkbook.Queries.Add Name:="JournalAuxExcel", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""" & Fichier & """), null, true)," & Chr(13) & "" & Chr(10) & "    JournalAuxExcel_Sheet = Source{[Item=""JournalAuxExcel"",Kind=""Sheet""]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    JournalAuxExcel_Sheet"
ActiveWorkbook.Worksheets.Add
....
....
Si le nom de l'onglet peut être différent, et qu'il n'y a qu'un seul onglet, tu peux supprimer la partie Item=""JournalAuxExcel"", le code deviendrait alors :
VB:
....
Dim Fichier As String
Fichier = Application.GetOpenFilename("XL* Files (*.xls;*.xlsm;*.xlsx), *.xls;*.xlsm;*.xlsx", 1, "ouvrir un fichier")
If Fichier = "" Then Exit Sub
ActiveWorkbook.Queries.Add Name:="JournalAuxExcel", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""" & Fichier & """), null, true)," & Chr(13) & "" & Chr(10) & "    Import = Source{[Kind=""Sheet""]}[Data]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Import"
ActiveWorkbook.Worksheets.Add
....
....
 

Discussions similaires

Réponses
3
Affichages
320

Membres actuellement en ligne

Statistiques des forums

Discussions
313 259
Messages
2 096 618
Membres
106 688
dernier inscrit
Cherif99