lire cellule nommée fichier fermé (ADO ou ExcecuteExeclForMacro)

[jojo_peter67]

Bonjour à tous,

Je cherche une solution pour lire des données de cellules nommées de fichiers fermés.
142 fichiers et 60 cellules nommées par fichier.
Les 142 fichiers sont des données confidentielles à caractère financier.

j'ai tenté la fonction Pull mais lorsqu'Excel recalcul, le fichier devient très - trop lent.
j'ai tenté le couple LINKEDRANGE et LINKREFERENCE de Shawn Stackhouse mais bizarrement, ne fonctionne qu'avec les fichier sources ouvert

Je ne sais pas utiliser ExcecuteExeclForMacro dans le worksheet avec les formules. La fonction GetValue ne s'utilisant qu'avec le VBA :

Public Function GetValue(path, file, namedRange)
'   Retrieves a value from a closed workbook
    Dim arg As String
   
'   Make sure the file exists
    If Right(path, 1) <> "\" Then path = path & "\"
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
    
'   Create the argument
    arg = "'" & path & file & "'!" & namedRange
      
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)
End Function

J'ai essayé avec ADO via le site de Jacques Boisgontier mais je bloque sur un élément: passer dans la fonction le nom de la cellule nommée (et pas le numéro de la cellule).

Je suis ouvert à toute idée qui me fera avancer.

merci beaucoup

Function LitUneCellule(repertoire As String, fichier As String, feuille As String, cellule As String)
  'Microsoft ActiveX DataObject 2.8 doit être coché
  Application.Volatile
  Set cnn = New ADODB.Connection
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & repertoire & "\" & fichier & _
         ";Extended Properties=""Excel 8.0;HDR=No;"";"
  Set rs = cnn.Execute("SELECT * FROM [" & feuille & "$" & cellule & ":" & cellule & "]")
  LitUneCellule = rs(0)
  rs.Close
  cnn.Close
  Set rs = Nothing
  Set cnn = Nothing
End Function

 

[jojo_peter67]

Re : lire cellule nommée fichier fermé (ADO ou ExcecuteExeclForMacro)

ci joint 2 fichiers pour illustrer mon propos.
1 base et une fichier source.

Merci à tous

Jo

 

[jojo_peter67]

Re : lire cellule nommée fichier fermé (ADO ou ExcecuteExeclForMacro)

un petit UP pour remonter ma question

 

[jojo_peter67]

Re : lire cellule nommée fichier fermé (ADO ou ExcecuteExeclForMacro)

je me répond a moi même ...
a partir de la fonction de Jacques Boisgontier, je transforme via formule le nom de la cellule nommée en Range
avec qq chose comme : =SUBSTITUE(DROITE(CELLULE("adresse";INDIRECT($DZ88&BU$14));NBCAR(CELLULE("adresse";INDIRECT($DZ88&BU$14)))-CHERCHE("!";CELLULE("adresse";INDIRECT($DZ88&BU$14));1));"$";"").

La fonction fonctionne 😉

Elle est seulement très longue compte tenu du nombre de fois où elle est utilisée .....

quelqu'un aurait il une idée pour accélérer la fonction :

Function LitUneCellule(repertoire As String, fichier As String, feuille As String, cellule As String)
'Microsoft ActiveX DataObject 2.8 doit être coché
Application.Volatile
Set cnn = New ADODB.Connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & repertoire & "\" & fichier & _
";Extended Properties=""Excel 8.0;HDR=No;"";"
Set rs = cnn.Execute("SELECT * FROM [" & feuille & "$" & cellule & ":" & cellule & "]")
LitUneCellule = rs(0)
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Function

 

[Roland_M]

Re : lire cellule nommée fichier fermé (ADO ou ExcecuteExeclForMacro)

Bonjour,

Avec 142 fichiers et 60 cellules nommées par fichier, pas question de répéter cela par fonction !
Il faut une routine Excel4 ou ADO celle-ci étant plus rapide, et ceci appelé simplement par un bouton
(j'ai plusieurs exemples de routines de ce genre à adapter et qui fonctionnent parfaitement !)
puis de boucler sur les fichiers et les cellules !

pour commencer oublier les cellules nommées !(du moins c'est mon avis)

ensuite pour ce faire il faut une source d'informations avec les données sources qui pourrait être une feuille Nommée "Data"
et voir comment structurer cette base de données selon les besoins !?
AVEC:
Le ou les dossiers voir sous dossiers sources !?
(à moins que ce soit un seul dossier de base ce qui serait beaucoup plus simple !)

les noms des fichiers sources !?
(à moins que ce soit tous les fichiers dans ce même dossiers, là aussi plus simple !)

Les cellules sources !?


Puis, les cellules de destination !? (si dans une seule et même colonne serait aussi plus simple !)


voilà voilà ...

 

[jojo_peter67]

Re : lire cellule nommée fichier fermé (ADO ou ExcecuteExeclForMacro)

Bonjour et merci pour cette réponse.

Je répond un peu tard mais je jongle avec plusieurs projets qui prennent un caractère urgent en fonction des humeurs des uns et des autres ..........

Je suis donc parti sur une mauvaise piste qui ne permet pas a Excel de gérer tous ces appels à une fonction comme LitUneCellule avec autant de fichiers. (c'est lourd, très lourd et risque +++ de plantage de la becanne)

La réalisation d'une base de données à partir d'

une routine Excel4 ou ADO

à l'air une solution fort intéressante. (mais je n'y connais rien)

Les cellules nommées (ce ne sont pas des plages mais toujours une seule cellule) sont utilisées par ce que les fichiers de départs changent d'une année sur l'autre, sauf leur nommage (je m'explique, "nom1" en 2012 est sur la cellule B14 puis D16 en 2014)

Je met donc en PJ la structuration des sources : adresse, dossiers, sous dossiers, fichiers et cellules nommées ainsi que la destination.
C'est du lourd comme structuration compte tenu du nombre de fichiers ... pour tester, on peut imaginer mettre les dans un répertoire par année pour simplifier les routines ...

Grand merci

Jo

 

[jojo_peter67]

Re : lire cellule nommée fichier fermé (ADO ou ExcecuteExeclForMacro)

ça marche pas comme je le souhaite, j'abandonne cette voie.
Merci Roland pour ton aiguillage.