Macro : recuperation de données de plusieurs classeurs

[Benjy]

Bonjour à tous,

J'aimerais dans un classeur et dans une cellule précise récupérer la somme d'une cellule de plusieurs autres classeurs ( nombre non définie à l'avance )

J'ai trouvé cette maccro, dont je remercie l'auteur au passage, mais j'ai du mal à la comprendre et à l'adapter.

'Macro de MichelXLD
Sub chercheFichiersFermesV03()
Dim X As Integer, nbFichiers As Integer, Y As Integer
Dim Tableau() As String
Dim Direction As String

Application.ScreenUpdating = False
Direction = Dir('C:\\Documents and Settings\\michel\\dossier\\general\\excel\\*.xls') 'adapter chemin repertoire

Do While Len(Direction) > 0
nbFichiers = nbFichiers + 1
ReDim Preserve Tableau(1 To nbFichiers)
Tableau(nbFichiers) = Direction
Direction = Dir()
Loop

If nbFichiers > 0 Then
For X = 1 To nbFichiers
If Tableau(X) <> ThisWorkbook.Name Then
Y = Y + 1
With ActiveSheet.Cells(Y, 1)
.Formula = '='C:\\Documents and Settings\\michel\\dossier\\general\\excel\\[' & Tableau(X) & ']Feuil1' & ''!' & 'A1'
.Value = .Value
End With
End If
Next X
End If

Application.ScreenUpdating = True
End Sub

On récupère les cellules A1 de la feuil1 de tous les claseurs contenus dans C:\documents and settings\michel\dossier\general ?
Mais ou sont stockées ces valeurs ?

 

[Benjy]

Re : Macro : recuperation de données de plusieurs classeurs

Un petit up. Comment adapter la macro pour me permettre de sommer toutes les cellules A1 des classeurs et d'afficher le résultat dans un autre classeur dans une seule cellule ?

 

[Guest]

Re : Macro : recuperation de données de plusieurs classeurs

Bonjour,

On récupère les cellules A1 de la feuil1 de tous les claseurs contenus dans C:\documents and settings\michel\dossier\general ?

Oui c'est ça.

Mais ou sont stockées ces valeurs ?

Cette macro crée dans la colonne A ligne y (variable Y) une liaison avec la Cellule A1 de chaque Feuil1 trouvée dans les classeurs listés.

Pour ne pas créer de lien, mais seulement récupérer la valeur tu peux remplacer les lignes:

With ActiveSheet.Cells(Y, 1)
.Formula = '='C:\\Documents and Settings\\michel\\dossier\\general\\excel\\[' & Tableau(X) & ']Feuil1' & ''!' & 'A1'
.Value = .Value
End With

Par

ActiveSheet.Cells(Y, 1).Value = ExecuteExcel4Macro("'" & chemin & "\[" & Tableaux(X) & "]Feuil1'!R1C1")

Où chemin est le chemin du répertoire des fichiers.
Et où j'espère n'avoir pas fait d'erreur dans les " et les '

A+

 

[Benjy]

Re : Macro : recuperation de données de plusieurs classeurs

A part un x en trop à la fin de tableau ta ligne semble correct.
Par contre à l'éxécution il me demande d'enregistrer les valeurs, en gros autant d'enregistrement que de fichiers présents.

J'éxecute la macro direcement de mon fichier résultat. Donc comment obtenir dans la case B2 de ce fichier la somme de toute les cellules A1 des fichiers contenu dans C:\... ?

 

[Guest]

Re : Macro : recuperation de données de plusieurs classeurs

Re,

Je ne comprends pas cette demande d'enregistrement.
En tous cas il t'importe bien les valeurs dans les cellules de la colonne A?
Si oui, une simple fonction Somme en B2 te donneras le total.
Si non pour n'avoir que le total:
En tête de macro (sous sub....())

Dim LeTotal as double

Et Remplacer la ligne

ActiveSheet.Cells(Y, 1).Value = ExecuteExcel4Macro("'" & chemin & "\[" & Tableaux(X) & "]Feuil1'!R1C1")

Par:

LeTotal = LeTotal + ExecuteExcel4Macro("'" & chemin & "\[" & Tableaux(X) & "]Feuil1'!R1C1")

En fin de macro

RAnge(B2")=LeTotal

A+

 

[Benjy]

Re : Macro : recuperation de données de plusieurs classeurs

Oui oui tout à fait les valeurs sont bien remplis dans la colonne A.
Enfin je suppose que car je réponds " annulé " à la demande de mise à jour des valeur et j'ai donc #Ref qui s'affiche dans la colonne.

En ce qui concerne cette mise à jour j'ai une fenêtre explorateur qui s'ouvre me demandant de mettre à jour les valeurs en selectionnant un fichier excel. Et cela autant de fois que j'ai de fichiers présent dans le répertoire chemin.
Avec ton nouveau code quand je fais annulé j'ai cette fois une erreur de compilation sur la ligne :

LeTotal = LeTotal + ExecuteExcel4Macro("'" & chemin & "\[" & Tableau(X) & "]Feuil1'!R1C1")

 

[Guest]

Re : Macro : recuperation de données de plusieurs classeurs

Re,

Je comprends d'où vient le problème, il vient des liaisons créées par la macro d'origine.

Re-prend à partir du post #3 et adapte la macro comme dit plus haut.

Peut-être faudra-t-il faire Editon/liasons/Rompre les liaisons.

A+

 

[Benjy]

Re : Macro : recuperation de données de plusieurs classeurs

Re,

Désolé je n'ai pas compris, j'ai bien suivi les instructions de chacun de tes posts a partir du #3. Je suis sous 2007 ou se situe l'option Rompre les liaisons ?

J'ai ceci comme macro :

Sub somme()
Dim LeTotal As Double
Dim X As Integer, nbFichiers As Integer, Y As Integer
Dim Tableau() As String
Dim Direction As String

Application.ScreenUpdating = False

Direction = Dir("Z:\protocole\" & Sheets("Sommaire").Cells(28, 2).Value & "\Fiches Espace\*.xlsm")

Do While Len(Direction) > 0
nbFichiers = nbFichiers + 1
ReDim Preserve Tableau(1 To nbFichiers)
Tableau(nbFichiers) = Direction
Direction = Dir()
Loop

If nbFichiers > 0 Then
For X = 1 To nbFichiers
If Tableau(X) <> ThisWorkbook.Name Then
Y = Y + 1
LeTotal = LeTotal + ExecuteExcel4Macro("'" & Chemin & "\[" & Tableau(X) & "]Feuil1'!R9C6")



End If
Next X
End If
Range("N22") = LeTotal

Application.ScreenUpdating = True
End Sub

J'ai adapter la cellule cible pour la somme et les cellules cibles pour mes fichiers.

Merci à toi.

 

[Guest]

Re : Macro : recuperation de données de plusieurs classeurs

Re,

La macro semble correcte. Tu ne dis pas si elle renvoie un résultat correcte.

Pour les liaisons l'aide Excel 2007 contient des éléments essentiels à ce sujets. F1 et tu entres les mots clef: 'rompre liaisons'

A+

 

[Benjy]

Re : Macro : recuperation de données de plusieurs classeurs

"1.Sous l'onglet Données, dans le groupe Connexions, cliquez sur Modifier les liens d'accès.
Remarque La commande Modifier les liens d'accès n'est pas disponible si votre fichier ne contient pas d'informations liées."

Je suis dans ce cas, la commande est grisée.
Eh bien la macro ne me renvoi aucun résultat. Quand je la lance il me demande ces mises à jour et je ne sais pas quel fichier selectionné. Et avec 100 ou plus fichiers je me vois mal chercher les 100 fichiers manuellement à chaque fois. Je clique donc sur annulé et j'obtiens une erreur " Incompatibilité de type" sur la ligne

LeTotal = LeTotal + ExecuteExcel4Macro("'" & Chemin & "\[" & Tableau(X) & "]Feuil1'!R9C6")

 

[Guest]

Re : Macro : recuperation de données de plusieurs classeurs

Re,

A mon avis tu devrais essayé la macro dans un nouveau fichier vierge . Enregistre le, lance la macro et vois ce qui se passe. Parce que là je ne vois pas pourquoi il demanderait des mises à jour?

Chez moi sous 2007, ExecuteExcel4Macro fonctionne ne demande aucune mise à jour de liens ou fichier ou autre


A+

 

[Benjy]

Re : Macro : recuperation de données de plusieurs classeurs

Déjà fait. Toujours le même problème.
Je joins un screen pour visualiser le problème.
J'ai affecté la macro au boutton , je clique et voilà. ( voir PJ)

Le nom en haut (A-10.xlsm) correspond à mon premier fichier cible.

 

[Guest]

Re : Macro : recuperation de données de plusieurs classeurs

Re Benji,

Dans

LeTotal = LeTotal + ExecuteExcel4Macro("'" & Chemin & "\[" & Tableau(X) & "]

N'aurais -tu pas oublié de remplacer 'Chemin' par le chemin vers ton répertoire???

Décompresse le zip (un fichier excel+un dossier 'Benji' avec 3 classeurs)
Ouvre le classeur et lance la macro 'Somme'

A+

 

[Benjy]

Re : Macro : recuperation de données de plusieurs classeurs

Effectivement je pensais l'avoir déclaré mais non ! Mais j'ai toujours un problème d'incompatibilité de type et toujours sur la même ligne.

j'ai rajouté :

dim chemin as string
chemin= "Z:\..."

Bon c'est pas grave c'est pas si important que sa pour mon fichier... Quand sa veut pas, sa veut pas.

Merci quand même pour ton aide et ta réactivité !
Bonne journée !

 

[Guest]

Re : Macro : recuperation de données de plusieurs classeurs

RE,

Pour l'erreur de type, effectivement si tu as une cellule (R9C6) dans un fichier qui contient autre chose qu'un double(valeur nulle ou vide ou texte) alors il y a erreur. C'est à toi d'adapter et faire les tests nécessaires.

A+