如何使用存储在Excel的名称管理器中的命名路径字符串并使用它打开文件。存储的字符串包含整个文件字符串

Question

当用户找到从QuickBooks导出的文件时，第一个例程抓住了文件名字符串 第二个例程将该名称写入名称经理为字符串，名称为字符串 “持有的路径招聘” 第三个例程尝试使用存储在名称管理器中的名称打开文件 我已经在名称管理器中验证了名称和正确的路径字符串，但是我尚未成功使用该名称来检索读取文件。 当前代码返回一个1004错误，但Debug.print确实给出了正确的路径，在Windows Run Box中粘贴字符串打开了电子表格，并且使用路径的静态语句也是如此。 事先感谢您的帮助

lang-VB
'HouseKeepingCODE Module

Sub GetPath()
'This sub gets the path to a File defined by the user within the routine
'It then calls another sub that applies that path to a name in the worksheet.
' Before calling this routine, The name should first be searched for, and then verified, then opportunity given to change the name.
Dim MyPath As String        'String to hold the path to an excel spreadsheet exported from quickbooks
Dim NameToChange As String  'String that holds the name manager name to store the path under
Dim NameComment As String   'Comment to identify the name in the name manager

NameToChange = "PathToEmployeeWithholding"
NameComment = "This Name contains the Path to the 'Employee Withholding' worksheet exported from quickbooks using VBA"


With Application.FileDialog(msoFileDialogFilePicker)
If .Show <> 0 Then
MyPath = .SelectedItems(1)
End If
End With

'Debug.Print PathToWithholding
'This routine should be modified
'It should simply get a path to a file,
'And return that path to the calling subroutine
'The calling subroutine should then add comment and call the changevalueofname subroutine


Call ChangeValueOfName(NameToChange, MyPath, NameComment)  'this routine stores the retrieved text string in the name manager

End Sub

Sub ChangeValueOfName(NameToChange As String, NewNameValue As String, Comment As String)
'
' ChangeValueOfNameMagagerName Macro
' Changes the Value of a defined name in the Name Manager
'This should be used to change the name.
'
'Once the file is selected data needs to be imported to an array, and the
'Employee name values need to be checked against the worksheets in the workbook and against the recap sheet
'If changes are needed, it needs to write them into the workbook, including changing recap sheet and adding
'worksheets for any new employees
'
'
'

'
    With ActiveWorkbook.Names(NameToChange)
        .Name = NameToChange
        .RefersTo = NewNameValue
        .Comment = Comment
    End With
End Sub

'****problem subroutine below***

Sub UpdateEmployeewithholding()
'This sub will clean employee withholding as it is exported from quickbooks and then read the file into this workbook
'The path is already stored in the names manager
'This routine needs to integrate changevalueofname and getpath.  They should update before executing the balance of this routine
Dim MyWorkBook As Workbook
Dim MyPath As Variant   'Contains path to employee withholding spreadsheet as exported from quickbooks.  This sheet is to be modified for reading, but not saved
Dim MyRange As Range    'Contains a defined range after setting it so
Dim whichrow As Variant 'Marks the starting point for routines that find and delete blanks as well as those that define range values and scan them into an array
Dim Direction As Variant    'Defines whether we are progressing over "Rows" or "Columns"
Dim ArrayWidth As Range  'Holds the top row addresses of the array
Dim ArrayHeight As Range 'Holds the left column addresses of the array
Dim MyArray As Variant   'Holds the array to transfer to this spreadsheet
Dim Width As Long           'Holds the array width to prevent loosing it when the original spreadsheet closes
Dim Height As Long          'Holds the array height to prevent loosing it when the original spreadsheet closes

whichrow = 1        'We are starting in cell A! or R1C1
Direction = "Rows"


'******************************************************************************************************
'***INSERT Code that will read the string value stored in the name manager Name "PathToEmployeeWithholding" into the variable "MyPath"
' and eliminate the hard coded path from the routine
'  STILL MISSING
'*****************************************************************************************************

'Setting MyPath to the fixed path to employee withholding until we can get the routine to 'open the workbook from a varialbe
'stored  in the name manager
MyPath = ThisWorkbook.Names("PathToEmployeeWithholding")
'ActiveWorkbook.Names (PathToEmployeeWithholding)
Debug.Print MyPath 'This works

Set MyWorkBook = Workbooks.Open(MyPath) '***Problem line returns 1004 stored-path could 
'not be found
Debug.Print ActiveWorkbook.Name

'**** The immediate statement below worked ***
debug.Print thisworkbook.Names("PathToEmployeeWithholding")="D:\redacted\Employee Withholding .xlsx"


'***Code below to extract data from workbook opened above

Answer 1

问题在于，从名称管理器中撤回名称总是添加=标志，并为定界人士添加打开和关闭引号。 。例如
=“ string \ text”
解决方案是使用MID函数剥离定界器

    Sub UpdateEmployeewithholding()
'This sub will clean employee withholding as it is exported from quickbooks and then read the file into this workbook
'The path is already stored in the names manager
'This routine needs to integrate changevalueofname and getpath.  They should update before executing the balance of this routine
Dim MyWorkBook As Workbook
Dim MyPath As String       'Contains path to employee withholding spreadsheet as exported from quickbooks.  This sheet is to be modified for reading, but not saved
Dim MyRange As Range    'Contains a defined range after setting it so
Dim whichrow As Variant 'Marks the starting point for routines that find and delete blanks as well as those that define range values and scan them into an array
Dim Direction As Variant    'Defines whether we are progressing over "Rows" or "Columns"
Dim ArrayWidth As Range  'Holds the top row addresses of the array
Dim ArrayHeight As Range 'Holds the left column addresses of the array
Dim MyArray As Variant   'Holds the array to transfer to this spreadsheet
Dim Width As Long           'Holds the array width to prevent loosing it when the original spreadsheet closes
Dim Height As Long          'Holds the array height to prevent loosing it when the original spreadsheet closes
Dim StrLength As Long

whichrow = 1        'We are starting in cell A! or R1C1
Direction = "Rows"


MyPath = ThisWorkbook.Names("PathToEmployeeWithholding") 'get string from name manager

Debug.Print MyPath          'As stored in name manager the name always returns and = sign plus open and close quotes
StrLength = Len(MyPath)     'In order to get the workbook to open programatically these must be stripped


MyPath = Mid(MyPath, 3, StrLength - 3) 'The mid statement strips these delimiters off, leaving just the text


Debug.Print MyPath


Set MyWorkBook = Workbooks.Open(MyPath)
Debug.Print ActiveWorkbook.Name

'Stuff below gets data from opened workbook