[AC-2003] Génération fichier Excel via formulaire: Erreur 1004 sur Calculation=xlCalculationManual [Résolu]

[thomas.m]

Bonjour

Dans ma base access 2003, j'utilise un formulaire pour générer un fichier Excel assez lourd (contenant beaucoup de données, de formules, de mise en forme, de feuilles etc. En gros, un fichier de 1 Mo) grâce à du VBA.
Sans optimisation, il fallait 33 secondes à mon dual core pour générer le fichier.
En utilisant exc.Calculation = xlCalculationManual et exc.ScreenUpdating = False je passe à 8 sec pour la génération, ce qui n'est vraiment pas négligeable.
Le problème est qu'exactement une fois sur deux, la ligne
exc.Calculation = xlCalculationManual
me génère une Erreur d'exécution '1004': La méthode 'Calculation' de l'objet '_Application' a échoué. (exc.Calculation = <Incompatibilité de type>)

J'ai effectué pas mal de recherches, et j'ai essayé les méthodes suivantes:

• Créer le workbook avant
• Sélectionner un Range avant

Mais rien n'a faire, une fois sur deux l'erreur reviens. J'ai aussi remarqué qu'après la génération de mon fichier, dans les processus, EXCEL était toujours présent.
Ici les principales lignes de mon code (début / fin ):

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Private Sub CreerFichierContraintes_Click()
    Dim exc As New Excel.Application
    Set exc = CreateObject("Excel.Application")
    Workbooks.Add 'rajoute trois feuilles
    exc.ScreenUpdating = False
    'On Error Resume Next
    Range("A1").Select
    exc.Calculation = xlCalculationManual   '--> Erreur 1004 une fois sur deux.
 
    'Manipulations sur le fichier ...
    '800 lignes plus tard:
 
    exc.Calculation = xlCalculationAutomatic
    exc.ScreenUpdating = True
    ActiveWorkbook.SaveAs Filename:=file, FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
    , CreateBackup:=False
    ActiveWorkbook.Close
    exc.Quit
 
End Sub

En rajoutant Application.Close, cela me ferme Access :/
Comment: éviter d'avoir des processus Excel qui s'accumulent, et comment éviter cette erreur une fois sur deux?

Merci beaucoup d'avance

[marot_r]

T'es-tu assuré que ton Excel était visible avant d'envoyer le .quit ? Si tu ne fais pas cela, le Excel peut rester caché en mémoire.

Note qu'il peut être très dificile de fermer Excel à partir de Access. Il y a pluseiurs postes sur ce site qui traite du problème. Parfois la seule, et bien triste, solution c'est de 'tuer' le process Excel qui est en mémoire.

Ton problème, c'est systématiquement une fois sur deux ou en moyenne une fois sur deux ?

Une solution serait peut-être de faire les traitements Excel à partir de Excel. C-à-d de créer une macro Excel et de l'exécuter avec une commande shell "Excel". Ça peut être un peu accrobatique mais cela évite d'avoir un pointeur vers Excel dans ton Access.

A+

[pgz]

Bonsoir.

Pour les processus qui s'accumulent, je crois que tu ouvres 2 fois Excel : ici

Code :

Dim exc As New Excel.Application

puisque tu as utilisé le mot New, et là

Code :

Set exc = CreateObject("Excel.Application")

Perso, je préfère

Code :

1
2
3
Dim xlApp as Excel.Application
...
Set xlApp = New Excel.Application

Ensuite, il faut être plus précis comme

Code :

Set oWbl = xlApp.Workbooks.Add

et

Code :

oWbk.Worksheets("NomFeuille").Range("A1") ...

Corrige et regarde si l'erreur aléatoire persiste.

Cordialement,

PGZ

[LedZeppII]

Bonsoir,

Je suis d'accord avec PGZ

Envoyé par pgz

Ensuite, il faut être plus précis comme

Code :

Set oWbl = xlApp.Workbooks.Add

et

Code :

oWbk.Worksheets("NomFeuille").Range("A1") ...

Une syntaxe explicite est préférable lorsqu'on fait de l'automation, sinon on se heurte à des problèmes de libération de variables objets, qui ont pour conséquence de laisser le processus actif en mémoire.
Conseil allant dans le même sens ici.
Une syntaxe explicite signifie que l'on part d'une variable objet, pour arriver au membre (méthode ou propriété) souhaité.
Par exemple cette ligne n'est pas bonne parce qu'elle commence directement par une méthode, sans préciser à quel variable objet elle se rapporte.

Code :

    Range("A1").Select

A+

[thomas.m]

Bonjour,

Tout d'abord, merci pour vos réponses.

C'est bien exactement une fois sur deux que l'erreur apparait.
Quand je génère un première fichier Excel, tout marche bien, mais un EXCEL reste dans les processus. Je génère un second fichier, et là dès le début, erreur. Un second processus Excel est généré. Je termine la sub et je reclique sur mon bouton pour regénérer ce deuxième fichier. Là pas d'erreur. Un troisieme processus est créé... et ainsi de suite.

Sinon non, excel n'est pas visible ... Et j'ai besoin d'effectuer toutes mes manipulations dans access et non dans une macro car je formate et j'injecte des valeurs en fonction de résultats de recordsets.

Quant au Set exc = CreateObject("Excel.Application"), l'erreur est présente avec ou sans.

Je suis actuellement en train de revoir tout mon code pour régler ce problème de référence dont j'étais conscient mais qui me facilitait le codage.
Là je remplace tout avec des With WBl.Worksheets(i) ..

Je vous tiens au courant,

Merci encore

[thomas.m]

Super, ça marche à chaque coup sans grincher, et les processus Excel disparaissent à la fin de la sub. Et je passe même de 8s à 7s pour générer mon fichier.

Merci à vous deux de m'avoir mis sur la piste.

Voilà comment j'ai structuré mon code pour corriger ce problème:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
    Dim xlApp As Excel.Application
    Dim oWbl As Excel.Workbook
    Set xlApp = New Excel.Application
    Set oWbl = xlApp.Workbooks.Add
    xlApp.ScreenUpdating = False
    xlApp.Calculation = xlCalculationManual
 
    "[.....]"
 
    For i = 0 To UBound(Onglets)
        With oWbl.Worksheets(Onglets(i))
            .Cells( etc...
        End With
    Next i
 
    xlApp.Calculation = xlCalculationAutomatic
    xlApp.ScreenUpdating = True
    oWbl.SaveAs Filename:=file, FileFormat:= _
    xlNormal, Password:="", WriteResPassword:="",     ReadOnlyRecommended:=False _
    , CreateBackup:=False
    oWbl.Close
    xlApp.Quit
    DoCmd.SetWarnings True
 
End Sub