Discussion :

[ninouchfis]

Bonjour à tous,

Je développe un petit programme d'import de données permettant de faire des analyses.
A partir d'un fichier Excel, j'importe des données provenant de différentes bases (SQL_server, oracle) à partir d'une requête MS query.
Jusque la tout fonctionne très bien, seulement plus la quantité de données est importante, plus le processus excel utilise de la mémoire tampon et au bout d'un certain temps d'utilisation la capacité mémoire est pleine, d'où le message d'erreur "Mémoire insufisante" et d'où le plantage d'excel.
Ma première question est de savoir s'il existe une solution VBA, autre que redémarrer Excel, pour remettre à zéro la mémoire utilisée par le logiciel.
J'aimerais aussi savoir si le fonctionnement est le même pour les versions excel ultérieurs à 2000.

Merci par avance

[ninouchfis]

Toujours pas d'idées?

J'ai fait une petite procédure qui me prévient quand la mémoire commence à être trop importante. Ceci me permet d'éviter de planter Excel sans pouvoir rien faire et risquer de perdre des données.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
' Variable contenant la capacité de la mémoire libre
    Memoire_libre = Application.MemoryFree
' Variable contenant la capacité totale de la mémoire
    Capacite_memoire = Application.MemoryTotal
' Calcul du rapport de la mémoire restante sur la mémoire totale
    Rapport_memoire = Memoire_libre / Capacite_memoire * 100
 
' S'il reste moins de 3% de mémoire alors message d'avertissement
    If Rapport_memoire <= 3 Then
        reponse = MsgBox("Attention, la mémoire tampon Excel arrive bientôt à saturation!" & _
        Chr(10) & "Il est vivement conseillé de redémarrer l'application Excel pour éviter" & _
        " un plantage du processus!" & Chr(10) & Chr(10) & "Souhaitez vous  quand même continuer?", vbYesNo)
 
' En fonction de la réponse, poursuite du code ou sortie du code
        If reponse = vbNo Then
            Exit Sub
        End If
 
    End If
 
' Si vbYes alors lancement du code
    Call MonCode

Ceci n'est qu'une solution provisoir mais ne constitue pas une résolution de mon problème. Je suis donc toujours preneur de toutes vos idées.

Merci d'avance

[Invité]

Bonjour,

Vous avez dû affecter des objets avec Set XXXXX = YYYYY
sans jamais détruire l'espace mémoire occupé dans la pile. Pour cela, il vous faut détruire vos objets avec Set XXXXX = Nothing dès que vous n'avez plus besoin de l'objet.

Astuce : Arrangez-vous pour que l'objet déclaré dans une procédure soit détruit dans la même procédure.

Cordialement.

[ninouchfis]

En réalité je n'affecte aucun objet avec Set XXXXX = YYYYY

Voici un exemple de code que j'utilise et qui monopolise beaucoup de mémoire:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
connstring=mes données de connection
sqlstring=ma requete SQL
range_insertion = "A1"
 
' Création et éxécution de la requête SQL à partir de MSQuery
    With ActiveSheet.QueryTables.Add(Connection:=connstring, _
            Destination:=Range(range_insertion), Sql:=sqlstring)
        .Refresh BackgroundQuery:=False
    End With

Et bien sur plus il y a de données, plus la mémoire utilisée est importante.
Je peux bien sur optimiser ma requête pour minimiser l'utilisation mémoire mais quoi qu'il en soit plus je répète ma requête, plus la mémoire utilisée augmente car elle s'incrémente sans jamais revenir à son état initial...

[Invité]

En réalité si puisque vous créez implicitement un nouvel objet avec .Add

Voici ce que j'ai récupéré de l'aide VBA Microsoft pour QueryTables.Add

Cet exemple permet d'importer un fichier texte à largeur fixe dans une nouvelle table de requêtes. La première colonne du fichier texte a une largeur de cinq caractères et elle est importée en tant que texte. La deuxième colonne a une largeur de quatre caractères et elle est ignorée. Le reste du fichier texte est importé dans la troisième colonne et elle a le format Standard.

Visual Basic pour Applications
Set shFirstQtr = Workbooks(1).Worksheets(1)
Set qtQtrResults = shFirstQtr.QueryTables.Add( _
Connection := "TEXT;C:\My Documents\19980331.txt",
Destination := shFirstQtr.Cells(1,1))
With qtQtrResults
.TextFileParsingType = xlFixedWidth
.TextFileFixedColumnWidths := Array(5,4)
.TextFileColumnDataTypes := _
Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)
.Refresh
End With

Je pense qu'en déclarant une variable

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Public QtQtrResults As QueryTable

Et en l'affectant comme indiqué plus haut et en la supprimant en fin de procédure avec un

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Set QtQtrResults  = Nothing

Vous devriez régler votre problème.

Cordialement.