Logo GNT
Actualités Tests & Guides Bons Plans
Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11
Entraide Applications Microsoft Office Excel automatiser la mise à jour de la source ext. d'un TCD

automatiser la mise à jour de la source ext. d'un TCD

14 réponses
Avatar
michel
Bonjour à tous....

Un peu las de tourner en rond depuis de nombreuses heures, et après
avoir longuement étudié la page d'EXCELABO dédié à cette question
(Changer la source (lien ODBC) d'un tableau croisé dynamique), je me
tourne vers vous...
Voici mon prb.

Sur différentes feuilles d'un fichier EXCEL, je trouve plusieurs TCD
(nombre variable selon les feuilles) qui tous sont liés à une même table
ACCESS située soit dans le même dossier que le fichier XLS soit dans un
dossier "fils".

Ce fichier (et la table associée) sont destinés à être utilisés sur
différents postes de travail, et, selon toute probabilité, à être
utilisés en copie locale sur chaque Poste.
Ca implique qu'à chaque copie du Fichier et de sa table associée, on
redéfinisse la source de tous les TCD en fonction du nouvelle
emplacement....

La solution manuelle, appliquée à 1 TCD, trouvée sur Excelabo
(http://www.excelabo.net/excel/tcddiv.php#sourcetcd) fonctionne très bien.

Mon objectif serait de pouvoir automatiser la chose pour chaque TCD de
toutes mes feuilles pour que ce soit transparent pour l'utilisateur....

Actuellement j'en suis là :

---------------------------------------------
Sub Mise_à_jours_lient_BDD()
'
Dim Dlg As FileDialog
Dim I As Integer, J As Integer
Dim Feuille As Worksheet

'Récupération du chemin complet de la table ACCESS par l'utilisateur
Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
Dlg.Show
NomBDD = Dlg.SelectedItems(1)

'extraction du chemin de dossier seul contenant la table Access
For I = Len(NomBDD) To 1 Step -1
If Mid$(NomBDD, I, 1) = "\" Then Exit For
Next
CheminBDD = Left$(NomBDD, I - 1)

'pour chaque feuille du fichier
For Each Feuille In Worksheets

'si le nom de la feuille commence par TCD
If UCase(Left(Feuille.Name, 4)) = "TCD_" Then
For J = Feuille.PivotTables.Count To 1 Step -1
'#
'# C'est là que je voudrais trouver le moyen
'# de définir le chemin de ma source à partir
'# des variables NomBDD et CheminBDD
Next J
End If
Next Feuille

End Sub
-------------------------------------------

En mode d'enregistrement automatique de macro, la méthode Excelabo donne
cela en VB (après avoir selectionné une cellule du TCD dont la source
est à mettre à jour) :

ActiveSheet.PivotTableWizard SourceType:=xlExternal,
SourceData:=Array( _
"SELECT BDD.Numéro, BDD.`Code ACP`, BDD.Région, BDD.Point,
BDD.Bassin, BDD.Age, BDD.Campagne, BDD.Opération, BDD.`poids moyen`,
BDD.`IC95%`, BDD.morts, BDD.vivants, BDD.enlevés, BDD.trouvés, BDD.remis," _
, _
" BDD.`Durée (j)`, BDD.Mortalité, BDD.`IC 95%`, BDD.Pds_coq,
BDD.Pds_coq_IC95, BDD.Coef_ep, BDD.Coef_ep_IC95, BDD.Gelatine,
BDD.GelatineIC95, BDD.Polydora, BDD.Polydora_IC95, BDD.Ind_chair,
BDD.Ind_cha" _
, _
"ir_IC95, BDD.Tx_chair_sec, BDD.Tx_chair_sec_IC95,
BDD.Maturite, BDD.s_ColLineage, BDD.s_Generation, BDD.s_GUID,
BDD.s_Lineage" & Chr(13) & "" & Chr(10) & "FROM BDD BDD" _
), Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Documents and
Settings\xxx\BDD\BDD_REMONOR.mdb;DefaultDir=C:\Documents and
Settings\xxx\BDD;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;" _
))


(Je ne sais pas si c'est très compréhensible.....)


BREF, voilà les éléments en ma possession (qui restent pour moi assez
obscures), mais si parmi vous ca semble limpide (on ne sait jamais),
alors vous aurez par avance tous mes remerciements pour les conseils...

Merci..

Michel
Signaler un problème avec ce contenu

10 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
1 2
Avatar
francois.forcet
Salut Michel

Si tu peux utiliser le code de l'enregistreur de macro pour actualiser
les cellules des TDC de l'adresse de la base access il suffit de
modifier cette partie :

"ODBC;DSN=MS Access Database;DBQ=C:Documents and
SettingsxxxBDDBDD_REMONOR.mdb;DefaultDir=C:Documents and
SettingsxxxBDD;DriverId%;FIL=MS
Access;MaxBufferSize 48;PageTimeout=5;UID­min;" _
))
ainsi :

"ODBC;DSN=MS Access Database;DBQ=" & CheminBDD & NomBDD &
";DefaultDir=" & Mid(CheminBDD,1,Len(CheminBDD)-1) &
";DriverId%;FIL=MS
Access;MaxBufferSize 48;PageTimeout=5;UID­min;" _
))


Je n'ai évidemment pas pu tester
Fais des essais et dis moi !!!!
Avatar
michel
Bonjour François et merci de te pencher sur la question...

J'ai en effet immédiatement essayer de remplacer les chemins "en dur"
par mes variables dans le code Macro.
Mais ca ne marche que dans la mesure ou la cellule active est à
l'intérieur d'un TCD. Et comme mes différents TCD n'occupent pas les
mêmes emplacements dans mes différentes feuilles....

Et je ne trouve pas comment (tout simplement) selectionner mes TCD via
VBA....

C'est là que je bloque...

Mais merci qd meme...

M.



a écrit :
Salut Michel

Si tu peux utiliser le code de l'enregistreur de macro pour actualiser
les cellules des TDC de l'adresse de la base access il suffit de
modifier cette partie :

