[Automation] Erreur Extraction de données depuis un fichier Excel

**raph04** · 27/04/2010, 09h54

Bonjour,

J'ai une base de donnée Access de laquelle je souhaite faire une extraction de mes données en les présentant de façon plus visuel sous Excel.
Mon code fonctionne parfaitement lorsque je lance une première extraction.

Cependant quand je relance la même extraction, sur la même date, rien ne change, le code est exécuté de la même façon, je me retrouve face à une erreur d'exécution '1004' (donc la bien classique qui aide pas trop...) :
"La méthode 'Range' de l'objet '_Worksheet' a échoué."

Je regarde dans les processus, il y a un processus Excel qui n'est pas visible. Si je le tue manuellement, alors je pourrais relancer une extraction qui fonctionnera. A la suite de cette extraction qui fonctionne, je quitte l'Excel qui m'a été ouvert pour cette extraction. J'ai cependant encore un processus Excel qui tourne sans être visible, sans même avoir lancer une nouvelle extraction.

Je pense donc que le problème vient de ce processus cacher, mais je ne sais pas, soit comment le tuer depuis la macro (mais que dans le cas ou il existe), soit comment éviter qu'il ne se crée.

Dans ma macro, j'ouvre mon fichier Excel modèle en lecture seul comme ceci :

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
 
Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlBook As Excel.Workbook
 
Set Abase = CurrentDb
 
'Reception du chemin d'acces où est situe l'outil
chemin = CurrentProject.Path
 
'Initialisations Excel et ouverture du modèle en lecture seule
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open(chemin & "\00 - Extractions\03 - Modèles\Suivi par Département.xls", , True)
 
'Cache l'application durant la création du fichier
xlApp.Visible = False
 
Set xlSheet = xlBook.Worksheets("Global")

Ensuite je rempli mon fichier en utilisant evidement plein de :
xlSheet.Range(Cells(X, Y), Cells(X2, Y2))
Ce sur quoi la macro passe très bien une première fois, mais pas la deuxième.

Puis je laisse l'Excel ouvert pour que l'utilisateur puisse le consulter et l'enregistrer (ailleurs puisqu'il est en lecture seule). Le tout comme ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
xlBook.Application.ScreenUpdating = True
xlApp.Visible = True
 
 
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
 
Abase.Close

Le même problème apparait si j'enregistre et que je ferme Excel avec un :

Code :

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

1
2
3
 
xlBook.Close
xlApp.Application.Quit

Pourriez-vous m'aider à trouver comment résoudre ce problème ? Soit en tuer le processus en VBA, soit en n'évitant qu'il ne se crée.

D'avance Merci,

Raph

**SSJconsult** · 27/04/2010, 10h44

Bonjour,

Sur quelle instruction (ligne) donne-t-il l'erreur ?

**nico84** · 27/04/2010, 10h48

Bonjour,

Je fais souvent tout pareil et ça marche très bien

Au jeu des différences je n'en ai trouvé qu'une :

Code :

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

Set xlBook = xlApp.Workbooks.Open(chemin & "\00 - Extractions\03 - Modèles\Suivi par Département.xls", readonly=True)

Chez moi readonly est le 2e paramètre et non le 3e...

Je me souviens avoir eu le même problème que toi au début, en particulier pour faire du copier coller, mais je ne me souviens plus comment je l'ai résolu

**raph04** · 27/04/2010, 10h48

Ha oui en effet j'ai oublier de dire que c'est sur :

Code :

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

1
2
3
 
'Mise en forme de l'entete
With xlSheet.Range(Cells(Lig, 1), Cells(Lig, 17))

Mais en admettant que je saute cette ligne, cela fera pareil sur le prochain

Code :

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

1
2
 
xlSheet.Range(Cells(X, Y), Cells(X2, Y2))

**nico84** · 27/04/2010, 10h53

voici un exemple complet qui marche bien :

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
If Not Mode_debug Then On Error GoTo err:
Dim objApp As Object, objBook As Object, objSheet As Object
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim i As Integer, indice As String
DoCmd.Hourglass True
 
