Microsoft 365 Ouvrir un fichier Excel rapidement en VBA

[iliess]

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.

Est-il possible de joindre mon fichier de démonstration qui fait 12 Mo en taille ?

 

[job75]

En gros ce sont C : D et I : J de chaque ligne.

Tu as raison, j'avais mal regardé.

 

[Laurent78]

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.

 

[TooFatBoy]

Tu as raison, j'avais mal regardé.

Faut dire qu'avec une colonne D de 0,5 de largeur, il fallait la voir.

 

[iliess]

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]

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]

Je travaille sur un code VBA et le Power Query et ça me donne un très bon résultat, mais le code n'est pas fini.

 

[TooFatBoy]

Ne peut-on pas exporter dans un autre format ?

Voilà une question qu'elle est bonne.

 

[iliess]

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]

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 ?

Je ne suis pas ici pour perdre votre temps en ouvrant le fichier.

 

[iliess]

Voici un code ("Power Query") avec un temps de 6,48 secondes.

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]

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]

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]

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]

Bonjour,
En modifiant le début du code ainsi :

....
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 :

....
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
....
....

 

[Cousinhub]

Re-,
Petite erreur, s'il n'y a pas de sélection...
Remplace par :

If Fichier = "Faux" Then Exit Sub