[Résolu] ADO Lire des valeurs 'named range' d'un fichier excel fermé

[sadar]

Bonjour,

mon problème me semble simple à résoudre pourtant je n'arrive pas à trouver de solution

J'ai un classeur fermé qui contient des feuilles contenant des nom de cellules toto, titi, ttc (pour l'exemple hein !)

j'aimerai pouvoir faire une requête me permettant de récupérer les values/valeurs de ces cellules nommées et là cela coince !

J'ai lu attentivement le tuto en ligne de ce site (Lire et écrire dans les classeurs Excel fermés) qui nous indique comment récupérer un range via un

select * from [feuil&$A1:B2]

par exemple, mais je ne souhaite pas y accéder en adressage fixe car l'intérêt de la cellule nommée est justement de s'affranchir de sa localisation sous la forme Colonne:Ligne, et, dans mon cas je ne vais pas maitriser leurs adresses exactes !

Certes il y des solutions telles que ado.net - How do I access an Excel named table via ADO/ODBC? - Stack Overflow qui permettent d'aller lire l'adresse de la cellule nommée pour ensuite faire la bonne requête, mais je ne trouve pas cela très élégant ni efficace 🙄

bref, merci de votre aide car il me semble que ce que je cherche à faire est somme toute affreusement banal !

ci-après le code me permettant de lire parcourir le catalogue de noms, car c'est pour le moment la seule manière que j'ai trouvé pour accéder aux noms titi, toto, ttc (voir fichier en PJ) sans pour autant réussir à lire leur valeur

  Dim Cn As ADODB.Connection
  Dim oCat As ADOX.Catalog
  Dim oFile, Resultat, StrCnx As String
  Dim oSheet As ADOX.Table
  
  oFile = "C:\classeur1.xlsm"

  Set Cn = New ADODB.Connection
  Set oCat = New ADOX.Catalog
  
  '--- Connexion ---
  With Cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
    & oFile & ";Extended Properties=""Excel 12.0;HDR=YES;"""
    .Open
  End With
  
  Set oCat.ActiveConnection = Cn
  
  For Each oSheet In oCat.Tables
    Debug.Print oSheet.Name, oSheet.Type
  Next
  
  Set oSheet = Nothing
  Set oCat = Nothing
  Cn.Close
  Set Cn = Nothing

merci de vos lumières...

 

[job75]

Re : ADO Lire des valeurs 'named range' d'une fichier excel fermé

Bonjour sadar,

Pourquoi utiliser ADO ? De simples formules de liaison suffisent.

Ouvrez les 2 classeurs.

En supposant que la classeur source se nomme "Classeur1.xlsx", formules dans le classeur destination :

=Classeur1.xlsx!toto
=Classeur1.xlsx!titi

Fermez le classeur source, vous verrez apparaître le chemin d'accès.

A+

 

[sadar]

Re : ADO Lire des valeurs 'named range' d'une fichier excel fermé

Bonjour

Merci pour ta réponse, mais cette solution va créer un lien vers le fichier ce qui ne convient pas dans mon cas d'usage. J'ai en effet besoin d'importer la valeur relative au "named range" sans pour autant lier cette value au fichier d'origine

 

[mromain]

Re : ADO Lire des valeurs 'named range' d'une fichier excel fermé

Bonjour sadar, Job 🙂 le forum,

Veux-tu absolument passer par ADO, ou est-ce qu'une solution VBA du type Ouvrir le classeur en lecture seule / Importer la plage nommée / Fermer le classeur sans enregistrer pourrait convenir ?

De plus, quelque chose m'intrique dans ton code :

  ...
  
  With Cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
    & oFile & ";Extended Properties=""Excel 12.0;HDR=YES;"""
    .Open
  End With
  
  ...

Tu indiques dans la première ligne que le Provider est Microsoft.Jet.OLEDB.4.0 (qui apparemment devient obsolète), et dans la chaine de connexion, tu dis que c'est Microsoft.ACE.OLEDB.12.0.

?? C'est étrange ?

A+

 

[sadar]

Re : ADO Lire des valeurs 'named range' d'une fichier excel fermé

hello,

Workbook.open et autres bricoles du même genre sont, du moins pour la pratique que j'ai à ce jour, assez lourd dès lors où les classeurs contiennent pas mal de données sans compter qu'il est nécessaire de penser à faire des screenupdate = false et autre joyeusetées 🙄

concernant le Microsoft.Jet.OLEDB.4.0 c'est un copier coller d'exemple glané ci et là

entre temps j'ai (un peu) avancé puisque le code suivant fonctionne

Dim sql, CxString, nomFeuille As String
Dim Myconnection As Connection
Dim Myrecordset As Recordset
Dim MyWorkbook As String
 
oFile = "C:\Users\jeanrobd\Desktop\Classeur1.xlsm"
 
Set Myconnection = New Connection
Set Myrecordset = New Recordset
  
CxString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & oFile & ";Extended Properties=""Excel 12.0;HDR=No;IMEX=1;"""
   
'Open connection to the workbook
Myconnection.Open CxString
       
'Load the selected range into the recordset
'Myrecordset.Open "Select * from [ttc]", Myconnection, adOpenStatic
'Debug.Print Myrecordset.Fields.Count, Myrecordset.Fields.Item(0)

Myconnection.Close
Set Myrecordset = Nothing

toutefois, si je dois ramener (ce qui est mon cas) plus d'une dizaines de "named range" c'est pas terrible comme solution cas cela équivaut à un query par "named range" !

