hi,

Excel 2007 +, OpenXML format xlsx and xlsm

Sub Sheets_list_and_named_ranges_on_CLOSED_workbook()

'Need to activate the Microsoft ADO Ext xx reference for DLL and Security
'Need to activate the reference Microsoft ActiveX Data Objects xx Library

Dim Cn As ADODB.Connection
Dim oCat As ADOX.Catalog
Dim oFile As String, Resultat As String
Dim oSheet As ADOX.Table

oFile = "C:\MyFile.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
Resultat = Resultat & oSheet.Name & vbCrLf
Next

MsgBox Resultat

Set oSheet = Nothing
Set oCat = Nothing
Cn.Close
Set Cn = Nothing
End Sub

isabelle


Le 2014-04-22 07:35, a écrit :
Hello,

Is there a way to retrieve all the named ranges names in a CLOSED workbook then, picking one of them, copy its content to the clipboard?

Remark: without opening the wbk...


Thanks
Avi