utiliser la valeur d'une cellule sans ouvrir son fichier .xls

[manuk]

Bonjour à tous et merci par avance à tous ceux qui pourront m'aider!

Je pense que la solution à mon problème doit exister sur ce forum ou sur le net.
Mais, autant j'ai des petits bouts de réponse à droite à gauche, autant je suis très pris par ... le temps


J'aimerais savoir comment on peut obtenir la valeur d'une cellule d'un fichier .xls sans l'ouvrir pour ajouter ensuite cette valeur dans un fichier de consolidation.

Je m'explique:

Je souhaite obtenir la valeur de la cellule G12 de plusieurs fichiers sans avoir à les ouvrir pour pouvoir ensuite les placer dans un fichier à part.

Dans mon exemple, ce sont des fiches inventaire de sites nommées 0001-0, 0001-1, etc
Dans chaque fiches inventaire, j'ai le nombre de Poste de travail qui se trouve en G12.

Je souhaiterais donc dans le fichier Retour_fiches.xls, obtenir en face de chaque nom de site colonne A, son nombre de Poste de travail colonne B.

Cela implique de rechercher des fichiers dans un répertoire INVENTAIRES qui a des sous répertoires.

En espérant avoir été assez explicite.

Merci par avance pour votre aide!

 

[job75]

Re : utiliser la valeur d'une cellule sans ouvrir son fichier .xls

Bonjour manuk, bienvenue sur XLD,

1) Télécharger sur le web (c'est gratuit) la macro complémentaire Morefunc.

C'est une bibliothèque de fonctions avec en particulier la fonction INDIRECT.EXT.

Celle-ci fonctionne comme INDIRECT mais elle permet de travailler sur un classeur fermé.

2) Télécharger le fichier zip ci-joint et extraire tout dans le même dossier (par exemple le bureau).

Les fichiers "fiches" se retrouvent alors dans des sous-dossiers du dossier INVENTAIRES.

3) Ouvrir le fichier Retour_fiches-v1.xls et voir la formule en B2 :