i = 0
indice = "" 'indice de page (15 lignes par page)
Set qdf = CurrentDb.QueryDefs("imp_F1")
qdf.Parameters("cd") = cd
Set rst = qdf.OpenRecordset
 
Set objApp = CreateObject("excel.application")
Set objBook = objApp.Workbooks.Open(Client_path & "planet_form1.xls", ReadOnly:=True)
Set objSheet = objBook.Worksheets("Form1")
objApp.Visible = Mode_admin   'si on veut visualiser
With objSheet
  .Activate
  While Not rst.EOF
    i = i + 1
    If i = 16 Then 'saut de page
      If Not Mode_admin Then .PrintOut Copies:=1, Collate:=True
      If indice = "" Then indice = "A" Else indice = Chr(Asc(indice) + 1)
      i = 1
    End If
    If i = 1 Then    'entete et pied de page
      .Range("repere").Value = cd & indice
      .Range("contrat").Value = rst!contrat
      .Range("rmq").Value = rst!Remarques
      .Range("sgn").Value = rst!signataire
      .Range("jour").Value = rst!date_cre
    End If
    .Range("adm").Offset(i - 1, 0).Value = rst!adm
    .Range("statut").Offset(i - 1, 0).Value = rst!STATUT
    .Range("description").Offset(i - 1, 0).Value = rst!libelle
    .Range("item").Offset(i - 1, 0).Value = i
    .Range("article").Offset(i - 1, 0).Value = rst!ref
    .Range("qte").Offset(i - 1, 0).Value = rst!Qte
    .Range("lot").Offset(i - 1, 0).Value = rst!lot1
    rst.MoveNext
  Wend
  rst.Close: Set rst = Nothing
  If indice > "" Then
    While i < 15
      i = i + 1
      .Range("adm").Offset(i - 1, 0).Value = ""
      .Range("statut").Offset(i - 1, 0).Value = ""
      .Range("description").Offset(i - 1, 0).Value = ""
      .Range("item").Offset(i - 1, 0).Value = ""
      .Range("article").Offset(i - 1, 0).Value = ""
      .Range("qte").Offset(i - 1, 0).Value = ""
      .Range("lot").Offset(i - 1, 0).Value = ""
    Wend
  End If
End With
 
'ferme excel & libère
If Not Mode_admin Then
  objSheet.PrintOut Copies:=1, Collate:=True
  objBook.Close SaveChanges:=False
  objApp.Quit
End If
Set objSheet = Nothing
Set objBook = Nothing
Set objApp = Nothing
DoCmd.Hourglass False
Exit Sub
err: msgbox "Erreur " & err.Number & " dans etats.imp_Form1 : " & err.DESCRIPTION
End Sub

**raph04** · 27/04/2010, 10h53

Envoyé par nico84

Chez moi readonly est le 2e paramètre et non le 3e...

Chez moi il met dit que le 2ème est : UpdateLinks
Et comme j'ai pas de liaison...

Et concernant ton code ci-dessus, en effet je ne vois pas trop de différences...

Le pire c'est que j'ai déjà fait des extractions excel, sans avoir de problème...
Mais bon celle là est particulièrement importante.

**raph04** · 27/04/2010, 11h00

En fait ce qui me gène le plus c'est que l'extraction fonctionne un première fois.
Donc le code n'est pas mauvais en soit. Ca donne surtout l'impression d'une erreur Windows, comme s'il hésitait entre 2 Workbooks Excel ou s'il faisait un overflow... Mais impossible de trouver le problème exact et donc le régler

**SSJconsult** · 27/04/2010, 11h04

S'il te donne cette erreur sur n'importe quelle ligne du type

Code :

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

xlSheet.Range(Cells(X, Y), Cells(X2, Y2))

et que X, Y, X2 et Y2 ont des valeurs acceptables pour Excel (doivent en tout cas etre differents de 0), c'est que le probleme se situe au niveau de la sheet.

Quand il bug, fait <ctrl+G> et tape dans la fenetre immediate

Code :

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

print xlsheet.name

pour verifier que tu es bien sur la bonne sheet.

**raph04** · 27/04/2010, 11h19

Après vérification :

Code :

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

