Lancer une macro excel à partir d'access

**LedZeppII** · 04/08/2011, 18h54

Je ne vois pas d'où vient le problème.
Chez moi ça fonctionne, je n'arrive pas à reproduire ton problème.

**Williamm** · 05/08/2011, 08h53

:/

C'est dommage car la manip sur Excel marche mais après ça fait planter Access sans m'indiquer de messages d'erreur :s

**Williamm** · 05/08/2011, 09h25

Ne penses tu pas qu'il faut peut-être après ces lignes:

Code :

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

1
2
3
4
5
6
7
8
Set xlQryTbl = Nothing
       Set xlSheet = Nothing
       wbk.Close
       Set wbk = Nothing
       xl.Quit
       Set xl = Nothing
 
    End If

lui faire comprendre qu'il faut revenir sur Access?

Merci
Will

**LedZeppII** · 05/08/2011, 13h30

Bonjour,

j'ai plutôt l'impression que le problème est apparu après l'ajout de :

Code :

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

1
2
    ' Demarre la requete ajout
    DoCmd.RunMacro "M3 Horrairemystere.Rempliossage horraire disvié"

Ou bien est-ce un hasard ?

**Williamm** · 05/08/2011, 14h05

Effectivement tu as raison!

J'avais mis un "Echo" et un "sablier" dans ma macro Access.
Je les ai retiré et maintenant j'ai plus de bugs.

Par contre (je n'avais pas fais attention), quand je rentre mon année dans le champ de mon formulaire (qui correspond donc au champ d'une table) et que je clique sur mon bouton qui génère am macro. Il prend pas en compte mon année mais celle qui était saisie avant que je rentre la nouvelle.

Ca parait logique d'ailleurs, il faudrait donc que lorsque je clique sur le bouton ça m'efface le champ initial de la table puis insère le nouveau champ que j'ai saisis.

Je vais essayer de trouver tout seul mais si jamais la solution est toute bête et que tu l'as, n'hésites pas à me l'expliquer

Je te remercies
Will

**LedZeppII** · 05/08/2011, 14h30

J'avais mis un "Echo" et un "sablier" dans ma macro Access.
Je les ai retiré et maintenant j'ai plus de bugs.

Je ne savais pas que ça pouvait causer des soucis.

Par contre (je n'avais pas fais attention), quand je rentre mon année dans le champ de mon formulaire (qui correspond donc au champ d'une table) et que je clique sur mon bouton qui génère am macro. Il prend pas en compte mon année mais celle qui était saisie avant que je rentre la nouvelle.

Lorsqu'un enregistrement est en cours de modification, les nouvelles valeurs ne sont pas encore écrites dans la table.
Cet état est matérialisé par un petit crayon dans le sélecteur d'enregistrement.

Avant de lancer ta macro, il faudrait forcer la sauvegarde de l'enregistrement.

Code :

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

1
2
3
If Me.Dirty Then
   DoCmd.RunCommand acCmdSaveRecord
End If

A+

**Williamm** · 05/08/2011, 15h00

Ca marche par contre j'ai pas dû le voir avant mais:

Quand je saisie l'année 2012 et que je clique sur S1, ça me crée un onglet "S1.1012" avec le tableau de ma requête avec l'année 2012. Jusque là tout est parfait.

Ensuite quand je saisie l'année 2013 et que je clique sur S1, ça me crée un autre onglet "S1.2013" avec le tableau de ma requête avec l'année 2013.

Et le problème c'est que le tableau de l'onglet "S1.2012" prend également la valeur du tableau de l'onglet "S1.2013"
Je voudrais que le tableau de l'onglet "S1.1012" garde ses valeurs c'est à dire celles correspondantes à l'année 2012.

C'est possible ?

ps: Quand on clique sur S1, c'est toujours la meme requête qui est utilisée. Seul l'année dont dépend la requete va faire changer les valeurs.

**LedZeppII** · 05/08/2011, 15h41

C'est curieux ça.
J'ai testé dans ma base, mais moi je n'ai pas ce problème.
Par contre, dans Excel, si je fais «Actualiser» sur les données de S1.2012 il re-exécute la requête et ramène les données 2013.

Si tu n'as pas besoin que l'on puisse re-exécuter les requêtes Excel depuis le classeur Excel, on peut ne pas conserver la définition des requêtes Excel.

Code :

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

1
2
3
4
       ' Exécute requête
       xlQryTbl.Refresh False
       ' Supprime requête (mais garde les données)
       xlQryTbl.Delete

**Williamm** · 05/08/2011, 16h04

Ce code efface bien les liaisons.