"ODBC;DSN=MS Access Database;DBQ=C:Documents and
SettingsxxxBDDBDD_REMONOR.mdb;DefaultDir=C:Documents and
SettingsxxxBDD;DriverId%;FIL=MS
Access;MaxBufferSize 48;PageTimeout=5;UID­min;" _
))
ainsi :

"ODBC;DSN=MS Access Database;DBQ=" & CheminBDD & NomBDD &
";DefaultDir=" & Mid(CheminBDD,1,Len(CheminBDD)-1) &
";DriverId%;FIL=MS
Access;MaxBufferSize 48;PageTimeout=5;UID­min;" _
))


Je n'ai évidemment pas pu tester
Fais des essais et dis moi !!!!


Avatar
francois.forcet
Rebonjours Michel
Si tu veux que ta cellule active soit dans la Feuille d'un TCD tu peux
rajouter à cette partie :

For Each Feuille In Worksheets
'si le nom de la feuille commence par TCD
If UCase(Left(Feuille.Name, 4)) = "TCD_" Then

la ligne :

Sheets(Feuille.Name).Activate

ce qui donne :

For Each Feuille In Worksheets
'si le nom de la feuille commence par TCD
If UCase(Left(Feuille.Name, 4)) = "TCD_" Then
Sheets(Feuille.Name).Activate

et la suite du code ....

la cellule active deviendra une des cellules d'une Feuille TCD

Celà devrait convenir

Dis moi !!!!
Avatar
isabelle
bonjour Michel,

si tu mets le tcd1 comme source de données des autre tcd
tu n'aura qu'à modifier le chemin de la source du tcd1 pour mettre à jour tous les tcd.


isabelle


michel a écrit :
Bonjour à tous....

Un peu las de tourner en rond depuis de nombreuses heures, et après
avoir longuement étudié la page d'EXCELABO dédié à cette question
(Changer la source (lien ODBC) d'un tableau croisé dynamique), je me
tourne vers vous...
Voici mon prb.

Sur différentes feuilles d'un fichier EXCEL, je trouve plusieurs TCD
(nombre variable selon les feuilles) qui tous sont liés à une même table
ACCESS située soit dans le même dossier que le fichier XLS soit dans un
dossier "fils".

Ce fichier (et la table associée) sont destinés à être utilisés sur
différents postes de travail, et, selon toute probabilité, à être
utilisés en copie locale sur chaque Poste.
Ca implique qu'à chaque copie du Fichier et de sa table associée, on
redéfinisse la source de tous les TCD en fonction du nouvelle
emplacement....

La solution manuelle, appliquée à 1 TCD, trouvée sur Excelabo
(http://www.excelabo.net/excel/tcddiv.php#sourcetcd) fonctionne très bien.

Mon objectif serait de pouvoir automatiser la chose pour chaque TCD de
toutes mes feuilles pour que ce soit transparent pour l'utilisateur....

Actuellement j'en suis là :

---------------------------------------------
Sub Mise_à_jours_lient_BDD()
'
Dim Dlg As FileDialog
Dim I As Integer, J As Integer
Dim Feuille As Worksheet

'Récupération du chemin complet de la table ACCESS par l'utilisateur
Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
Dlg.Show
NomBDD = Dlg.SelectedItems(1)

'extraction du chemin de dossier seul contenant la table Access
For I = Len(NomBDD) To 1 Step -1
If Mid$(NomBDD, I, 1) = "" Then Exit For
Next
CheminBDD = Left$(NomBDD, I - 1)

'pour chaque feuille du fichier
For Each Feuille In Worksheets

'si le nom de la feuille commence par TCD
If UCase(Left(Feuille.Name, 4)) = "TCD_" Then
For J = Feuille.PivotTables.Count To 1 Step -1
'#
'# C'est là que je voudrais trouver le moyen
'# de définir le chemin de ma source à partir
'# des variables NomBDD et CheminBDD
Next J
End If
Next Feuille

End Sub
-------------------------------------------

En mode d'enregistrement automatique de macro, la méthode Excelabo donne
cela en VB (après avoir selectionné une cellule du TCD dont la source
est à mettre à jour) :

ActiveSheet.PivotTableWizard SourceType:=xlExternal,
SourceData:=Array( _
"SELECT BDD.Numéro, BDD.`Code ACP`, BDD.Région, BDD.Point,
BDD.Bassin, BDD.Age, BDD.Campagne, BDD.Opération, BDD.`poids moyen`,
BDD.`IC95%`, BDD.morts, BDD.vivants, BDD.enlevés, BDD.trouvés,
BDD.remis," _
, _
" BDD.`Durée (j)`, BDD.Mortalité, BDD.`IC 95%`, BDD.Pds_coq,
BDD.Pds_coq_IC95, BDD.Coef_ep, BDD.Coef_ep_IC95, BDD.Gelatine,
BDD.GelatineIC95, BDD.Polydora, BDD.Polydora_IC95, BDD.Ind_chair,
BDD.Ind_cha" _
, _
"ir_IC95, BDD.Tx_chair_sec, BDD.Tx_chair_sec_IC95, BDD.Maturite,
BDD.s_ColLineage, BDD.s_Generation, BDD.s_GUID, BDD.s_Lineage" & Chr(13)
& "" & Chr(10) & "FROM BDD BDD" _
), Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:Documents and
SettingsxxxBDDBDD_REMONOR.mdb;DefaultDir=C:Documents and
SettingsxxxBDD;DriverId%;FIL=MS
Access;MaxBufferSize 48;PageTimeout=5;UID­min;" _
))