1
2
3
4
5
6
?xlSheet.name
Gestion des Opération Apr
?xlbook.Name
Suivi par Département.xls
?xlapp.Name
Microsoft Excel

Ma feuille s'appel bien comme ça "Gestion des Opération Apr" et mon fichier Excel "Suivi par Département.xls"
Mais j'avoue que j'ai vraiment espéré que cela soit différent...

Sinon la ligne sur laquelle ça bug en premier :

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
xlSheet.Cells(Lig, 1) = "Nom"
xlSheet.Cells(Lig, 4) = "Fonct."
xlSheet.Cells(Lig, 5) = "Contrat"
xlSheet.Cells(Lig, 6) = "01/01/" & Me.Liste_Annee
xlSheet.Cells(Lig, 7) = "02/01/" & Me.Liste_Annee
xlSheet.Cells(Lig, 8) = "03/01/" & Me.Liste_Annee
xlSheet.Cells(Lig, 9) = "04/01/" & Me.Liste_Annee
xlSheet.Cells(Lig, 10) = "05/01/" & Me.Liste_Annee
xlSheet.Cells(Lig, 11) = "06/01/" & Me.Liste_Annee
xlSheet.Cells(Lig, 12) = "07/01/" & Me.Liste_Annee
xlSheet.Cells(Lig, 13) = "08/01/" & Me.Liste_Annee
xlSheet.Cells(Lig, 14) = "09/01/" & Me.Liste_Annee
xlSheet.Cells(Lig, 15) = "10/01/" & Me.Liste_Annee
xlSheet.Cells(Lig, 16) = "11/01/" & Me.Liste_Annee
xlSheet.Cells(Lig, 17) = "12/01/" & Me.Liste_Annee
xlSheet.Cells(Lig, 18) = "Effec." & vbCrLf & "Moy."
xlSheet.Cells(Lig, 20) = "SH"
 
With xlSheet.Range(Cells(Lig, 6), Cells(Lig, 17)) '  ****BUG ICI****
    .NumberFormat = "mmm;@"
End With

Lig = 2
Donc ça ne viens pas de là.

**SSJconsult** · 27/04/2010, 11h30

a tout hasard :

quel est le type de la variable "Lig" ?
n'y a-t-il pas un merge sur l'une des zones ?
le fichier Excel est bien ouvert en R/W ?
avez-vous essaye avec : xlSheet.Cells(Lig, 5).Value = "Contrat"

Comme Conan Doyle faisait dire a Sherlok Holmes : "Quand vous avez elimine tout ce qui est possible, il est temps d'envisager l'impossible"...

**raph04** · 27/04/2010, 12h11

Alors le lig est un long :

Code :

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

Dim Lig As Long

Et pour les 3 suivantes, le problème se serait posé lors de la première execution j'imagine. Mais il n'y a pas de cellules fusionnées, Excel est ouvert en ReadOnly et j'essaye avec les .value en plus, mais rien n'y fait.

Par contre j'aime bien la petite citation

Je cherche donc dans l'impossible

J'ai egalement ajouté une ligne :

Code :

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

1
2
'Zoom à 75%
ActiveWindow.Zoom = 75

Sur celle-ci aussi il bug à la seconde extraction... mais pas toujours

Donc ça ne viens vraiment pas trop du code en soit j'imagine... Mais plutôt de la gestion des workbooks et de l'application Excel.

**SSJconsult** · 27/04/2010, 12h37

oui, je pense que vous avez raison.

A votre place,

je fermerais totalement le fichier Excel tout en laissant le soin a l'utilisateur d'aller le lire par lui meme de la maniere classique
Je creerais a chaque fois un nouveau fichier dont le nom serait suffixe de Now() et ce afin que l'application Access ne risque pas d'essayer d'ecrire dans un fichier ouvert

C'est toujours ainsi que je travaille et parfois, ce n'est pas encore suffisant

**raph04** · 27/04/2010, 13h50

Envoyé par SSJconsult

je fermerais totalement le fichier Excel tout en laissant le soin a l'utilisateur d'aller le lire par lui meme de la maniere classique
Je creerais a chaque fois un nouveau fichier dont le nom serait suffixe de Now() et ce afin que l'application Access ne risque pas d'essayer d'ecrire dans un fichier ouvert