Mais moi dans le cas ou l'onglet "S1.2012" est crée et que je décide de créer l'onglet "S1.2013" je veux supprimer la requête de "S1.2012" (tout en gardant les données) et continuer de faire marcher la requête sur l'onglet "S1.2013"

Tu vois ou je veux en venir?

En testant avec ton code, le problème c'est que quand je crée un nouvel onglet j'ai plus aucun lien avec la base Access avec cet onglet.
Or je veux cette situation mais seulement pour l'onglet d'avant.

**LedZeppII** · 05/08/2011, 17h24

Ok, je vois.

On va partir sur une convention de nommage des requêtes Excel sous la forme TQ_Sn_AAAA (où n=1 ou 2 et AAAA est l'année).
Cette Sub permet de supprimer les définitions de requêtes Excel TQ_Sn_AAAA sauf celle passée en deuxième argument.

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
Sub SupprLiaisonsTQ(xlWbk As Excel.Workbook, sSaufTQName As String)
Dim xlSheet As Excel.Worksheet
Dim sTQName As String
Dim i As Integer
 
' Parcourir les feuilles
For Each xlSheet In xlWbk.Worksheets
    ' Parcourir les requêtes Excel
    For i = xlSheet.QueryTables.Count To 1 Step -1
        sTQName = xlSheet.QueryTables(i).Name
        ' Si c'est une requête TQ_Sn_AAAA
        If sTQName Like "TQ_S#_####" Then
           If sTQName <> sSaufTQName Then
              xlSheet.QueryTables(i).Delete
           End If
        End If
    Next
Next
 
End Sub

Ensuite dans ton code, ajoutes cette déclaration de variable

Code :

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

    Dim TQName As String

Là je reprends le code à partir du Else

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
    Else
       ' Créer une nouvelle feuille après la dernière feuille
       Set xlSheet = wbk.Worksheets.Add(After:=wbk.Worksheets(wbk.Worksheets.Count))
       xlSheet.Name = "S1" & "." & Annee
       xlSheet.Activate
 
       ' Chaîne de connexion ODBC
       sODBCconn = ...
 
       ' Code SQL de la requête
       sSQL = ...
       ' Nom requête Excel
       TQName = "TQ_" & "S1" & "_" & Annee
 
       ' Création requête Excel
       Set xlQryTbl = wbk.ActiveSheet.QueryTables.Add(sODBCconn, wbk.ActiveSheet.Range("B6"))
       'Paramétrage requête Excel
       With xlQryTbl
           .CommandText = sSQL
           .Name = TQName
           .FieldNames = True
           .RowNumbers = False
           .FillAdjacentFormulas = False
           .PreserveFormatting = True
           .RefreshOnFileOpen = False
           .BackgroundQuery = True
           .RefreshStyle = xlInsertDeleteCells
           .SavePassword = True
           .SaveData = True
           .AdjustColumnWidth = True
           .RefreshPeriod = 0
           .PreserveColumnInfo = True
       End With
       ' Exécute requête
       xlQryTbl.Refresh False
 
       ' Supprime définitions de requêtes autres que TQName
       SupprLiaisonsTQ wbk, TQName
 
       wbk.Save
 
       Set xlQryTbl = Nothing
       Set xlSheet = Nothing
       wbk.Close
       Set wbk = Nothing
       xl.Quit
       Set xl = Nothing
 
    End If

Ce qui change :
Ligne 13 : je mets le nom de la requête Excel qui va être créée dans la variable TQName.
Ligne 20 : j'utilise la variable pour définir le nom de la requête XL qui vient d'être créée.
Ligne 38 : j'appelle la Sub qui supprime les définitions de requêtes TQ_Sn_AAAA (sauf TQName).

A+

**Williamm** · 05/08/2011, 18h48

Parfait lorsque je crée successivement 2 semestre S1.

Mais je crois que ça n'a pas résolue une chose:

je crée S1. 12 et S1. 13 <= tout se passe normalement.

Le soucis est que le tableau de "S1. 13" doit s'actualiser avec le Access. Et dans ce cas, le tableau de "S1. 12" s'actualise aussi de la même maniere et récupere le tableau de "S1. 13" :/

Il y a un moyen que ça n'actualise seulement le dernier onglet crée ?
D'ailleurs en parlant d'actualisation, quand je modifie les données Acess ca ne s'actualise pas automatiquement sur le excel :/

Dit moi si je ne suis pas assez clair,

Merci
Will

**LedZeppII** · 05/08/2011, 19h16

Envoyé par Williamm

Le soucis est que le tableau de "S1. 13" doit s'actualiser avec le Access. Et dans ce cas, le tableau de "S1. 12" s'actualise aussi de la même manière et récupère le tableau de "S1. 13" :/

Ça, je ne comprend pas comment c'est possible («le tableau de "S1. 12" s'actualise aussi de la même manière et récupère le tableau de "S1. 13"»).
Il y a peut-être quelque chose dans les options d'Excel ou du classeur, qui force le rafraichissement des données issues de requêtes Excel.
Mais après la dernière modif de code, il ne doit plus y avoir de définition de requête pour S1.2012 si tu as créé S2.2013.
Donc le rafraichissement ne devrait pas être possible.

