[VBA] - Vitesse d'ouverture de document VS import BDD à l'ouverture

Bonsoir,

Pour faire fonctionner mon document de travail, j'ai besoin de quatre bases de données. L'une de ces 4 BDD comprend 180 000 lignes. Je vais travailler sur cette dernière pour réduire sa taille prochainement.

Les différentes macros qui s'exécutent sont réalisées en quelques millièmes de secondes, tout au plus quelques secondes. La gestion de la BDD ne me pose donc pas de soucis.

Là où j'essai d'optimiser mon document c'est dans sa vitesse d'ouverture et d'enregistrement.

J'ai constaté que l'ouverture du document prend environ 1 minute et l'enregistrement 30 secondes.

> Si je met en place une macro qui va rechercher la base de données adéquate et la charge dans mon document cela prend 30 secondes. L'ouverture du document quant à elle se fait en 5 secondes et l'enregistrement est quasi instantané. Car je supprime automatiquement la BDD à la fermeture du document.

> Si je copie-colle ma BDD dans mon document de travail, cela prend 15 secondes. (hors temps d'ouverture du document)

Ma question :

Est-ce que la solution de charger la BDD requise est une bonne solution selon vous ? Ou devrais-je continuer à conserver la (les) BDD dans mon document de travail ?

Sauriez-vous comment améliorer ma macro pour que le chargement de la BDD soit plus rapide ? Je constate qu'un copier-coller est plus rapide, alors j'ai espoir qu'il est possible d'obtenir des vitesse d'exécution de la macro similaire. Peut-être en ouvrant pas BDD pour l'import des données par exemple.

Mon code :

Private Sub CommandButton1_Click()
Dim lctc&
With ActiveSheet 'Tc
.Activate
    Call import_data
        If chk2 > 0 Then Call opt_fin: Exit Sub
'lctc = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
End Sub

Public Sub import_data()
chk2 = 0
    Dim fich, wbks As Workbook, fe$, wbkc As Workbook
    Set wbkc = ThisWorkbook: fe = ActiveSheet.Name
    fich = Application.GetOpenFilename("Fichiers Csv,*.csv")
    If Not fich = False Then
        Set wbks = Workbooks.Open(fich, local:=1)
        wbkc.Sheets(fe).Cells.Clear
        wbks.ActiveSheet.Cells.Copy wbkc.Sheets(fe).Cells
        wbks.Close 0
    Else
        MsgBox "Vous n'avez choisi aucun fichier", vbCritical, "Absence de sélection": chk2 = chk2 + 1
    End If
End Sub

Je joins un document pour illustrer le fonctionnement de la macro et un document csv (qui ne correspond pas à la réalité).

Pour info :

J'ai déjà essayé de travailler avec une BDD sur un autre document fermé, mais je ne peux pas exécuter tous les codes que je souhaite.

Merci de votre attention !

Bonne soirée

Bonjour,

Pensez-vous qu'il est possible d'enregistrer les données de la BDD dans un dictionnaire (ou une collection ?) (ça se passerait dans le document de la BDD en amont) et que le document de travail soit capable de simplement récupérer ce dictionnaire sans ouvrir la BDD ?

Je vais chercher si je trouve une solution de ce type. Notamment avec ce que m'avait déjà proposé Sleelson une fois.
https://www.excel-pratique.com/fr/telechargements/utilitaires/collecter-donnees-fiches-individuelles-no478

Bonne journée !

Bonjour,

Petite mise à jour :

J'ai fait une macro qui récupère les infos d'une feuille pour les mettre dans un dictionnaire et les rapatrier sur une autre feuille, de la manière la plus rapide que je connaisse. C'est instantané pour les 110 lignes x 86 colonnes.

En revanche pour les 185 000 lignes x 86 colonnes, c'est extrêmement lent. Je ne pense pas que ce soit une bonne idée de passer par un dictionnaire dans ce cas.

Voici le code :

Option Explicit
Dim lrbd&, lcbd&, dict1 As Object

Sub Cre_dic()
Dim i&, tablo1

Set dict1 = CreateObject("scripting.dictionary")

With Sheets("BDD")
lrbd = .Cells(.Rows.Count, 1).End(xlUp).Row: lcbd = .UsedRange.Columns.Count
    tablo1 = .Range(.Cells(1, 1), .Cells(lrbd, lcbd))
    For i = LBound(tablo1) To UBound(tablo1)
        dict1(i) = tablo1(i, 1) & ";," & tablo1(i, 2) & ";," & tablo1(i, 3) & ";," & tablo1(i, 4) & ";," & tablo1(i, 5) & ";," & _
        tablo1(i, 6) & ";," & tablo1(i, 7) & ";," & tablo1(i, 8) & ";," & tablo1(i, 9) & ";," & tablo1(i, 10) & ";," & _
        tablo1(i, 11) & ";," & tablo1(i, 12) & ";," & tablo1(i, 13) & ";," & tablo1(i, 14) & ";," & tablo1(i, 15) & ";," & _
        tablo1(i, 16) & ";," & tablo1(i, 17) & ";," & tablo1(i, 18) & ";," & tablo1(i, 19) & ";," & tablo1(i, 20) & ";," & _
        tablo1(i, 21) & ";," & tablo1(i, 22) & ";," & tablo1(i, 23) & ";," & tablo1(i, 24) & ";," & tablo1(i, 25) & ";," & _
        tablo1(i, 26) & ";," & tablo1(i, 27) & ";," & tablo1(i, 28) & ";," & tablo1(i, 29) & ";," & tablo1(i, 30) & ";," & _
        tablo1(i, 31) & ";," & tablo1(i, 32) & ";," & tablo1(i, 33) & ";," & tablo1(i, 34) & ";," & tablo1(i, 35) & ";," & _
        tablo1(i, 36) & ";," & tablo1(i, 37) & ";," & tablo1(i, 38) & ";," & tablo1(i, 39) & ";," & tablo1(i, 40) & ";," & _
        tablo1(i, 41) & ";," & tablo1(i, 42) & ";," & tablo1(i, 43) & ";," & tablo1(i, 44) & ";," & tablo1(i, 45) & ";," & _
        tablo1(i, 46) & ";," & tablo1(i, 47) & ";," & tablo1(i, 48) & ";," & tablo1(i, 49) & ";," & tablo1(i, 50) & ";," & _
        tablo1(i, 51) & ";," & tablo1(i, 52) & ";," & tablo1(i, 53) & ";," & tablo1(i, 54) & ";," & tablo1(i, 55) & ";," & _
        tablo1(i, 56) & ";," & tablo1(i, 57) & ";," & tablo1(i, 58) & ";," & tablo1(i, 59) & ";," & tablo1(i, 60) & ";," & _
        tablo1(i, 61) & ";," & tablo1(i, 62) & ";," & tablo1(i, 63) & ";," & tablo1(i, 64) & ";," & tablo1(i, 65) & ";," & _
        tablo1(i, 66) & ";," & tablo1(i, 67) & ";," & tablo1(i, 68) & ";," & tablo1(i, 69) & ";," & tablo1(i, 70) & ";," & _
        tablo1(i, 71) & ";," & tablo1(i, 72) & ";," & tablo1(i, 73) & ";," & tablo1(i, 74) & ";," & tablo1(i, 75) & ";," & _
        tablo1(i, 76) & ";," & tablo1(i, 77) & ";," & tablo1(i, 78) & ";," & tablo1(i, 79) & ";," & tablo1(i, 80) & ";," & _
        tablo1(i, 81) & ";," & tablo1(i, 82) & ";," & tablo1(i, 83) & ";," & tablo1(i, 84) & ";," & tablo1(i, 85) & ";," & _
        tablo1(i, 86) '& ";," & tablo1(i, 87) & ";," & tablo1(i, 88) & ";," & tablo1(i, 89)
    Next i
End With
End Sub

Sub col_dic()
Dim a&, b&, tab1, txt$, tb() As String, pos&

With Sheets("Coller")
ReDim tab1(1 To lrbd, 1 To 1)
    For a = 1 To dict1.Count
         tab1(a, 1) = dict1(a)
            txt = tab1(a, 1)
            tb() = Split(txt, ";,")
            ReDim Preserve tab1(1 To lrbd, 1 To lcbd)
                For b = 1 To UBound(tb)
                    tab1(a, b) = tb(b)
                Next b
    Next a
    .Cells(1, 1).Resize(lrbd, lcbd) = tab1
End With
End Sub

Je n'ai pas su remplir le dictionnaire avec toutes les colonnes plus simplement qu'en les renseignant toutes...

Bonne journée !

Autre essai en important les données depuis un document fermé.

Le code utilisé :

source = ThisWorkbook.Path & "\Bases de données\"
fichier = "Base_de_données_FLORE.csv"
    ThisWorkbook.Names.Add "plage", _
            RefersTo:="='" & source & "[" & fichier & "]Base_de_données_FLORE'!$A$1:$CH$185900"
    With bd
        .Range("A1:CH185900").Value = "=plage"
    End With
End Sub

Je constate que seules les 7 premières lignes sont importées et les 6 premières colonnes...

Et plus surprenant encore, si j'ouvre ma base de données, alors les " #REF! " sont transformées en données. Mais je ne peux plus rien fermer et tout finit par buguer et s'arrêter.

Pourtant ça n'est pas que j'indique dans la macro..

Et je n'ai pas non plus encore réussi à trouver le moyen d'identifier la dernière ligne utilisée d'un document fermé.

Bonne journée !

Bonjour Le drosophile le forum

une base de 185000 lignes c'est de la rigolade normalement!!

c'est quoi ton document un csv? un xlsx?

ton document est plein de formules??

pour moi un fichier qui met comme tu le dis 1 minutes pour s'ouvrir, si c'est une feuille avec des données, même avec 100 colonnes cela devrait être bien plus rapide que cela

si ton fichier n'a rien de perso ou de confidentiel, passe le STP que je teste chez moi.

si du perso et du confidentiel, je te passe une adresse mail en MP

a+

Papou

Bonjour,

Une proposition Power Query ?

On peut traiter le csv avant restitution dans la feuille (suppression lignes, colonnes inutiles, filtrer, etc...)

Cdlt.

Public Sub import_data()
    Dim fich
    If Not Range("bdd").ListObject.DataBodyRange Is Nothing Then Range("bdd").ListObject.DataBodyRange.Delete
    fich = Application.GetOpenFilename("Fichiers Csv,*.csv")
    If Not fich = False Then
        Range("ChoixCSV").ListObject.Range.Cells(2, 1).Value = fich
        ThisWorkbook.RefreshAll
    Else
        MsgBox "Vous n'avez choisi aucun fichier", vbCritical, "Absence de sélection"
    End If
End Sub

Bonjour Jean Eric le drosophile le forum

c'est bien là que je voulais arriver, mais je vais suivre à la lettre ce que tu vas lui faire

a+

Papou

Bonjour,

Le fichier est un regroupement de plusieurs bases de données libres d'accès. J'ai travaillé à ce qu'elles soient toutes sur la même base pour pouvoir travailler avec, voici donc le lien vers ce document (.csv). Il n'y a pas de formule, requêtes ou autres, a priori.

Ça fait tout de même 15 000 000 de cellules à traiter. J'ai un processeur "Intel Core I5" / 8 Go de Ram ; ce n'est pas une bête de puissance.

Le fichier compressé fait 8mo et je ne peux pas utiliser cjoint...

Je vais aller tester le code que vous proposez. Le document importé n'a pas besoin d'être modifié, il est déjà prêt à être utilisé.

Merci pour votre aide

Re,

J'ai finalement une erreur lorsque j'exécute la macro en question. En général dès que je mélange VBA et PowerQuery j'ai des erreurs et beaucoup de problèmes de fonctionnement (comme les requêtes qui ne s'exécutent plus si on efface le contenu d'une feuille).

Re,

Que ce soit avec VBA ou Power Query, il faut être très rigoureux !...

As-tu testé avec le csv que tu as joint ? N'as-tu pas pris un autre fichier csv, qui n'aurait pas un même nombre de colonnes ou encore des en-têtes différents ?

Cdlt.

Que ce soit avec VBA ou Power Query, il faut être très rigoureux !

En effet ! Toutefois, avec VBA, je m'assure généralement de bien cadrer les opérations à réaliser et j'essaie de prendre en compte les différentes possibilités.

Avec PowerQuery, si la taille de la Base de données change, alors il faut reprendre le code (en VBA il suffit de lui indiquer de rechercher la dernière colonne par exemple).

J'ai comparé les deux documents, ils sont identiques en noms et en nombre de colonnes (le nombre de ligne est de 185 961).

Re,

Le fichier csv que tu as joint comporte que 110 lignes et 86 colonnes.

Ta procédure VBA traite 89 colonnes alors que tu indiques que ton fichier comporte 86 colonnes !?

Et tu écris :

Le fichier est un regroupement de plusieurs bases de données libres d'accès. J'ai travaillé à ce qu'elles soient toutes sur la même base pour pouvoir travailler avec...

Alors, que penser ?

Cdlt.

Re,

Ma base de données fait bien 86 colonnes (et seules 86 sont traitées) j'ai laissé la partie de la macro qui traite les colonnes 87-89 désactivée, sans trop réfléchir, plutôt que de la supprimer.

Pour les tests j'ai essayé avec deux documents :

- le document "bdd" que j'ai transmis sur le forum (110 lignes 86 colonnes).

- la bdd réelle (185 000 lignes 86 colonnes).

J'ai le même message d'erreur. (Ou alors je ne sais pas comment utiliser votre document ; en l'occurrence, je vais dans la console VBA et je lance votre macro).

Le fichier est un regroupement de plusieurs bases de données libres d'accès. J'ai travaillé à ce qu'elles soient toutes sur la même base pour pouvoir travailler avec...

Ça c'est en amont, je répondais à la question sur la confidentialité du document. Il s'agit de bases de données récupérées sur des sites officiels sur internet que j'ai regroupé. Mais ça n'a pas d'incidence sur mon problème d'import, l'import ne se fait que sur 1 seule et même bdd.

Bonne journée.

Bonjour,

Passer par PowerQuery pour charger une feuilles de données est très efficace, par contre j'ai systématiquement des problème avec cette solution.

Je ne sais pas charger les données dans la feuille que je veux, si je supprime la feuille, alors la procedure PowerQuery ne fonctionne plus...

Dans mon cas, à la fermeture du document la bdd importée est supprimée. Elle est à nouveau recherchée si, à la prochaine ouverture, on en a besoin. C'est alors une macro qui décide si on en a besoin ou pas et qui va la rechercher dans le même dossier où se trouve mon document de travail (et les différentes autres bases de données).

Si PowerQuery est en mesure d'aller chercher systématiquement la bdd dans le document souhaité (thisworkbook.path) et que la bdd est chargée dans une feuille qui vient d'être créé (avec un nom prédéfini) alors ce pourrait être vraiment intéressant. J'avais essayé de le faire par le passé, sans succès.

La bdd peut être enregistrée au format .txt si c'est plus rapide à charger.

Je reessaierai avec PowerQuery si je trouve une solution du genre..

Bonne journée !

Bonsoir,

Pour le moment je n'ai pas trouvé de solution pour résoudre l'erreur qui s'affichait. Et je ne trouve rien qui permette de charger les données systématiquement dans une feuille prédéfinie et nouvellement générée.

une base de 185000 lignes c'est de la rigolade normalement!!

Vous m'avez donné l'espoir maintenant ! Haha

Mon ordinateur, qui n'est pas une foudre de guerre, met 1 minute à ouvrir le document quand la BDD se trouve à l'intérieur.

Peut-être que si j'enregistre cette dernière au format .txt, ce sera plus rapide pour la lecture.

Mes deux solutions ne me conviennent pas totalement pour le moment car :

- soit le document met 1min à s'ouvrir et 30 sec à chaque enregistrement [EDIT : Même durée que je passe par un .csv ou un .txt]

- soit j'exécute une macro après chaque ouverture, qui charge les données en plus ou moins longtemps selon les tests (> 28sec - 120sec <).

Mon document se destine à différentes personnes qui pourraient avoir des utilisations différentes. J'ai mis en place un système qui permet de réinitialiser le document à chaque fermeture (avec suppression des feuilles inutiles notamment ; dont les bases de données).

Si je trouve quelque chose je reviendrai vers vous. Si vous avez des idées, même des pistes, je suis preneur.

Bonne soirée !

Bonjour,

Voici un exemple qui charge une base de données en utilisant une requête PowerQuery.

Si on supprime la feuille "TAXREF" alors la requêtes est désactivée (ce qui pose un gros problème dans mon cas).

La vitesse de chargement est des données est très chronophages (plusieurs minutes ; jusqu'à 10minutes).

Je joins le document ainsi qu'une bdd de 300lignes si vous souhaitez y jeter un œil.

A chaque fois que j'ai essayé d'utiliser PowerQuery, j'en suis revenu, car il s'arrête systématiquement de fonctionner si la base de données change ou si on supprimer la feuille. D'autant que les temps de chargement sont visiblement beaucoup plus longs qu'avec VBA dans mon cas.

Bonne fin de journée !