=SI(ESTERR(INDIRECT.EXT("'"&$E$1&"\INVENTAIRES\"&$A2&"\["&$A2&".xls]Feuil1'!G12"));INDIRECT.EXT("'"&$E$1&"\INVENTAIRES\"&GAUCHE($A2;TROUVE("-";$A2)-1)&"\["&$A2&".xls]Feuil1'!G12");INDIRECT.EXT("'"&$E$1&"\INVENTAIRES\"&$A2&"\["&$A2&".xls]Feuil1'!G12"))

Elle est compliquée car les noms des sous-dossiers ne sont pas homogènes, il faut les tester...

4) C'est pourquoi j'ai créé le fichier Retour_fiches-v2.xls, plus logique, avec la formule en C2 :

=INDIRECT.EXT("'"&$F$1&"\INVENTAIRES\"&$A2&"\["&$B2&".xls]Feuil1'!G12")

A+

 

[kiki29]

Re : utiliser la valeur d'une cellule sans ouvrir son fichier .xls

Salut,si cela peut t'aider voir #12 sur https://www.excel-downloads.com/threads/lire-un-fichier-excel-sans-louvrir.94092/ en l'adaptant à ton contexte

 

[manuk]

Re : utiliser la valeur d'une cellule sans ouvrir son fichier .xls

Bonjour job75 et tout d'abord merci pour ta réponse et ta solution rapide!

Je viens d'essayer et cela fonctionne bien pour mon exemple.

Le seul souci bloquant pour moi, c'est que dans mon répertoire INVENTAIRE, il y a des sous-répertoires et il peut y avoir d'autres sous-sous-répertoire encore...

L'arborescence de mon répertoire INVENTAIRE est vraiment super mal faite et certaines fiches peuvent se trouver dans un 5ème ou 6ème sous-répertoire de l'arborescence générale.

Par exemple:
D:\INVENTAIRES\SP3\segment\_Vert\5 - LOT\LOT3\etc...

D'où l'intérêt d'une fonction recherche d'abord puis d'un collage de valeur de cellule ensuite je pense.

Pour info, si il existe une macro ou fonction qui doit ouvrir le fichier pour obtenir ce que je souhaite, je suis preneur quand même.
Cela sera juste plus long.
J'ai un peu plus de 1000 fiches inventaires.

En tout cas merci pour ta solution!

 

[Hippolite]

Re : utiliser la valeur d'une cellule sans ouvrir son fichier .xls

Bonjour,
J'arrive un peu tard -> synthèse de ce qu'on peut faire avec des classeurs fermés :
Lire et écrire dans les classeurs Excel fermés

 

[job75]

Re : utiliser la valeur d'une cellule sans ouvrir son fichier .xls

Re,

Alors maintenant cette macro dans le fichier Retour_fiches-v3.xls zippé ci-joint :

Sub Recherche()
Dim cel As Range, txt$, F$
[B2:B65536].ClearContents
For Each cel In Range("A2", [A65536].End(xlUp))
  With Application.FileSearch 'marche jusqu'à Excel 2003
    .NewSearch
    .RefreshScopes
    .LookIn = ThisWorkbook.Path 'adapter éventuellement
    .Filename = cel & ".xls"
    .SearchSubFolders = True
    .Execute
    On Error Resume Next 'si le fichier n'existe pas
    txt = .FoundFiles(1)
    F = "='" & Application.Replace(txt, InStrRev(txt, "\") + 1, 0, "[") & "]Feuil1'!G12"
    cel.Offset(, 1).Formula = F
  End With
Next
End Sub

Noter que la propriété FileSearch n'existe plus à partir d'Excel 2007, c'est bien dommage...

Mais il y a d'autres méthodes en effet, à voir sur le forum.

A+

 

[kiki29]

Re : utiliser la valeur d'une cellule sans ouvrir son fichier .xls

Salut,avec le lien que j'ai donné plus haut et https://www.excel-downloads.com/threads/recherche-dans-plusieurs-fichiers.72998/ tu devrais avoir une réponse à ton probleme d'arborescence,naturellement à adapter à ton contexte

 

[job75]

Re : utiliser la valeur d'une cellule sans ouvrir son fichier .xls

Re,

Pour traiter correctement le cas où le fichier n'est pas trouvé, il faut écrire :

On Error Resume Next 'si le fichier n'existe pas
    Err = 0
    txt = .FoundFiles(1)
    F = "='" & Application.Replace(txt, InStrRev(txt, "\") + 1, 0, "[") & "]Feuil1'!G12"
    If Err Then F = ""
    cel.Offset(, 1).Formula = F

A+

 

[manuk]

Re : utiliser la valeur d'une cellule sans ouvrir son fichier .xls

Merci à toi d'abord job75 pour tes solutions proposées, je vais essayer cela.

J'avais oublié de préciser que la cellule G12 comportait soit un nombre soit une formule simple (Somme) qui affiche un nombre.
J'espère que cela ne va pas trop gêner.

Et merci à kiki29 & Hippolite pour vos interventions utiles, je vais regarder tout cela dès que possible.

Bonne soirée à tous.

Edit:
C'est bon job75 cela fonctionne bien apparemment.
Merci beaucoup pour ton aide pas seulement pour la solution fournie mais par ce qu'elle reste simple et compréhensible pour un novice comme moi.
Et surtout elle sera utilisable pour pleins d'autres choses tout en m'ayant retiré un grosse épine du pied pour un travail à rendre pour demain ^^

 

[job75]

Re : utiliser la valeur d'une cellule sans ouvrir son fichier .xls

Bonjour manuk, le fil,

Heureux que la solution proposée vous convienne.

Bien noter que la durée de la recherche dépend évidemment du répertoire de départ.

Par exemple avec .LookIn = ThisWorkbook.Path, et le fichier sur mon bureau, la durée est de 5,4 s.

Mais avec .LookIn = ThisWorkbook.Path & "\INVENTAIRES" la durée est de 0,5 s...

A+

 

[manuk]

Re : utiliser la valeur d'une cellule sans ouvrir son fichier .xls

Bonjour à tous.

Pour faire suite à ma demande initiale, j'aurai besoin de pouvoir compter le nombre de Poste De Travail total d'un site (site principal *-0 et ses sous sites *-1, *-2, etc).

Dans mon fichier exemple Retour_fiches-v4, je dois calculer le nombre total de PDT (Poste De Travail) dans la colonne C par rapport au nombre de PDT du site et de ses sous sites, si ils existent.

Par exemple, pour le site 0001-0 et son sous site 0001-1 j'obtiendrai 8.
Pour le site 0003 et ses sous sites j'obtiendrai 26, etc.

J'aimerai automatiser cela, en passant par la formule SOMME.SI ou autre mais je bute sur le comment.

La colonne D "Indice" peut aider au calcule, mais je préfèrerai m'en passer si possible.

En espérant avoir été assez clair.

Merci par avance pour vos lumières!

Manuk

 

[manuk]

Re : utiliser la valeur d'une cellule sans ouvrir son fichier .xls

Bonjour à tous.

J'ai trouvé une solution à mon problème et je la mets à dispo ici pour ceux qui seraient intéressés.

J'ai utilisé une partie de la "macro" donnée par job75 et la Formule SOMME.SI.

Je compte le nombre de PDT par site en ne me basant que sur les 4 premiers "digit" qui représente le site et ses sous sites (site global).
J'utilise ensuite la Formule SOMME.SI pour faire la somme totale des PDT par site global.

Dans mon fichier Retour_fiches-v5, j'ai inséré une colonne entre A et B et je remplis cette colonne vide B avec les 4 premiers digit de la cellule à sa gauche via la commande LEFT.
C'est ce qui va créer mon site global (site + sous sites).
Je passe par la macro suivante:

Sub Epure_nom_fiche()

[B2:B65536].ClearContents
Dim cel As Range, txt$, F$
For Each cel In Range("A2", [A65536].End(xlUp))
L = Left(cel, 4)
cel.Offset(, 1).Formula = L
Next

Je fais ensuite la somme des PDT par site global dans le colonne D via la Formule:

=SOMME.SI(B:B;B2;C:C)

que j'étire jusqu'à la fin.

En espérant que cela aide quelqu'un d'autre.

Bonne journée!