(Je ne sais pas si c'est très compréhensible.....)


BREF, voilà les éléments en ma possession (qui restent pour moi assez
obscures), mais si parmi vous ca semble limpide (on ne sait jamais),
alors vous aurez par avance tous mes remerciements pour les conseils...

Merci..

Michel


Avatar
michel
Bonjour MichDenis.....


Ca parait si simple que c'est foutrement séduisant (!!!!)

Je vais essayer de suite....



MichDenis a écrit :
As-tu essayé ceci, proposé par Microsoft :

http://support.microsoft.com/kb/327572

'------------------------------------
Sub ChangeServer()

Dim sh As Worksheet, qy As QueryTable
Dim pt As PivotTable, pc As PivotCache
Dim OldPath As String, NewPath As String
Dim strOld As String, strNew As String

' Replace the following paths with the original path or
' server name where your database resided, and the new
' path or server name where your database now resides.

OldPath = "<old server name>"
NewPath = "<new server name>"
For Each sh In ActiveWorkbook.Sheets
For Each pt In sh.PivotTables
strOld = pt.PivotCache.Connection
strNew = Replace(strOld, OldPath, NewPath)
pt.PivotCache.Connection = strNew
pt.PivotCache.Refresh
Next pt
Next sh
End Sub
'------------------------------------





Avatar
michel
En fait sur chaque feuille "TCD" de mon classeur, je trouve entre 3 et 5
TCD répartis horizontalement sur la feuille....

Chaque TCD est nommé (TCD1, TCD2, TCD3 ...etc).

Au total j'ai donc (en moyenne) 4 TCD × 4 Feuilles "TCD" soit 16 TCD.

Et il ne suffit malheureusement pas d'activé la feuille mais bien LE TCD
pour que mon code macro passe sans erreur.
Si ma cellule active n'est pas à l'intérieur du TCD à traiter alors
Excel m'en crée un nouveau sans se précoccupé de ce qui est déjà
existant.....

BREF un vrai casse-tête...

M

a écrit :
Rebonjours Michel
Si tu veux que ta cellule active soit dans la Feuille d'un TCD tu peux
rajouter à cette partie :

For Each Feuille In Worksheets
'si le nom de la feuille commence par TCD
If UCase(Left(Feuille.Name, 4)) = "TCD_" Then

la ligne :

Sheets(Feuille.Name).Activate

ce qui donne :

For Each Feuille In Worksheets
'si le nom de la feuille commence par TCD
If UCase(Left(Feuille.Name, 4)) = "TCD_" Then
Sheets(Feuille.Name).Activate

et la suite du code ....

la cellule active deviendra une des cellules d'une Feuille TCD

Celà devrait convenir

Dis moi !!!!


Avatar
michel
?????????? Heuuuuuuuuuuuu !!!!!!!!!
(comment j'ai pas pensé à ca dès le départ ?????)

Je me sents tout bête..... :o)))
Ca devrait bien simplifier le prb car je n'ai plus qu'1 seul TCD à
mettre à jour



MERCI !!!!!





isabelle a écrit :
bonjour Michel,

si tu mets le tcd1 comme source de données des autre tcd
tu n'aura qu'à modifier le chemin de la source du tcd1 pour mettre à
jour tous les tcd.


isabelle


michel a écrit :
Bonjour à tous....

Un peu las de tourner en rond depuis de nombreuses heures, et après
avoir longuement étudié la page d'EXCELABO dédié à cette question
(Changer la source (lien ODBC) d'un tableau croisé dynamique), je me
tourne vers vous...
Voici mon prb.

Sur différentes feuilles d'un fichier EXCEL, je trouve plusieurs TCD
(nombre variable selon les feuilles) qui tous sont liés à une même
table ACCESS située soit dans le même dossier que le fichier XLS soit
dans un dossier "fils".

Ce fichier (et la table associée) sont destinés à être utilisés sur
différents postes de travail, et, selon toute probabilité, à être
utilisés en copie locale sur chaque Poste.
Ca implique qu'à chaque copie du Fichier et de sa table associée, on
redéfinisse la source de tous les TCD en fonction du nouvelle
emplacement....

La solution manuelle, appliquée à 1 TCD, trouvée sur Excelabo
(http://www.excelabo.net/excel/tcddiv.php#sourcetcd) fonctionne très
bien.

Mon objectif serait de pouvoir automatiser la chose pour chaque TCD de
toutes mes feuilles pour que ce soit transparent pour l'utilisateur....

Actuellement j'en suis là :

---------------------------------------------
Sub Mise_à_jours_lient_BDD()
'
Dim Dlg As FileDialog
Dim I As Integer, J As Integer
Dim Feuille As Worksheet

'Récupération du chemin complet de la table ACCESS par l'utilisateur
Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
Dlg.Show
NomBDD = Dlg.SelectedItems(1)

'extraction du chemin de dossier seul contenant la table Access
For I = Len(NomBDD) To 1 Step -1
If Mid$(NomBDD, I, 1) = "" Then Exit For
Next
CheminBDD = Left$(NomBDD, I - 1)

'pour chaque feuille du fichier
For Each Feuille In Worksheets

'si le nom de la feuille commence par TCD
If UCase(Left(Feuille.Name, 4)) = "TCD_" Then
For J = Feuille.PivotTables.Count To 1 Step -1
'#
'# C'est là que je voudrais trouver le moyen
'# de définir le chemin de ma source à partir
'# des variables NomBDD et CheminBDD
Next J
End If
Next Feuille

End Sub
-------------------------------------------

En mode d'enregistrement automatique de macro, la méthode Excelabo
donne cela en VB (après avoir selectionné une cellule du TCD dont la
source est à mettre à jour) :

ActiveSheet.PivotTableWizard SourceType:=xlExternal,
SourceData:=Array( _
"SELECT BDD.Numéro, BDD.`Code ACP`, BDD.Région, BDD.Point,
BDD.Bassin, BDD.Age, BDD.Campagne, BDD.Opération, BDD.`poids moyen`,
BDD.`IC95%`, BDD.morts, BDD.vivants, BDD.enlevés, BDD.trouvés,
BDD.remis," _
, _
" BDD.`Durée (j)`, BDD.Mortalité, BDD.`IC 95%`, BDD.Pds_coq,
BDD.Pds_coq_IC95, BDD.Coef_ep, BDD.Coef_ep_IC95, BDD.Gelatine,
BDD.GelatineIC95, BDD.Polydora, BDD.Polydora_IC95, BDD.Ind_chair,
BDD.Ind_cha" _
, _
"ir_IC95, BDD.Tx_chair_sec, BDD.Tx_chair_sec_IC95,
BDD.Maturite, BDD.s_ColLineage, BDD.s_Generation, BDD.s_GUID,
BDD.s_Lineage" & Chr(13) & "" & Chr(10) & "FROM BDD BDD" _
), Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:Documents and
SettingsxxxBDDBDD_REMONOR.mdb;DefaultDir=C:Documents and
SettingsxxxBDD;DriverId%;FIL=MS
Access;MaxBufferSize 48;PageTimeout=5;UID­min;" _
))