Hey bien en fait c'est ce que je faisais avant aussi, mais je me disais que je devait mal fermer le fichier au niveau de mon code et que c'était la raison de mon processus "fantome". Et j'avais le même problème

Mon code de fermeture :

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
xlBook.Application.ScreenUpdating = True
 
'Enregistre le fichier
xlApp.DisplayAlerts = False
xlBook.SaveAs chemin & "\00 - Extractions\00 - Cahier de plannif\Suivi par Département " & Format(Now, "yyyy-mm-dd - hhmmss") & ".xls"
xlApp.DisplayAlerts = True
 
xlApp.Visible = True
 
'code de fermeture et libération des objets
xlBook.Close
xlApp.Application.Quit
 
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

**Tofalu** · 27/04/2010, 14h15

Vous mélangez les deux types de déclarations VB. Préférez une liaison tardive

Code :

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

Dim xlApp As Object

A la fermetrue :

xlBook.Close

Ajoutez le paramètre permettant de forcer le non enregistrement (vous avez déjà sauvegardé le fichier avant)

On peut aussi essayer de placer un petit doevents ici :

Code :

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

1
2
3
xlBook.Close
DoEvents
xlApp.Application.Quit

**raph04** · 27/04/2010, 14h31

J'ai remplacé par ce que tu me propose.
Il semble qu'on ai avancé un peu.

En effet il me fait encore l'erreur sur les même lignes (y compris le zoom), mais pour les "xlSheet.Range(Cells(X, Y), Cells(X2, Y))" il me met une erreur différente.
Toujours erreur d'execution '1004' mais le message change :
"La méthode 'Cells' de l'objet '_Global' a echoué"

Alors je sais pas si ça évolue dans le bon sens ou non...

En tout cas je suis passé sur un enregistrement, et un 'quit' plutôt qu'un simple affichage.

**raph04** · 27/04/2010, 15h46

Mais sinon y a t'il un moyen de carrément tuer un processus en VBA ?
Parce qu'en "killant" mon EXCEL.EXE, le problème disparaît alors...
C'est peut-être un peu brutal, mais bon...

J'ai scruté mon gestionnaire des tâches, quand je lance la macro, un processus Excel se crée (c'est bien 1 seul processus, je m'étais dit qu'il été possible qu'il en crée un de trop) mais à la fin, lors de la fermeture du fichier, le processus Excel ne disparaît pas du gestionnaire...

**Tofalu** · 27/04/2010, 15h57

Cells est inconnu pour Access. Il faut faire l'appel complet :

xlSheet.Range(xlSheet.Cells(X, Y), xlSheet.Cells(X2, Y))"

Vérifie aussi de ne pas dépasser 255 colonnes

**Tofalu** · 27/04/2010, 16h00

Envoyé par Tofalu

Cells est inconnu pour Access. Il faut faire l'appel complet :

xlSheet.Range(xlSheet.Cells(X, Y), xlSheet.Cells(X2, Y))"

Vérifie aussi de ne pas dépasser 255 colonnes

Il en va de même quand utilise Selection, il est nécessaire de passer par l'objet Excel.Application correspondant. Genre : oAppExc.Selection

**SSJconsult** · 27/04/2010, 16h10

@TOFALU :

Code :

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

xlSheet.Range(Cells(1, 1), Cells(1, 4))

fonctionne parfaitement bien, j'en ai quelques centaines dans mes programmes

@raph04 :

Es-tu certain de ton copier/coller dans :

La méthode 'Cells' de l'objet '_Global' a echoué"

?

En effet la sheet dont il parle est '_Global' alors que celle que tu declarais dans ton permier message etait 'Global'

**Tofalu** · 27/04/2010, 16h13

Je viens de réessayer, en automation Access->Excel, cells ne fonctionne pas. A la limite .Cells dans un with Excel.Application OK, mais pas cells tout seul en dehors d'un bloc with puisque le membre par défaut sous Access est Access.Application et non Excel.Application

[Automation] Erreur Extraction de données depuis un fichier Excel

VBA Access

Discussions similaires

Partager

Partager