Je cherche donc maintenant à accéder au classer avec un query me permettant de lire l'ensemble des "named range" avec leurs values...

merci de votre aide précieuse

 

[mromain]

Re : ADO Lire des valeurs 'named range' d'une fichier excel fermé

Re bonjour,

Je cherche donc maintenant à accéder au classer avec un query me permettant de lire l'ensemble des "named range" avec leurs values...

Je pense que ce que tu cherches à faire est compliqué. Néanmoins, je vais suivre ce fil car on sais jamais 🙂

Juste par curiosité, est-ce que tu as la main sur le classeur fermé contenant les données à récupérer ?
Et est-ce que les noms que tu veux récupérer font référence à des cellules, des zones, ou les deux ?

A+

 

[sadar]

Re : ADO Lire des valeurs 'named range' d'une fichier excel fermé

Hello,

Les noms font références à des cellules et a des "range"/zones, je pense que j'ai une solution en relisant excellent tuto de skillroad sur Lire et écrire dans les classeurs Excel fermés tout à la fin du tuto il y a une boucle imbriquée que je vais tenter de customiser pour faire ce que je souhaite

bien je posterai le résultat !

 

[job75]

Re : ADO Lire des valeurs 'named range' d'une fichier excel fermé

Re,

Bon alors sans créer de liaison.

Placez cette macro dans le code de la feuille destination :

Private Sub Worksheet_BeforedoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim s, chemin$, x
s = Split(Target, ";")
If UBound(s) <> 1 Then Exit Sub
Cancel = True
chemin = ThisWorkbook.Path & "\" 'à adapter
Application.DisplayAlerts = False
On Error Resume Next
x = ExecuteExcel4Macro("'" & chemin & s(0) & "'!" & s(1))
MsgBox x 'pour tester
'[B2] = x 'pour entrer la valeur dans une cellule
End Sub

Puis entrez dans une cellule le texte Classeur1.xlsx;toto et faites un double-clic.

Classeur1.xlsx étant le nom du fichier source.

A+

 

[sadar]

Re : ADO Lire des valeurs 'named range' d'une fichier excel fermé

😕 post en double !

 

[sadar]

Re : ADO Lire des valeurs 'named range' d'une fichier excel fermé

code non optimisé pour lire l'ensemble des "named range" sans les values (next step hein ;-))

OFile = "C:\Classeur1.xlsm"

Set Cn = New ADODB.Connection
CxString = "Provider=MSDASQL.1;DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & OFile & ";ReadOnly=True;"

Cn.Open CxString
       
Set Rst = Cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))

For t = 1 To Rst.RecordCount
  Debug.Print Rst.Fields("TABLE_NAME").Value
  Rst.MoveNext
Next

Rst.Close
Set Rst = Nothing
Cn.Close
Set Cn = Nothing

 

[sadar]

Resolu : ADO Lire des valeurs 'named range' d'un fichier excel fermé

Voilà j'arrive après plusieurs hh de recherche et de tests à cela

Dim Cn As ADODB.Connection
Dim Rst As ADODB.Recordset, Rsc As ADODB.Recordset

Fichier = "C:\Classeur1.xlsm"

Set Cn = New ADODB.Connection

With Cn
  .Provider = "MSDASQL.1"
  .ConnectionString = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & Fichier & ";ReadOnly=True;"
  .Open
End With

Set Rsc = Cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, StrTable, Empty))

While Not Rsc.EOF
 If Right(Rsc!Table_Name, 1) <> "$" Then
    Debug.Print Rsc!Table_Name, Rsc!Column_Name
End If
  
  Rsc.MoveNext
Wend
  
Rsc.Close
Set Rsc = Nothing
Cn.Close
Set Cn = Nothing

l'astuce ici est de passer par OpenSchema(adSchemaColumns et de filtrer sur le "TABLE_NAME" pour ne pas prendre les onglet du classeur. Pour rappel, les TABLE_NAME qui finissent avec un $...sont des onglets !

et htop 😱😉😀

 

[mromain]

Re : ADO Lire des valeurs 'named range' d'une fichier excel fermé

Bonjour sadar, job,

Merci pour le partage 🙂
Je me met ton code de coté si besoin 😉

A+

 

[sadar]

Re : ADO Lire des valeurs 'named range' d'une fichier excel fermé

Hello

Pas de problème il est là pour cela. Je cherche maintenant à faire la même chose en ne sélectionnant QUE les TABLE_NAME dont j'ai besoin.

En effet, la feuille que je lis contient pas mal de nom de cellules. Donc essayons de faire concis !

Je reviendrai mettre la MAJ une fois le code terminé

 

[sadar]

[Résolu ] ADO Lire des valeurs 'named range' d'un fichier excel fermé

Voila le code finalisé pour ne lire que les champs/cellules nommées/named range souhaitées

OFile = "C:\tmp\myFile.xlsm"

Set Cn = New ADODB.Connection
Set Rst = New ADODB.Recordset

CxString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & OFile & ";Extended Properties=""Excel 12.0;HDR=No;IMEX=1;"""
   
Cn.Open CxString
Rst.Open "Select * from [toto], [titi]", Cn, adOpenStatic
Debug.Print Rst.Fields.Count, Rst.Fields.Item(0), Rst.Fields.Item(1)

Rst.Close
Set Rst = Nothing
Cn.Close
Set Cn = Nothing

A noter que [toto], [titi] sont les champs/cellules nommées/named range recherchés et que
toto = Rst.Fields.Item(0)
titi = Rst.Fields.Item(1)

Voilà !