(Je ne sais pas si c'est très compréhensible.....)


BREF, voilà les éléments en ma possession (qui restent pour moi assez
obscures), mais si parmi vous ca semble limpide (on ne sait jamais),
alors vous aurez par avance tous mes remerciements pour les conseils...

Merci..

Michel




Avatar
michel
Le code s'intègre très bien dans ma macro (pas de plantage)

Mais le résultat ne semble pas là..... la mise à jour ne parait pas se
faire......

Mais tu as peut être vu qu'en dessous le fil se poursuit et qu'à priori,
je n'ai plus qu'1 seul TCD sur les 16 à modifier (les 15 autres sont
relié au premier...)

merci...

M.


MichDenis a écrit :
As-tu essayé ceci, proposé par Microsoft :

http://support.microsoft.com/kb/327572

'------------------------------------
Sub ChangeServer()

Dim sh As Worksheet, qy As QueryTable
Dim pt As PivotTable, pc As PivotCache
Dim OldPath As String, NewPath As String
Dim strOld As String, strNew As String

' Replace the following paths with the original path or
' server name where your database resided, and the new
' path or server name where your database now resides.

OldPath = "<old server name>"
NewPath = "<new server name>"
For Each sh In ActiveWorkbook.Sheets
For Each pt In sh.PivotTables
strOld = pt.PivotCache.Connection
strNew = Replace(strOld, OldPath, NewPath)
pt.PivotCache.Connection = strNew
pt.PivotCache.Refresh
Next pt
Next sh
End Sub
'------------------------------------




"michel" a écrit dans le message de news: %
Bonjour à tous....

Un peu las de tourner en rond depuis de nombreuses heures, et après
avoir longuement étudié la page d'EXCELABO dédié à cette question
(Changer la source (lien ODBC) d'un tableau croisé dynamique), je me
tourne vers vous...
Voici mon prb.

Sur différentes feuilles d'un fichier EXCEL, je trouve plusieurs TCD
(nombre variable selon les feuilles) qui tous sont liés à une même table
ACCESS située soit dans le même dossier que le fichier XLS soit dans un
dossier "fils".

Ce fichier (et la table associée) sont destinés à être utilisés sur
différents postes de travail, et, selon toute probabilité, à être
utilisés en copie locale sur chaque Poste.
Ca implique qu'à chaque copie du Fichier et de sa table associée, on
redéfinisse la source de tous les TCD en fonction du nouvelle
emplacement....

La solution manuelle, appliquée à 1 TCD, trouvée sur Excelabo
(http://www.excelabo.net/excel/tcddiv.php#sourcetcd) fonctionne très bien.

Mon objectif serait de pouvoir automatiser la chose pour chaque TCD de
toutes mes feuilles pour que ce soit transparent pour l'utilisateur....

Actuellement j'en suis là :

---------------------------------------------
Sub Mise_à_jours_lient_BDD()
'
Dim Dlg As FileDialog
Dim I As Integer, J As Integer
Dim Feuille As Worksheet

'Récupération du chemin complet de la table ACCESS par l'utilisateur
Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
Dlg.Show
NomBDD = Dlg.SelectedItems(1)

'extraction du chemin de dossier seul contenant la table Access
For I = Len(NomBDD) To 1 Step -1
If Mid$(NomBDD, I, 1) = "" Then Exit For
Next
CheminBDD = Left$(NomBDD, I - 1)

'pour chaque feuille du fichier
For Each Feuille In Worksheets

'si le nom de la feuille commence par TCD
If UCase(Left(Feuille.Name, 4)) = "TCD_" Then
For J = Feuille.PivotTables.Count To 1 Step -1
'#
'# C'est là que je voudrais trouver le moyen
'# de définir le chemin de ma source à partir
'# des variables NomBDD et CheminBDD
Next J
End If
Next Feuille

End Sub
-------------------------------------------

En mode d'enregistrement automatique de macro, la méthode Excelabo donne
cela en VB (après avoir selectionné une cellule du TCD dont la source
est à mettre à jour) :

ActiveSheet.PivotTableWizard SourceType:=xlExternal,
SourceData:=Array( _
"SELECT BDD.Numéro, BDD.`Code ACP`, BDD.Région, BDD.Point,
BDD.Bassin, BDD.Age, BDD.Campagne, BDD.Opération, BDD.`poids moyen`,
BDD.`IC95%`, BDD.morts, BDD.vivants, BDD.enlevés, BDD.trouvés, BDD.remis," _
, _
" BDD.`Durée (j)`, BDD.Mortalité, BDD.`IC 95%`, BDD.Pds_coq,
BDD.Pds_coq_IC95, BDD.Coef_ep, BDD.Coef_ep_IC95, BDD.Gelatine,
BDD.GelatineIC95, BDD.Polydora, BDD.Polydora_IC95, BDD.Ind_chair,
BDD.Ind_cha" _
, _
"ir_IC95, BDD.Tx_chair_sec, BDD.Tx_chair_sec_IC95,
BDD.Maturite, BDD.s_ColLineage, BDD.s_Generation, BDD.s_GUID,
BDD.s_Lineage" & Chr(13) & "" & Chr(10) & "FROM BDD BDD" _
), Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:Documents and
SettingsxxxBDDBDD_REMONOR.mdb;DefaultDir=C:Documents and
SettingsxxxBDD;DriverId%;FIL=MS
Access;MaxBufferSize 48;PageTimeout=5;UID­min;" _
))


(Je ne sais pas si c'est très compréhensible.....)


BREF, voilà les éléments en ma possession (qui restent pour moi assez
obscures), mais si parmi vous ca semble limpide (on ne sait jamais),
alors vous aurez par avance tous mes remerciements pour les conseils...

Merci..

Michel


Avatar
michel
Bonjour MichDenis.....

En effet c'est sur cette piste que j'ai du arrêter hier soir....

Ce matin, j'ai (enfin !) compris comment selectionner en VBA mon TCD (en
nommant "TCD1" la première cellule du TCD en haut à gauche)

Ensuite j'ai appliqué cette petite routine (adapté de l'aide VBA d'Excel
à la page "SourceData"):
***************
Sub Test()
'ActiveSheet.Range("TCD1").Select
sdArray = Worksheets("TCD").Range("TCD1").PivotTable.SourceData
Set NewSheet = ActiveWorkbook.Worksheets.Add
For I = LBound(sdArray) To UBound(sdArray)
NewSheet.Cells(I, 1) = sdArray(I)
Next I
End Sub
****************