Envoyé par Williamm

D'ailleurs en parlant d'actualisation, quand je modifie les données Acess ca ne s'actualise pas automatiquement sur le excel :/

Normal.
Les requêtes Excel montrent les données du moment où la requête a été exécuté.
C'est un instantané des données.
Il n'y a pas de liaison temps réel entre Excel et Access.
Pour rafraichir les données, il faut sélectionner une cellule dans la plage de données, faire un clic-droit et sélectionner Rafraichir ou Actualiser (je ne sais plus).

A+

**Williamm** · 05/08/2011, 19h33

J'avais modifié un peu ton code pensant bien faire.
En fait pour actualiser le excel: j'ai modifier en mettant:

Code :

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

1
2
.RefreshOnFileOpen = True
.RefreshPeriod = 1

Le problème du coup c'est que ça actualise même le "S1. 12" de la même maniere que le "S1. 13"

Effectivement je peux actualiser de maniere manuelle la plage de cellule mais mon projet nécessite que le Excel s'actualise automatiquement avec le Access.
Et il faudrait même qu'il s'actualise même si le Excel est fermé.

Tu me dis que ce n'est pas possible vraiment ?

Will,

**LedZeppII** · 05/08/2011, 21h03

Essaie en ouvrant le classeur avec

Code :

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

    Set wbk = xl.Workbooks.Open("C:\" & NomFichier, 0)

Ça devrait bloquer l'actualisation à l'ouverture.
Si le reste du code s'exécute en moins d'une minute, ça peut le faire.

**Williamm** · 06/08/2011, 19h31

Non rien y fait :s

J'ai peut être une idée.
En ecrivant:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
.CommandText = sSQL
           .Name = TQName
           .FieldNames = True
           .RowNumbers = False
           .FillAdjacentFormulas = False
           .PreserveFormatting = True
           .RefreshOnFileOpen = True
           .BackgroundQuery = True
           .RefreshStyle = xlInsertDeleteCells
           .SavePassword = True
           .SaveData = True
           .AdjustColumnWidth = True
           .RefreshPeriod = 1
           .PreserveColumnInfo = True

Avec ça l'onglet s'actualise toute les minutes et a chaque ouverture du fichier excel.

Ne penses tu pas qu'on pourrait faire en sorte que lorsqu'on clique sur le bouton, ça supprime l'actualisation automatique de "S1. 12" et de meme pour tous les autres semestre qu'on crée à chaque clique de manière a ce que le nouvelle onglet crée s'actualise (minute par minute et à l'ouverture) et rendre l'actualisation de l'onglet d'avant inactif?

Will
(Merci de garder le fil de la conversation

)

**LedZeppII** · 08/08/2011, 10h25

Bonjour,

J'ai fait des essais avec :

Code :

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

1
2
.RefreshOnFileOpen = True
.RefreshPeriod = 1

Je n'ai pas le problème de rafraichissement à l'ouverture (Excel 2003 SP3).

Du coup, je n'arrive pas à savoir si ton Excel agit différemment du mien.
Le rafraichissement est-il consécutif à l'ouverture du fichier Excel, ou à l'intervalle d'1 minute ?

A+

**Williamm** · 08/08/2011, 10h35

Il est consécutif aux deux évènements: ouverture du excel et intervalle d'une minute.

