excel 如何使用已定义的名称对对象进行解密

evrscar2 于 7个月前发布在其他

关注(0)|答案(1)|浏览(71)

免责声明：我仍然是一个很新的，所以希望你的耐心
我正在尝试编写一个使用Range（“Defined Name”） 而不是Range（“Cell Address”）的代码
但是，我总是遇到错误，例如，在这段代码中，我试图使用我在单元格中输入的工作簿/工作簿名称来设置源文档和目标文档
我所尝试的：

假设单元格的内容也是其定义的名称，并且此表位于目标工作簿中，其中还嵌入了验证码
| | B（工作表名称）| B (Name of Worksheets) |
| --|--|--|
| 1 |SourceWB| SourceWS|
| 2 |DestinationWB| DestinationWS|

Sub ImportData()
    Dim WB_S as Workbook 'source workbook
    Dim WS_S as Worksheet 'source worksheet
    Dim WB_D as Workbook 'destination workbook
    Dim WS_D as Worksheet 'destination worksheet

    Application.DisplayAlerts = True

'OpenWorksheets

    Set WB_D = Workbooks.Open(Range("DestinationWB")) 'referring to A2
    Set WS_D = WB_D.Sheets(Range("DestinationWS")) 'referring to B2 'ERROR
    Set WB_S = Workbooks.Open(Range("SourceWB")) 'referring to A1
    Set WS_S = WB_S.Sheets(WB_D.Range("DestinationWS")) 'referring to B2 'referring to B1

字符串
当我到达Set WS_D的行时，我会得到错误
同样，我的目标是引用文件路径和名称作为定义的名称
谢谢你的帮助

excel

来源：https://stackoverflow.com/questions/77431134/how-to-declare-objects-by-using-defined-names

1条答案

按热度按时间

jmp7cifd1#

尝试

Option Explicit

Sub ImportData()
    Dim WB_S As Workbook 'source workbook
    Dim WS_S As Worksheet 'source worksheet
    Dim WB_D As Workbook 'destination workbook
    Dim WS_D As Worksheet 'destination worksheet
    Dim ws As Worksheet, nm, msg As String

    Application.DisplayAlerts = True

    ' check named ranges
    Set ws = ThisWorkbook.ActiveSheet ' workbook where ranges are defined
    For Each nm In Array("SourceWB", "SourceWS", "DestinationWB", "DestinationWS")
        On Error Resume Next
        If ws.Range(nm) Is Nothing Then
             MsgBox "'" & nm & "' is not a named range", vbCritical
             Exit Sub
        Else
             msg = msg & vbLf & nm & " - " & ws.Range(nm)
        End If
        On Error Goto 0
    Next
    
    'OpenWorksheets
    Set WB_D = Workbooks.Open(ws.Range("DestinationWB")) 'referring to A2
    Set WS_D = WB_D.Sheets(ws.Range("DestinationWS").Value2) 'referring to B2
    
    Set WB_S = Workbooks.Open(ws.Range("SourceWB")) 'referring to A1
    Set WS_S = WB_S.Sheets(ws.Range("SourceWS").Value2) 'referring to B1
    
    MsgBox msg, vbInformation
End Sub

字符串

赞(0）回复(0）举报 7个月前

我来回答

excel 如何使用已定义的名称对对象进行解密

1条答案

相关问题

热门标签

最新问答