Elle me colle les chaines suivantes :

DSN=MS Access Database;DBQ=M:REMORAsynth
Pluri-annuelle(SMEL)LoïcBDDBDD_REMONOR_BV02.mdb;DefaultDir=M:REMORAsynth
Pluri-annuelle (SMEL)LoïcBDD;DriverId%;FIL=MS
Access;MaxBufferSize 48;PageTimeout=5;

SELECT BDD.Numéro, BDD.`Code ACP`, BDD.Région, BDD.Point, BDD.Bassin,
BDD.Age, BDD.Campagne, BDD.Opération, BDD.`poids moyen`, BDD.`IC95%`,
BDD.morts, BDD.vivants, BDD.enlevés, BDD.trouvés, BDD.remis, BDD.`Durée
(j)`, BDD.Mortalité, BDD.`IC 95%`, BDD.Pds_coq, BDD.Pds_coq_IC95,
BDD.Coef_ep, BDD.Coef_ep_IC95, BDD.Gelatine, BDD.GelatineIC95,
BDD.Polydora, BDD.Polydora_IC95, BDD.Ind_chair, BDD.Ind_chair_IC95,
BDD.Tx_chair_sec, BDD.Tx_chair_sec_IC95, BDD.Maturite, BDD.s_ColLineage,
BDD.s_Generation, BDD.s_GUID, BDD.s_Lineage
FROM `M:REMORAsynth Pluri-annuelle SMEL)LoïcBDDBDD_REMONOR_BV02`.BD"
D BDD




J'ai ensuite appliqué la routine que tu m'as envoyé...
Le résultat semble convainquant :

ODBC;DSN=MS Access Database;DBQ=M:REMORAsynth Pluri-annuelle
(SMEL)LoïcBDDBDD_REMONOR_BV02.mdb;DefaultDir=M:REMORAsynth
Pluri-annuelle (SMEL)LoïcBDD;DriverId%;FIL=MS
Access;MaxBufferSize 48;PageTimeout=5;



Je pense que je suis donc sur la BONNE VOIE....



Encore mille merci pour tous ces conseils !

Michel



MichDenis a écrit :
La procédure soumise boucle sur les PivotCaches()
et non sur les TDC.

Si tous les TDC de ton application utilise le même PivotCache
Alors il n'y a qu'une modification à faire.

Le PivotCache c'est l'antémémoire des TDC !

As-tu essayé d'insérer le contenu (c'est une chaîne de caractères)
de la connection dans une cellule. Si ça fait plusieurs procédures
que tu as utilisées pour triturer cette chaîne de connection, elle
a probablement perdu de la fraîcheur...;-) ça te permettrait de voir
si ce que tu tentes de remplacer existe vraiment dans la chaîne de
connection :

Pour ce faire :

Sub ChangeServer()

Dim sh As Worksheet, qy As QueryTable
Dim pt As PivotTable, pc As PivotCache
Dim OldPath As String, NewPath As String
Dim strOld As String, strNew As String

' Replace the following paths with the original path or
' server name where your database resided, and the new
' path or server name where your database now resides.

OldPath = "<old server name>"
NewPath = "<new server name>"
For Each sh In ActiveWorkbook.Sheets
For Each pt In sh.PivotTables
strOld = pt.PivotCache.Connection
'-------------------
A = A+1
RAnge("A" & A ) = strOld
'-------------------
'strNew = Replace(strOld, OldPath, NewPath)
' pt.PivotCache.Connection = strNew
' pt.PivotCache.Refresh
Next pt
Next sh
End Sub


"michel" a écrit dans le message de news:
Le code s'intègre très bien dans ma macro (pas de plantage)

Mais le résultat ne semble pas là..... la mise à jour ne parait pas se
faire......

Mais tu as peut être vu qu'en dessous le fil se poursuit et qu'à priori,
je n'ai plus qu'1 seul TCD sur les 16 à modifier (les 15 autres sont
relié au premier...)

merci...

M.


MichDenis a écrit :
As-tu essayé ceci, proposé par Microsoft :

http://support.microsoft.com/kb/327572

'------------------------------------
Sub ChangeServer()

Dim sh As Worksheet, qy As QueryTable
Dim pt As PivotTable, pc As PivotCache
Dim OldPath As String, NewPath As String
Dim strOld As String, strNew As String

' Replace the following paths with the original path or
' server name where your database resided, and the new
' path or server name where your database now resides.

OldPath = "<old server name>"
NewPath = "<new server name>"
For Each sh In ActiveWorkbook.Sheets
For Each pt In sh.PivotTables
strOld = pt.PivotCache.Connection
strNew = Replace(strOld, OldPath, NewPath)
pt.PivotCache.Connection = strNew
pt.PivotCache.Refresh
Next pt
Next sh
End Sub
'------------------------------------




