récupérer des données par des formules sur des fichiers fermés -#Ref - Méthode

Version imprimable

18/10/2019, 11h10
mouftie

récupérer des données par des formules sur des fichiers fermés -#Ref - Méthode
Bonjour,
Je voudrais une méthode pour créer un tableau de bord, en allant chercher mes données dans différents fichiers.
Actuellement, j'ai créé mon tableau de bord, en ouvrant les fichiers sources, en faisant mes formules.
Une fois terminé, mes fichiers sources sont fermés, mon fichier TdB s'ouvre, tout est Ok.
Cependant, si on utilise un menu déroulant, les formules se transforment en #REF. puisque mes fichiers sources sont fermés.
Avant, j'ai eu utilisé enregistrement de l'environnement de travail... cela n'existe plus et je ne tiens pas que ceux qui consultent le TdB puissent modifier les classeurs sources.

Ma question : Comment faire un tableau de bord avec des données qui sont dans des fichiers externes ?
/I\ J'ai environ une 15e de fichiers sources
- Faire une copie de toutes les données à l'ouverture du fichier TdB, dans des feuilles masquées ?
- Faire une macro qui ouvre tous les fichiers / met à jour toutes les données / Ferme les fichiers sources à chaque filtre ?
- existe-t-il un autre moyen ?
- Utilisation ADO - mais je ne connais pas et ne suis pas capable, je crois.
J'ai fait beaucoup de forum, je ne trouve pas mon bonheur.
Merci de vos conseils
18/10/2019, 12h09
MarcelG

Bonjour mouftie,

Certes, il existe des process avancés pour ce type de développement, (ADO, Power Query...).
Personnellement, je ne les maîtrise pas.

Si l'on veut rester "basique".
En bouclant sur chaque classeur, tu ne dois pas copier les formules mais les valeurs par collage spécial.
Je te laisse consulter le lien.
19/10/2019, 04h24
mouftie

Bonjour Marcel,
Merci pour ta réponse.
Comme je l'ai dit dans mon message il s'agit d'un tableau de bord qui fonctionne avec une liste deroulante.
C'est à dire que les données changent selon le choix fait dans cette liste. Le TdB est constitué de formules de type 'Index Equiv' et de 'SommeProd'.
Si j'ai bien compris ta proposition, tu veux que je fasse une feuille masquée, avec l'ensemble de mes données nécessaires, puis que j'en fasse un copier / coller les valeurs. Mais si je dois faire cette feuille, je ne vois pas l'intérêt de faire en plus un copier /coller les valeurs ?
19/10/2019, 06h55
Theze

Bonjour,

Bonjour Marcel ;)

Je suppose que tes différents classeurs ne subissent pas de modifications durant l'utilisation de ton TDB, dans ce cas, tu récupères en une seule fois, sur une feuille cachée, toutes les valeurs nécessaires à l'ouverture du TDB et tu vas ensuite piocher dans ces valeurs car sinon, si à chaque changement dans tes menus déroulants il te faut récupérer toutes tes valeurs dans les différents classeurs il y aura probablement un temps de latence qui peut contrarier les utilisateurs !

En attente de plus d'explications pour te pondre un code

Bonjour,
Comme vous me l'avez conseillé :

J'ai ouvert un fichier
J'ai fais mes formules pour récupérer les données dans une super Base de données
J'ai fermé le fichier
J'ai fais les mêmes 3 étapes pour chacun de mes fichiers sources
Dans ThisWorkBook, j'ai fait une macro qui ouvre puis ferme chacun des fichiers sources
Dans la feuille de la Super Base de Données j'ai recopié la macro de ThisWorkBook pour pouvoir mettre à jour les données, si tout ne s'est pas bien passé

Quand j'ouvre mon fichier, pas de pb
Quand je veux le mettre à jour, j'ai le message "Erreur 9 - L'indice n'appartient pas à la sélection" et certains fichiers restent ouverts...

Voici le code de ThisWorkBook

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
Private Sub Workbook_Open()
Application.EnableEvents = False    'empêche l'évènement svt (cas de macro à l'ouverture)
Application.ScreenUpdating = False  'Empêche les actualisations d'écran
Application.DisplayAlerts = False   'Empêche les questions
'Message pour patienter pdt màJr
CreateObject("Wscript.shell").Popup "Merci de patienter, le temps de mettre à jour les données." _
            & vbCrLf & "40 secondes max", 2, "MàJr Fich PDG"
 
Workbooks.Open ("N:\Adresse\Tb1.xlsx")
    Workbooks("Tb1.xlsx").Close False
Workbooks.Open ("N:\Adresse\Tb2.xlsx")
    Workbooks("Tb2.xlsx").Close False
'... et ainsi de suite
 
''je double l'ouverture pour mettre à jour  sinon cela ne le met pas à jour
Workbooks.Open ("N:\Adresse\Tb2.xlsx")
    Workbooks("Tb2.xlsx").Close False
 
'Call MaJrData                       NE FONCTIONNE PAS ?
Sheets("Fich PDG").Activate
[A1].Select
Application.EnableEvents = True    'Autorise l'évènement svt (cas de macro à l'ouverture)
Application.ScreenUpdating = True  'Autorise les actualisations d'écran
Application.DisplayAlerts = True   'Autorise les questions
End Sub

Et le code mis dans feuille de la base de données

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Sub MaJrData()
'  OUVERTURE DES FICHIERS LIES
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open ("N:\Adresse\Tb1.xlsx")                                   '1
Workbooks.Open ("N:\Adresse\Tb2.xlsx")                       '2
Workbooks.Open ("N:\Adresse\Tb3.xlsx")                                                       '3
'... ainsi de suite
 
'FEMETURE DES FICHIERS LIES
Workbooks("Tb1.xlsx").Close False              '1
Workbooks("Tb2.xlsx").Close False       '2
Workbooks("Tb3.xlsx").Close False                          '3
'... ainsi de suite
 
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Est-ce que qq'1 peut me dire ce que je dois faire, svp ? Merci