Mon code est le suivant:

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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
Private Sub Commande1_Click()
 
    Dim TQName As String
    Dim xlQryTbl As Excel.QueryTable
    Dim sODBCconn As String, sSQL As String
    Dim xl As Excel.Application
    Dim wbk As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim Annee As Variant
    Dim NomFichier As Variant
    Annee = Me![Num Année]
    NomFichier = "2onglets.xls"
 
    If Me.Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
    End If
 
    ' Demarre la requete ajout
 
    DoCmd.RunMacro "M3 Horrairemystere.Rempliossage horraire disvié"
 
    ' Démarrer Excel et le rendre visible
 
    Set xl = CreateObject("Excel.Application")
      Set wbk = xl.Workbooks.Open("C:\" & NomFichier, 0)
    xl.Visible = True
    'On Error Resume Next
    xl.UserControl = True
 
    'Worksheets("S1 " & "." & Annee - 1 & " ").Select
 
 
    ' Test de l'existence d'une feuille
    If FeuilleExiste(wbk, "S1 " & "." & Annee & " ") Then
       MsgBox "La feuille S1 " & "." & Annee & " existe deja.", vbInformation
 
       'Fermer le classeur sans l'enregistrer
       wbk.Close False
       Set wbk = Nothing
 
       ' Quitter Excel
       xl.Quit
       Set xl = Nothing
 
    Else
 
       ' Créer une nouvelle feuille après la dernière feuille
       Set xlSheet = wbk.Worksheets.Add(After:=wbk.Worksheets(wbk.Worksheets.Count))
       xlSheet.Name = "S1 " & "." & Annee & " "
       xlSheet.Activate
 
 
       ' Chaîne de connexion ODBC
       sODBCconn = "ODBC;DSN=MS Access Database;" & _
                   "DBQ=d:\Documents and Settings\2594215\Bureau\William AF\test william\TESTen coursMennecyAmeliore 05a_08.mdb"
       ' Code SQL de la requête
       sSQL = "SELECT * FROM [R_QueryTableaupresent 1S] ORDER BY IIf([R_QueryTableaupresent 1S].[Expr1]='MAN',1,IIf([R_QueryTableaupresent 1S].[Expr1]='TECH',2,3)), IIf([R_QueryTableaupresent 1S].[Horaire1]='M',1,IIf([R_QueryTableaupresent 1S].[Horaire1]='S',2,3));"
 
       ' Nom requête Excel
       TQName = "TQ_" & "S1" & "_" & Annee
 
       ' Création requête Excel
       Set xlQryTbl = wbk.ActiveSheet.QueryTables.Add(sODBCconn, wbk.ActiveSheet.Range("B6"))
 
 
       'Paramétrage requête Excel
       With xlQryTbl
           .CommandText = sSQL
           .Name = TQName
           .FieldNames = True
           .RowNumbers = False
           .FillAdjacentFormulas = False
           .PreserveFormatting = True
           .RefreshOnFileOpen = True
           .BackgroundQuery = True
           .RefreshStyle = xlInsertDeleteCells
           .SavePassword = True
           .SaveData = True
           .AdjustColumnWidth = True
           .RefreshPeriod = 1
           .PreserveColumnInfo = True
       End With
       ' Exécute requête
       xlQryTbl.Refresh False
 
 
       ' Supprime définitions de requêtes autres que TQName
       SupprLiaisonsTQ wbk, TQName
 
       wbk.Save
 
       Set xlQryTbl = Nothing
       Set xlSheet = Nothing
       wbk.Close
       Set wbk = Nothing
       xl.Quit
       Set xl = Nothing
 
    End If
 
End Sub

On pourrait pas mettre ce bout de code:

Code :

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

.RefreshOnFileOpen = False

et

Code :

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

.RefreshPeriod = 0

à un moment dans le code pour imposer que l'onglet précédent ne s'actualise pas ?

**LedZeppII** · 08/08/2011, 10h50

essaie comme ça

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
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
Private Sub Commande1_Click()
 
    Dim TQName As String
    Dim xlQryTbl As Excel.QueryTable
    Dim sODBCconn As String, sSQL As String
    Dim xl As Excel.Application
    Dim wbk As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim Annee As Variant
    Dim NomFichier As Variant
    Annee = Me![Num Année]
    NomFichier = "2onglets.xls"
 
    If Me.Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
    End If
 
    ' Démarrer Excel et le rendre visible
 
    Set xl = CreateObject("Excel.Application")
      Set wbk = xl.Workbooks.Open("C:\" & NomFichier, 0)
    xl.Visible = True
    'On Error Resume Next
    xl.UserControl = True
 
    'Worksheets("S1 " & "." & Annee - 1 & " ").Select
 
    ' Test de l'existence d'une feuille
    If FeuilleExiste(wbk, "S1 " & "." & Annee & " ") Then 
       'Fermer le classeur sans l'enregistrer
       wbk.Close False
       Set wbk = Nothing
 
       ' Quitter Excel
       xl.Quit
       Set xl = Nothing
 
       MsgBox "La feuille S1 " & "." & Annee & " existe deja.", vbInformation
 
    Else
 
       ' Créer une nouvelle feuille après la dernière feuille
       Set xlSheet = wbk.Worksheets.Add(After:=wbk.Worksheets(wbk.Worksheets.Count))
       xlSheet.Name = "S1 " & "." & Annee & " "
       xlSheet.Activate
 
       ' Chaîne de connexion ODBC
       sODBCconn = "ODBC;DSN=MS Access Database;" & _
                   "DBQ=d:\Documents and Settings\2594215\Bureau\William AF\test william\TESTen coursMennecyAmeliore 05a_08.mdb"
       ' Code SQL de la requête
       sSQL = "SELECT * FROM [R_QueryTableaupresent 1S] ORDER BY IIf([R_QueryTableaupresent 1S].[Expr1]='MAN',1,IIf([R_QueryTableaupresent 1S].[Expr1]='TECH',2,3)), IIf([R_QueryTableaupresent 1S].[Horaire1]='M',1,IIf([R_QueryTableaupresent 1S].[Horaire1]='S',2,3));"
 
       ' Nom requête Excel
       TQName = "TQ_" & "S1" & "_" & Annee
 
       ' Supprime définitions de requêtes autres que TQName
       SupprLiaisonsTQ wbk, TQName
 
       ' Demarre la requete ajout 
       DoCmd.RunMacro "M3 Horrairemystere.Rempliossage horraire disvié"
 
       ' Création requête Excel
       Set xlQryTbl = wbk.ActiveSheet.QueryTables.Add(sODBCconn, wbk.ActiveSheet.Range("B6"))
 
       'Paramétrage requête Excel
       With xlQryTbl
           .CommandText = sSQL
           .Name = TQName
           .FieldNames = True
           .RowNumbers = False
           .FillAdjacentFormulas = False
           .PreserveFormatting = True
           .RefreshOnFileOpen = True
           .BackgroundQuery = True
           .RefreshStyle = xlInsertDeleteCells
           .SavePassword = True
           .SaveData = True
           .AdjustColumnWidth = True
           .RefreshPeriod = 1
           .PreserveColumnInfo = True
       End With
       ' Exécute requête
       xlQryTbl.Refresh False
 
       wbk.Save
 
       Set xlQryTbl = Nothing
       Set xlSheet = Nothing
       wbk.Close
       Set wbk = Nothing
       xl.Quit
       Set xl = Nothing
 
    End If
 
End Sub

J'ai modifié l'ordre d'exécution.
En particulier celui de la macro "M3 Horrairemystere.Rempliossage horraire disvié"

A+

**Williamm** · 08/08/2011, 11h12

Ca à l'air de marcher nikel !!
J'attens de faire plusieurs autres essais avant d'en être certain.
Je te remercies

Mon tableau (ou plutôt ma requête) est composé de date sur sur les cellules D6:GC6

J'ai essayé d'introduire un code pour afficher le numéro de semaine sur les cellules D4:GC4 et le jour de la semaine sur les cellules D5:GC5.

Ca doit ressemble à ça:

Code :

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

1
2
3
4
ActiveCell.FormulaR1C1 = "=INT(MOD(INT((R[2]C-2)/7)+0.6,52+5/28))+1"
    Selection.AutoFill Destination:=Range("D4:GC4"), Type:=xlFillDefault
ActiveCell.FormulaR1C1 = "=TEXT(R[1]C,""jjj"")"
    Selection.AutoFill Destination:=Range("D5:GC5"), Type:=xlFillDefault

Mais ça ne fonctionne pas :/

**LedZeppII** · 08/08/2011, 20h17

Bonsoir,

Lorsqu'on programme Excel par automation (le code VBA n'est pas dans Excel), il faut éviter le codage implicite.
Par exemple ActiveCell, sans désigner dans quelle feuille, dans quel classeur, dans quelle instance d'Excel.
Ça veut dire éviter de faire référence à un élément Excel par autre chose qu'une variable objet Excel.
Dans ton code ce sont les variables xl, wbk, xlSheet, xlQryTbl.

Après exécution de la requête, essaie quelque chose dans ce style :

Code :

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

1
2
3
4
5
xlSheet.Range("D4").Formula = "=INT(MOD(INT((D6)/7)+0.6,52+5/28))+1"
xlSheet.Range("D4").AutoFill xlSheet.Range("D4:GC4"), xlFillCopy
 
xlSheet.Range("D5").Formula = "=TEXT(D6, ""jjj"")"
xlSheet.Range("D5").AutoFill xlSheet.Range("D5:GC5"), xlFillCopy

A+

Lancer une macro excel à partir d'access

VBA Access

Discussions similaires

Partager

Partager