"michel" a écrit dans le message de news: %
Bonjour à tous....

Un peu las de tourner en rond depuis de nombreuses heures, et après
avoir longuement étudié la page d'EXCELABO dédié à cette question
(Changer la source (lien ODBC) d'un tableau croisé dynamique), je me
tourne vers vous...
Voici mon prb.

Sur différentes feuilles d'un fichier EXCEL, je trouve plusieurs TCD
(nombre variable selon les feuilles) qui tous sont liés à une même table
ACCESS située soit dans le même dossier que le fichier XLS soit dans un
dossier "fils".

Ce fichier (et la table associée) sont destinés à être utilisés sur
différents postes de travail, et, selon toute probabilité, à être
utilisés en copie locale sur chaque Poste.
Ca implique qu'à chaque copie du Fichier et de sa table associée, on
redéfinisse la source de tous les TCD en fonction du nouvelle
emplacement....

La solution manuelle, appliquée à 1 TCD, trouvée sur Excelabo
(http://www.excelabo.net/excel/tcddiv.php#sourcetcd) fonctionne très bien.

Mon objectif serait de pouvoir automatiser la chose pour chaque TCD de
toutes mes feuilles pour que ce soit transparent pour l'utilisateur....

Actuellement j'en suis là :

---------------------------------------------
Sub Mise_à_jours_lient_BDD()
'
Dim Dlg As FileDialog
Dim I As Integer, J As Integer
Dim Feuille As Worksheet

'Récupération du chemin complet de la table ACCESS par l'utilisateur
Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
Dlg.Show
NomBDD = Dlg.SelectedItems(1)

'extraction du chemin de dossier seul contenant la table Access
For I = Len(NomBDD) To 1 Step -1
If Mid$(NomBDD, I, 1) = "" Then Exit For
Next
CheminBDD = Left$(NomBDD, I - 1)

'pour chaque feuille du fichier
For Each Feuille In Worksheets

'si le nom de la feuille commence par TCD
If UCase(Left(Feuille.Name, 4)) = "TCD_" Then
For J = Feuille.PivotTables.Count To 1 Step -1
'#
'# C'est là que je voudrais trouver le moyen
'# de définir le chemin de ma source à partir
'# des variables NomBDD et CheminBDD
Next J
End If
Next Feuille

End Sub
-------------------------------------------

En mode d'enregistrement automatique de macro, la méthode Excelabo donne
cela en VB (après avoir selectionné une cellule du TCD dont la source
est à mettre à jour) :

ActiveSheet.PivotTableWizard SourceType:=xlExternal,
SourceData:=Array( _
"SELECT BDD.Numéro, BDD.`Code ACP`, BDD.Région, BDD.Point,
BDD.Bassin, BDD.Age, BDD.Campagne, BDD.Opération, BDD.`poids moyen`,
BDD.`IC95%`, BDD.morts, BDD.vivants, BDD.enlevés, BDD.trouvés, BDD.remis," _
, _
" BDD.`Durée (j)`, BDD.Mortalité, BDD.`IC 95%`, BDD.Pds_coq,
BDD.Pds_coq_IC95, BDD.Coef_ep, BDD.Coef_ep_IC95, BDD.Gelatine,
BDD.GelatineIC95, BDD.Polydora, BDD.Polydora_IC95, BDD.Ind_chair,
BDD.Ind_cha" _
, _
"ir_IC95, BDD.Tx_chair_sec, BDD.Tx_chair_sec_IC95,
BDD.Maturite, BDD.s_ColLineage, BDD.s_Generation, BDD.s_GUID,
BDD.s_Lineage" & Chr(13) & "" & Chr(10) & "FROM BDD BDD" _
), Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:Documents and
SettingsxxxBDDBDD_REMONOR.mdb;DefaultDir=C:Documents and
SettingsxxxBDD;DriverId%;FIL=MS
Access;MaxBufferSize 48;PageTimeout=5;UID­min;" _
))


(Je ne sais pas si c'est très compréhensible.....)


BREF, voilà les éléments en ma possession (qui restent pour moi assez
obscures), mais si parmi vous ca semble limpide (on ne sait jamais),
alors vous aurez par avance tous mes remerciements pour les conseils...

Merci..

Michel




Avatar
michel
Re Salut MichDenis..... (je crois que je ne serai jamais blasé de la
puissance et de la qualité de ce forum.....:o)

J'ai passé ma journée sur cette affaire, pour arriver à la conclusion
(juste avant ton dernier post) que le code macro fonctionnait très
bien... MAIS ne suffisait pas car si en effet c'est efficace pour
modifier la chaine de connection, les données du TCD ne se mettait pas à
jour (et c'est bien à cause de la requète....)

J'étais donc bloqué là et j'envisageeait de baisser les bras quand ton
Post est arrivé....
du coup ca relance tout et je m'y replonge de suite...

encore merci et je te tient au courant....

M.


MichDenis a écrit :
Après un petit test, la modification de la source de donnée
pour un TDC est semblable à la modification de la source
pour un QueryTable. De fait, il n'y a que l'objet qui est différent.

TDC = PivotTable.PivotCache
QueryTable = QueryTable

En supposant que tu modifies le chemin où se trouve la base de donnée .MDB
Tu dois modifier 2 choses
a ) La chaîne de connection du PivotCache : Connection
b ) La chaîne de connection dde la requête : CommandText

Voici ce que donne la procédure soumise hier à cet égat

P.S- Il faut être parcimonieux sur la syntaxe que l'on veut modifier

'J'ai fait un tout petit test et cela fonctionne très bien!
'---------------------------------------------------------
Sub ChangerCheminFichierSource_Pour_TDC()

Dim Sh As Worksheet, Pt As PivotTable
Dim OldConn As String, OldRepertoreParDefaut As String
Dim OldCheminComplet As String, NewRepParDefaut As String
Dim NewCheminComplet As String, NewConn As String

