Discussion :

[ChristianBosch]

Bonjour,

J'aimerais savoir s'il est possible de faire une actualisation de requête dans un classeur fermé afin d'éviter d'avoir un classeur "tampon" base de données. J'en ai besoin pour faire un échange bilatéral de données entre deux classeurs et le problème c'est que tant que mon classeur n'a pas fait la MAJ des données, les données sont inexactes puisque la requête du second classeur n'est pas rafraichie.

Si c'est pas possible tant pis je mettrais la table dans un classeur tampon que j'ouvrirais pour enregistrer les données

Merci

[Thumb down]

bonjour,
de ce que je comprend, tu as un classeur A qui dispose d'une requête sur ????!
tu as un classeur B qui fait une requête sur le classeur A?

ton problème est que les données du classeur B ne sont pas exacte vue que la requête du classeur fermé A n'a pas été mis à jour?

c'est bien cela?

[ChristianBosch]

En gros j'ai un classeur A qui a une requête sur un classeur B, la table du classeur A contient un champs qui est calculé via un autre table du classeur B.

Quand je change la table du classeur B j'aimerais que la table du classeur A se mette a jour pour avoir le retour d'information sur le classeur B (via la seconde requête bilatérale).

Il faudrait donc que la requête du classeur A sur la table du classeur B puisse se faire sans ouvrir le classeur le classeur A.

Il faudrait donc actualiser les requêtes du classeur A en le commandant depuis le classeur B sans l'ouvrir (pour éviter de monopoliser le classeur avec le temps d'ouverture et de MAJ des requêtes)

Mais je pense que c'est très probablement impossible.

voila ce que j'ai fait pour le moment (ca fonctionne 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
Sub OF_cloture()
Dim TabOF(1 To 2) As Variant
Dim wbOrdo As Workbook
Dim objExcel As Excel.Application
 
CancelOF = False
ordo.Show
If CancelOF Then Exit Sub
WOP_transfert.Show
If CancelOF Then Exit Sub
 
TabOF(1) = WOP
TabOF(2) = NB
 
ajoutLigne TabOF(), "OF_cloture_HS20", Feuil17
 
ThisWorkbook.Save
 
If Lecture_Seule(fichierOrdo) = False Then
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = False
    Set wbOrdo = objExcel.Workbooks.Open(fichierOrdo)
 
    With wbOrdo
        .RefreshAll
        .Close True
    End With
 
    objExcel.Quit
End If
 
With ThisWorkbook
    .RefreshAll
    .Save
End With
 
MsgBox "Enregistrement de " & NB & " pieces sur le WOP : " & WOP & " En attente de la mise a jour de l'ordonnancement", vbInformation, "Enregistrement WOP"
 
End Sub

Mais le gros soucis c'est que je mets déjà 30 secondes avec mon pc pour : ajouter une ligne a la table + sauvegarder fichier A + démarrer instance cachée + ouvrir fichier B + rafraichir fichier B + sauvegarder fichier B + fermer fichier B + rafraichir fichier A + sauvegarder fichier A.

Alors que mes fichier sont vraiment pas volumineux... Donc je pense que ça risque de se dégrader avec le temps alors que c'est déjà long.

D’où mon questionnement sur l'éventuelle MAJ des tables sous requêtes dans mon fichier B en ne l'ouvrant pas (car je pense que c'est l'opération la plus longue)

[Pierre Fauconnier]

Salut Christian...

Power Query?

Mieux encore, une refonte de ton système...

[ChristianBosch]

Salut Pierre, j'utilise déjà power query.

Mais je me rends compte que utiliser excel comme bdd dynamique partagée c'est un peu la galère et pas trop adapté. C'est un outils trop lourd pour faire ça.

[Pierre Fauconnier]

