excel - Get path, filename, sheet and address of defined name in closed workbook -

excel - Get path, filename, sheet and address of defined name in closed workbook -

this code can add together defined name workbook fine:

sub adddefinedname() dim spath string dim sfilename string dim ssheetname string dim srangeaddress string spath = "c:\me\folder" sfilename = "source.xls" ssheetname = "sheet1" srangeaddress = "$a$1:$b$5" thisworkbook.names.add "source", _ refersto:="='" & spath & "\[" & sfilename & "]" & ssheetname & "'!" & srangeaddress end sub

and code allows info if said workbook open:

sub getdefinedname() dim spath string dim sfilename string dim ssheetname string dim srangeaddress string spath = range("source").parent.parent.path sfilename = range("source").parent.parent.name ssheetname = range("source").parent.name srangeaddress = range("source").address msgbox spath msgbox sfilename msgbox ssheetname msgbox srangeaddress end sub

how can these when workbook "source.xls" closed (so can open through vba).

you can utilize name object address string, this (assuming have defined name workbook scope)

dim nm name set nm = thisworkbook.names("source") debug.print nm.refersto

this give total path , address in form

'=c:\me\folder\[source.xls]sheet1'!$a$1:$b$5

note: without ''s if there no spaces in path, filename or sheet name

the above homecoming info specific named range. if want info all remote references, seek using

thisworkbook.linksources

this homecoming array of link sources strings. links other worksheets, in form

c:\me\folder\source.xls

excel vba excel-vba