Dim oldQuery As String, OldWay As String
Dim NewWay As String, NewQuery As String

'***************************************************
'Variable à renseigner pour modifier la connection
'Répertoire par défaut du fichier .mdb lors de la connection
OldRepertoreParDefaut = "DefaultDir=C:"
'Chemin complet + nom base de donnée + extention
OldCheminComplet = "DBQ=C:Comptoir.mdb"
'Nouveau répertoire par défaut
NewRepParDefaut = "DefaultDir=C:AAA"
'Nouveau Chemin complet + nom base de donnée + extension
NewCheminComplet = "DBQ=C:AAAComptoir.mdb"
'DefaultDir et DBQ ont été incluses dans la chaîne afin
'd'éviter toute confusion possible lors de la substituion
'***************************************************

'Variable à renseigner pour modifier la requête
'ou propriété : CommandText du PivotCache
'Ancien chemin complet de la base de donnée SANS L'EXTENSION
OldWay = "C:Comptoir" '<<========== > 'Nouveau chemin complet + nom de la base de donnée SANS l'EXTENSION
NewWay = "c:AAAComptoir" '<<========== > '*****************************************

For Each Sh In ActiveWorkbook.Sheets
For Each Pt In Sh.PivotTables
'Récupérée l'ancienne connection
OldConn = Pt.PivotCache.Connection
'Substitution pour la nouvelle localisation
'La fonction Replace existe depuis Excel 2002
'Si pas disponible utiliser : Application.Substitute()
NewConn = Replace(OldConn, OldRepertoreParDefaut, _
NewRepParDefaut, 1, , vbTextCompare)
NewConn = Replace(NewConn, OldCheminComplet, _
NewCheminComplet, 1, , vbTextCompare)
Pt.PivotCache.Connection = NewConn

'Modifier la requête
'Extraire la chaîne de la requête
oldQuery = Pt.PivotCache.CommandText
'Substitution
NewQuery = Replace(oldQuery, OldWay, NewWay, 1, , vbTextCompare)
Pt.PivotCache.CommandText = NewQuery
'Mise à jour de la nouvelle information
Pt.PivotCache.Refresh
Next Pt
Next Sh
End Sub
'---------------------------------------------------------------


"michel" a écrit dans le message de news:
Bonjour MichDenis.....

En effet c'est sur cette piste que j'ai du arrêter hier soir....

Ce matin, j'ai (enfin !) compris comment selectionner en VBA mon TCD (en
nommant "TCD1" la première cellule du TCD en haut à gauche)

Ensuite j'ai appliqué cette petite routine (adapté de l'aide VBA d'Excel
à la page "SourceData"):
***************
Sub Test()
'ActiveSheet.Range("TCD1").Select
sdArray = Worksheets("TCD").Range("TCD1").PivotTable.SourceData
Set NewSheet = ActiveWorkbook.Worksheets.Add
For I = LBound(sdArray) To UBound(sdArray)
NewSheet.Cells(I, 1) = sdArray(I)
Next I
End Sub
****************

Elle me colle les chaines suivantes :

DSN=MS Access Database;DBQ=M:REMORAsynth
Pluri-annuelle(SMEL)LoïcBDDBDD_REMONOR_BV02.mdb;DefaultDir=M:REMORAsynth
Pluri-annuelle (SMEL)LoïcBDD;DriverId%;FIL=MS
Access;MaxBufferSize 48;PageTimeout=5;

SELECT BDD.Numéro, BDD.`Code ACP`, BDD.Région, BDD.Point, BDD.Bassin,
BDD.Age, BDD.Campagne, BDD.Opération, BDD.`poids moyen`, BDD.`IC95%`,
BDD.morts, BDD.vivants, BDD.enlevés, BDD.trouvés, BDD.remis, BDD.`Durée
(j)`, BDD.Mortalité, BDD.`IC 95%`, BDD.Pds_coq, BDD.Pds_coq_IC95,
BDD.Coef_ep, BDD.Coef_ep_IC95, BDD.Gelatine, BDD.GelatineIC95,
BDD.Polydora, BDD.Polydora_IC95, BDD.Ind_chair, BDD.Ind_chair_IC95,
BDD.Tx_chair_sec, BDD.Tx_chair_sec_IC95, BDD.Maturite, BDD.s_ColLineage,
BDD.s_Generation, BDD.s_GUID, BDD.s_Lineage
FROM `M:REMORAsynth Pluri-annuelle SMEL)LoïcBDDBDD_REMONOR_BV02`.BD"
D BDD




J'ai ensuite appliqué la routine que tu m'as envoyé...
Le résultat semble convainquant :

ODBC;DSN=MS Access Database;DBQ=M:REMORAsynth Pluri-annuelle
(SMEL)LoïcBDDBDD_REMONOR_BV02.mdb;DefaultDir=M:REMORAsynth
Pluri-annuelle (SMEL)LoïcBDD;DriverId%;FIL=MS
Access;MaxBufferSize 48;PageTimeout=5;



Je pense que je suis donc sur la BONNE VOIE....



Encore mille merci pour tous ces conseils !

Michel



MichDenis a écrit :
La procédure soumise boucle sur les PivotCaches()
et non sur les TDC.

Si tous les TDC de ton application utilise le même PivotCache
Alors il n'y a qu'une modification à faire.

Le PivotCache c'est l'antémémoire des TDC !

As-tu essayé d'insérer le contenu (c'est une chaîne de caractères)
de la connection dans une cellule. Si ça fait plusieurs procédures
que tu as utilisées pour triturer cette chaîne de connection, elle
a probablement perdu de la fraîcheur...;-) ça te permettrait de voir
si ce que tu tentes de remplacer existe vraiment dans la chaîne de
connection :

Pour ce faire :

Sub ChangeServer()

Dim sh As Worksheet, qy As QueryTable
Dim pt As PivotTable, pc As PivotCache
Dim OldPath As String, NewPath As String
Dim strOld As String, strNew As String