Je pense que Power Query permet certains "montages" de DB qui évitent la mise en place d'un Access ou d'un SQL. Tout dépend de la structure dans laquelle on travaille (nombre d'utilisateurs, volume des données, ....).

On peut donc réaliser certaines choses avec des classeurs Excel "as Database", mais je pense qu'il faut éviter les allers-retours entre fichiers. On peut avoir x classeurs qui contiennent les données, mais dans le classeur de travail, on ne devrait pas avoir des données dont les autres classeurs ont besoin. Autrement dit, le flux des données ne devrait aller que dans un sens...

[ChristianBosch]

Oui exactement et c'est pour ça que dans mon cas ça va pas aller j'ai besoin d'utiliser autre chose pour le faire, je vais aller voir du coté de Access. Je change de boutique

[Thumb down]

Bonjour,
Je ne suis pas un expert de Powerquerry.

Pierre est-il possible d'actualiser le classeur fermé en faisant une requête sur la feuille contenant la requête Powerquerry avec AdoDb par exemple?

Ou même soyons fous un autre Powerquerry !

Il me semble qu'il devait y avoir inter-reactivités?????

Du reste je ne vois pas pourquoi les Powerquerry ne s'actualiseraies pas en cascade ? Une option ?

[Pierre Fauconnier]

Salut Thumb,


Il n'est pas possible d'agir sur un classeur fermé, même en ADODB. Le fichier est forcément "ouvert" lors du adodb.cn.open même si c'est "hors Excel", de sorte que les mécanismes sur les verrous vont jouer quoi qu'on fasse. Dans la mesure ou on pourrait même ouvrir un fichier par ADODB alors qu'il l'est déjà par Excel, je doute que l'écriture par ADODB se fasse dans la mémoire vive du fichier Excel ouvert, de sorte que ça va poser un problème de téléscopage ou plus vraisemblablement une perte de données, le dernier fermant le fichier étant le "gagnant" dont les données seront pérennisées, et elles seules. Donc, perso, je suis loin d'être fan de l'écriture dans un fichier par ADODB et je considère en gros que c'est à ranger au rayon des mauvaises pratiques.

Quoi qu'il en soit, si on injecte des données dans un classeur "fermé", les cellules qui dépendraient de ces nouvelles données ne seraient de toute façon par recalculées "classeur fermé", de sorte que leur récupération par Power Query serait incohérente. Dès lors, imaginer que B pousse des données dans A "fermé" pour les "recalculer" de façon à ce que B récupère des données de A "actualisées après recalcul" ne tient tout simplement pas la route. Soit les données injectées dans A par B n'induisent pas de recalcul en A et dès lors, il suffit de les ajouter à la récup de A dans B, soit elle induisent un recalcul dans A avant récupération dans B et il faudra alors ouvrir A pour forcer le recalcul puis récupérer dans B. Dans ce cas, le modèle de fichiers est foireux et doit être revu.

En ce qui concerne la MAJ d'une solution Power Query, elle n'est possible selon moi que classeur ouvert. Donc si A contient une solution Power Query (qu'elle pointe vers B ou vers n'importe quoi d'autre), la récupération des données de A "fermé" en adodb ne déclenchera pas la MAJ de la solution Power Query, de sorte que les données récupérées ne seraient potentiellement pas les données actualisées.

En ce qui concerne la mise à jour en cascade, je suppose qu'il s'agit d'une cascade "entre fichiers" et que cela signifie que A aurait une requête vers B qui aurait une requête vers A, de sorte que la MAJ de PQ_B "force" en quelque sorte la MAJ de PQ_A. On comprendrait vite, si on faisait un schéma de la chose, que ça poserait problème, puisque B mettrait à jour A qui mettrait à jour B qui mettrait à jour A qui mettrait...


Je n'ai pas vérifié mes dires par la mise en place d'une solution qui nécessiterait ce montage, dans la mesure où je considère qu'il y a alors un vice de conception et qu'il faut donc porter son attention à la résolution de ce problème plutôt que d'imaginer des solutions de contournement.

[ChristianBosch]

Je n'ai pas vérifié mes dires par la mise en place d'une solution qui nécessiterait ce montage, dans la mesure où je considère qu'il y a alors un vice de conception et qu'il faut donc porter son attention à la résolution de ce problème plutôt que d'imaginer des solutions de contournement.

Oui effectivement étant donné que ce besoin n'est pas compatible avec les fonctionnalités Excel je vais essayer de faire autrement avec les outils que j'ai a ma disposition, qui seront eux plus compétents.

Après en soit j'arrive a le faire de manière détournée puisque je teste si le fichier est en lecture seule et si ce n'est pas le cas je l'ouvre et l'actualise. Mais Excel est un programme lourd (car il offre une belle panoplie de fonctionnalités) et c'est très long (surtout que j'ouvre une seconde instance pour cacher l'opération).

Donc je vais essayer de passer via une BDD partagée et unique sur access qui me permettra, je pense, de répondre a ce besoin. De plus l’outil PowerQuery nous permet de récupérer la table en requête comme dans un classeur. Il va juste falloir apprendre a programmer sous access pour manipuler la base

[Pierre Fauconnier]

Tu ne peux pas récupérer les données de A dans B, puis y joindre les données de B (celles que tu aurais injectées dans A) et recalculer dans B? Normalement, les données de A devraient être inertes (un peu comme un CSV) de sorte qu'elles ne devraient pas être recalculées en A avant d'être récupérées en B. Si elles doivent être recalculées, elles peuvent l'être dans B.


Je ne comprends pas trop où ça coince, en fait

[Thumb down]

Oui Pierre.
Pour moi utiliser AdoDb pour Excel, bien qu'il m'arrive de données des réponses à certains demandeur, est une aberration pour des raisons que j'avais évoqué dans une autre discussion !

J'en étais arrivé à la même conclusion mais je voulais ton avis car tu est plus compétant que moi sur le sujet !

[ChristianBosch]

En fait c'est de l’ordonnancement de production

Dans A on va rentrer les OF dans la table(1) et on va les archiver une fois fini (transférer dans la table des OF cloturés).

Dans B on recupere cette table(1) et via un USF on va rentrer la quantité de pièces faite sur un OF qui va se mettre dans une autre table (2)

Dans B on recupere cette table(2) qui va nous servir a mettre a jour les OF avec la quantité réalisée via la clefs de correspondance dans la table(1) (numéro de LOT)

Mais pour avoir la MAJ des OF dans B vu qu'on se sert de la table qui se trouve dans A on a besoin qu'elle se mette a jour.

Après oui je pourrais faire le calcul direct dans B ça serait plus simple, mais je ne sais pas comment faire pour faire ça sur une requête.

[Pierre Fauconnier]

Pour moi, ça peut se piloter au départ d'un fichier Excel qui interagit avec une base Access "inerte":
On récupère les données Access dans un tableau structuré;
On met à jour les données dans Excel;
On repousse dans Access.

Avec un clé de version d'enregistrement dans Access, on vérifie que personne d'autre n'a modifié l'enregistrement que l'on repousse dans Access. J'ai modélisé les accès à une base Access avec Excel dans cette contribution, et on peut voir à la mise en place de cela dans la présente discussion. Dans la mesure où justement c'est modélisé, il suffit, en gros, d'écrire les requêtes SQL d'acquisition et de mise à jour. A priori, c'est assez léger comme code.

Au départ, il est + facile de démarrer en créant la base côté Access plutôt que de la créer par code, mais dans un premier temps, c'est juste une copie de la structure de ta table 1.

[ChristianBosch]

Pour moi, ça peut se piloter au départ d'un fichier Excel qui interagit avec une base Access "inerte":
On récupère les données Access dans un tableau structuré;
On met à jour les données dans Excel;
On repousse dans Access.

Avec un clé de version d'enregistrement dans Access, on vérifie que personne d'autre n'a modifié l'enregistrement que l'on repousse dans Access. J'ai modélisé les accès à une base Access avec Excel dans cette contribution, et on peut voir à la mise en place de cela dans la présente discussion. Dans la mesure où justement c'est modélisé, il suffit, en gros, d'écrire les requêtes SQL d'acquisition et de mise à jour. A priori, c'est assez léger comme code.

Au départ, il est + facile de démarrer en créant la base côté Access plutôt que de la créer par code, mais dans un premier temps, c'est juste une copie de la structure de ta table 1.

Parfait merci je vais faire comme ça !

[ChristianBosch]

Re, j'ai commencé a étudier et mettre en pratique les billets qui sont en lien.

Je rencontre un soucis :

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
Function getRows(Command As String, Optional Parameters As Collection) As Variant
  Dim cn As New ADODB.Connection
  Dim cm As New ADODB.Command
  Dim rs As ADODB.Recordset
  Dim pm As ADODB.Parameter
  Dim getConnectionString As String
    getConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & cheminBDD
  cn.Open getConnectionString
  cm.ActiveConnection = cn
  cm.CommandText = Command
  If Not Parameters Is Nothing Then
    For Each pm In Parameters
      cm.Parameters.Append pm
    Next
  End If
  Set rs = cm.Execute()
  If Not rs.EOF Then getRows = Transpose(rs.getRows)
  rs.Close
  cn.Close
End Function
Private Function Transpose(Source)
  Dim i As Long, j As Long
  ReDim Target(0 To UBound(Source, 2), 0 To UBound(Source))
 
  For i = 0 To UBound(Target)
    For j = 0 To UBound(Target, 2)
      Target(i, j) = Source(j, i)
    Next j
  Next i
  Transpose = Target
End Function
 
Sub test()
Dim tabOrdo() As Variant
tabOrdo = getRows("select Article,Poste,Date debut,Date fin, Qte a faire,Qte realisee,notes from Ordonancement")
End Sub

Je récupère bien mes deux premiers champs (Article,Poste) mais ça bute sur "Date début" avec une erreur de syntaxe (opérateur absent) pourtant ma table contient bien ce champ avec ce nom



Je pense que le soucis vient peu être du faire que ce soir un champ "date", je sais pas trop.

PS: C'est simplement par curiosité car avec les requêtes c'est pas forcement necessaire

[Pierre Fauconnier]

Salut Christian,

Une règle d'or avec les noms de colonne: Jamais d'espaces

Pour résoudre cela, encadre les noms avec des crochets: Select Article, Poste, [Date debut], ...

Une bonne idée est de l'écrire côté Access pour voir comment elle passe, et tu la recopies dans ton code VBA côté Excel

[ChristianBosch]

Salut Christian,

Une règle d'or avec les noms de colonne: Jamais d'espaces

Pour résoudre cela, encadre les noms avec des crochets: Select Article, Poste, [Date debut], ...

Une bonne idée est de l'écrire côté Access pour voir comment elle passe, et tu la recopies dans ton code VBA côté Excel

Hoooo d'accord ! C'est vrai que j'y avais pas pensé merci

Un grand merci aussi pour les billets, c'est génial ! Vous êtes super