How to take a named path string stored in Excel's name manager and open a file using it. The stored string contains the entire file string

Question

The first routine grabs the filename string as the user locates a file which was exported from Quickbooks The second routine writes that name into the name manager as a string under the name "PathToEmployeeWithholding" The third routine attempts to open the file using the name stored in the name manager I have verified in the name manager that the name and correct path string is there, but I have not been successful in using that name to retrieve the file for reading. The current code returns a 1004 error, but debug.print does give the correct path, pasting the string in the windows run box opens the spreadsheet, and so does a static statement with the path. Thanks in advance for your help

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

The problem was in the fact that pulling the name back from name manager always added an = sign plus open and close quotes for delimiters. .eg ="string\text" The solution was to strip the delimiters using the mid function

    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