' Replace the following paths with the original path or
' server name where your database resided, and the new
' path or server name where your database now resides.

OldPath = "<old server name>"
NewPath = "<new server name>"
For Each sh In ActiveWorkbook.Sheets
For Each pt In sh.PivotTables
strOld = pt.PivotCache.Connection
'-------------------
A = A+1
RAnge("A" & A ) = strOld
'-------------------
'strNew = Replace(strOld, OldPath, NewPath)
' pt.PivotCache.Connection = strNew
' pt.PivotCache.Refresh
Next pt
Next sh
End Sub


"michel" a écrit dans le message de news:
Le code s'intègre très bien dans ma macro (pas de plantage)

Mais le résultat ne semble pas là..... la mise à jour ne parait pas se
faire......

Mais tu as peut être vu qu'en dessous le fil se poursuit et qu'à priori,
je n'ai plus qu'1 seul TCD sur les 16 à modifier (les 15 autres sont
relié au premier...)

merci...

M.


MichDenis a écrit :
As-tu essayé ceci, proposé par Microsoft :

http://support.microsoft.com/kb/327572

'------------------------------------
Sub ChangeServer()

Dim sh As Worksheet, qy As QueryTable
Dim pt As PivotTable, pc As PivotCache
Dim OldPath As String, NewPath As String
Dim strOld As String, strNew As String

' Replace the following paths with the original path or
' server name where your database resided, and the new
' path or server name where your database now resides.

OldPath = "<old server name>"
NewPath = "<new server name>"
For Each sh In ActiveWorkbook.Sheets
For Each pt In sh.PivotTables
strOld = pt.PivotCache.Connection
strNew = Replace(strOld, OldPath, NewPath)
pt.PivotCache.Connection = strNew
pt.PivotCache.Refresh
Next pt
Next sh
End Sub
'------------------------------------




"michel" a écrit dans le message de news: %
Bonjour à tous....

Un peu las de tourner en rond depuis de nombreuses heures, et après
avoir longuement étudié la page d'EXCELABO dédié à cette question
(Changer la source (lien ODBC) d'un tableau croisé dynamique), je me
tourne vers vous...
Voici mon prb.

Sur différentes feuilles d'un fichier EXCEL, je trouve plusieurs TCD
(nombre variable selon les feuilles) qui tous sont liés à une même table
ACCESS située soit dans le même dossier que le fichier XLS soit dans un
dossier "fils".

Ce fichier (et la table associée) sont destinés à être utilisés sur
différents postes de travail, et, selon toute probabilité, à être
utilisés en copie locale sur chaque Poste.
Ca implique qu'à chaque copie du Fichier et de sa table associée, on
redéfinisse la source de tous les TCD en fonction du nouvelle
emplacement....

La solution manuelle, appliquée à 1 TCD, trouvée sur Excelabo
(http://www.excelabo.net/excel/tcddiv.php#sourcetcd) fonctionne très bien.

Mon objectif serait de pouvoir automatiser la chose pour chaque TCD de
toutes mes feuilles pour que ce soit transparent pour l'utilisateur....

Actuellement j'en suis là :

---------------------------------------------
Sub Mise_à_jours_lient_BDD()
'
Dim Dlg As FileDialog
Dim I As Integer, J As Integer
Dim Feuille As Worksheet

'Récupération du chemin complet de la table ACCESS par l'utilisateur
Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
Dlg.Show
NomBDD = Dlg.SelectedItems(1)

'extraction du chemin de dossier seul contenant la table Access
For I = Len(NomBDD) To 1 Step -1
If Mid$(NomBDD, I, 1) = "" Then Exit For
Next
CheminBDD = Left$(NomBDD, I - 1)

'pour chaque feuille du fichier
For Each Feuille In Worksheets

'si le nom de la feuille commence par TCD
If UCase(Left(Feuille.Name, 4)) = "TCD_" Then
For J = Feuille.PivotTables.Count To 1 Step -1
'#
'# C'est là que je voudrais trouver le moyen
'# de définir le chemin de ma source à partir
'# des variables NomBDD et CheminBDD
Next J
End If
Next Feuille

End Sub
-------------------------------------------

En mode d'enregistrement automatique de macro, la méthode Excelabo donne
cela en VB (après avoir selectionné une cellule du TCD dont la source
est à mettre à jour) :

ActiveSheet.PivotTableWizard SourceType:=xlExternal,
SourceData:=Array( _
"SELECT BDD.Numéro, BDD.`Code ACP`, BDD.Région, BDD.Point,
BDD.Bassin, BDD.Age, BDD.Campagne, BDD.Opération, BDD.`poids moyen`,
BDD.`IC95%`, BDD.morts, BDD.vivants, BDD.enlevés, BDD.trouvés, BDD.remis," _
, _
" BDD.`Durée (j)`, BDD.Mortalité, BDD.`IC 95%`, BDD.Pds_coq,
BDD.Pds_coq_IC95, BDD.Coef_ep, BDD.Coef_ep_IC95, BDD.Gelatine,
BDD.GelatineIC95, BDD.Polydora, BDD.Polydora_IC95, BDD.Ind_chair,
BDD.Ind_cha" _
, _
"ir_IC95, BDD.Tx_chair_sec, BDD.Tx_chair_sec_IC95,
BDD.Maturite, BDD.s_ColLineage, BDD.s_Generation, BDD.s_GUID,
BDD.s_Lineage" & Chr(13) & "" & Chr(10) & "FROM BDD BDD" _
), Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:Documents and
SettingsxxxBDDBDD_REMONOR.mdb;DefaultDir=C:Documents and
SettingsxxxBDD;DriverId%;FIL=MS
Access;MaxBufferSize 48;PageTimeout=5;UID­min;" _
))


(Je ne sais pas si c'est très compréhensible.....)


BREF, voilà les éléments en ma possession (qui restent pour moi assez
obscures), mais si parmi vous ca semble limpide (on ne sait jamais),
alors vous aurez par avance tous mes remerciements pour les conseils...

Merci..

Michel






1 2