How do I handle special characters in external range references?-CodePudding

My application checks each sheet of an external workbook to see if a range with a certain name is defined on that sheet:

externalRangeName = "[" & workbookName & "]" & sheetName & "!" & rangeName
'
'  E.g., externalRangeName = "[source8.xlsm]PK CD=0!rawData"
'
Dim externalRange As Range
Dim rangeExists As Boolean
On Error Resume Next
Set externalRange = Range(externalRangeName)
rangeExists = Err.Number = 0

rangeExists is False even though the range exists. The problem seems to be the presence of special characters in the sheet name. If I change the sheetname from "PK CD=0" to something simple like "foo" the application finds the range.

I had already discovered that the file name portion of an external range reference could not contain certain characters, and so I simplified my filenames. But now it looks like the sheet name must be simplified as well.

Is this a known VBA bug? If so, is there a workaround? For example, is there a way to escape the special characters? If not, what am I doing wrong?

CodePudding user response：

You should enclose the workbook name and the sheet name in your references in single quotes. This is how Excel itself does to consider spaces in file names and sheet names.

externalRangeName = "'[" & workbookName & "]" & sheetName & "'!" & rangeName

'  example: ='[Name of the workbook.xlsm]PK CD=0'!rawData

CodePudding user response：

Yeah I simplify my sheet names by removing special characters.

Dim wb As Workbook
Dim ws As Worksheet       

 For Each ws In wb.Worksheets
    
    'Rename sheet names that have ^ = - in them'

        If InStr(ws.Name, "^") > 0 Then
            ws.Name = Replace(ws.Name, "^", "")
        
        ElseIf InStr(ws.Name, "-") > 0 Then
            ws.Name = Replace(ws.Name, "-", "")
            
        ElseIf InStr(ws.Name, "=") > 0 Then
            ws.Name = Replace(ws.Name, "=", "")
        
        End If
    
 Next