获取封闭工作簿中已定义名称的路径，文件名，工作表和地址 [英] Get path, filename, sheet and address of defined name in closed workbook

问题描述

此代码可以将定义好的名称添加到另一个工作簿中:

This code can add a defined name to another workbook perfectly fine:

Sub AddDefinedName()
    Dim sPath As String
    Dim sFilename As String
    Dim sSheetname As String
    Dim sRangeAddress As 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 this code allows to get all the information IF the said workbook is open:

Sub GetDefinedName()
    Dim sPath As String
    Dim sFilename As String
    Dim sSheetname As String
    Dim sRangeAddress As 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

关闭工作簿"source.xls"后，如何获得这些文件(以便可以通过VBA打开它).

How can I get these when the workbook "source.xls" is closed (so I can open it through VBA).

推荐答案

您可以使用 Name 对象获取地址字符串，就像这样
(假设您已将名称定义为 Workbook 范围)

You can use the Name object to get the address string, like this
(assuming you have defined the name as Workbook scope)

Dim nm as Name

Set nm = ThisWorkbook.Names("Source")
Debug.Print nm.RefersTo

这将以表格的形式为您提供完整的路径和地址

This will give you the full path and address in the form

'=C:\Me\Folder\[source.xls]Sheet1'!$A$1:$B$5

注意:如果路径，文件名或工作表名称中没有空格，则不带''

Note: without the ''s if there are no spaces in the path, filename or sheet name

以上内容将返回特定命名范围的信息.如果要获取所有远程引用的信息，请尝试使用

The above will return info for a specific named range. if you want to get info for all remote references, try using

ThisWorkbook.LinkSources

这将以字符串形式返回所有链接源的数组.对于指向其他工作表的链接，其格式为

This will return an array of all link sources as strings. For links to other worksheets, it will be in the form

C:\Me\Folder\source.xls

这篇关于获取封闭工作簿中已定义名称的路径，文件名，工作表和地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！