Not an external link

[Giordano Bruno]

I have an excel file named "New Weekly Report Summary 2020 02 10.xlsm" with a number of filenames that look something like
'=OFFSET('New Weekly Report Summary 2020 02 10.xlsm'!StartGraphDate,0,0,1,'New Weekly Report Summary 2020 02 10.xlsm'!WksCt),OFFSET(MC,0,1,1,'New Weekly Report Summary 2020 02 10.xlsm'!WksCt)
I'm not sure how they started, but I need to remove the unnecessary reference to the file name. Since the reference is to this workbook, there are no external links.
If anyone has any clues how they may be corrected and/or how they may have crept in in the first place, I would be most grateful.

 

[Alex Blakenburg]

Try this
Take a backup of your workbook first.

Ctrl+H (replace)
In Find What paste in this
'New Weekly Report Summary 2020 02 10.xlsm'!
Don't put anything in replace with box

In the Within box select workbook

Hit the Replace All button.

 

[Giordano Bruno]

Hi Alex. Many thanks for your interest and your suggestion. Unfortunately the file name appears only in the definition of the name, not the name itself, so searching for it in the formulas will not find anything. I have no idea why Excel would ever do this. I didn't write the file name in myself when creating the definition.

 

[Alex Blakenburg]

Do you mean the name range manager ?
Can you show us where you are seeing it.

 

[Giordano Bruno]

Hi Alex. Yes, the name quite separate. One of the names is "PWdata" and it refers to:
'=OFFSET('New Weekly Report Summary 2020 02 10.xlsm'!StartGraphDate,0,0,1,'New Weekly Report Summary 2020 02 10.xlsm'!WksCt),OFFSET(MC,0,1,1,'New Weekly Report Summary 2020 02 10.xlsm'!WksCt)
If I go through the editing process in the Name Manager and remove those unnecessary references, It still works just as before, but there are lots of them and they are unnecessary and they do bug me.

 

[Alex Blakenburg]

Do they all contain contain exactly the same file name ie 'New Weekly Report Summary 2020 02 10.xlsm'! ?
Also are they all in use or are most of them just carried over from a previous template,

 

[Alex Blakenburg]

If I understand it correctly and the spreadsheet is referencing an xlsm file in the name manager and that needs to be removed.
Then try this on a copy of your spreadsheet.

Sub NameRangeLoop()

    Dim rngName As Name
    Dim findStr As String
    Dim findPos As Long
    Dim replaceFrom As Long
    Dim replaceStr As String
    
    findStr = ".xlsm'"
    
    For Each rngName In ActiveWorkbook.Names
        findPos = InStr(1, rngName.RefersTo, findStr, vbTextCompare)
        If findPos <> 0 Then
            replaceFrom = InStr(rngName.RefersTo, "'")
            replaceStr = Mid(rngName.RefersTo, replaceFrom, findPos + 5 - replaceFrom + 2)
            
            rngName.RefersTo = Replace(rngName.RefersTo, replaceStr, "")
            
        End If
    Next rngName

End Sub

 

[Giordano Bruno]

Thanks so much Alex. Yes, they are all the same since they all refer to this workbook. I'll try your code tomorrow, but it looks good to me.
I'll let you know how it goes.
Thanks again GB

 

[Giordano Bruno]

Hi Alex,
Tested and works great. Just a note for others; as I understand it, the reference to the single quote works because the name has spaces and would require some other identifier if that were not the case. I'm also surprised that it removes the file references even when they are present multiple times in a singe name.
I'm still mystified by the process which introduces these unneeded references to itself.
Thanks again Alex for your patience and time helping me with this.

 

[Alex Blakenburg]

You are correct. It was just something I knocked up quickly as an initial proof of concept.
There are quite a few other variable eg
- as you said if there are no spaces in the file name it doesn't use the single quotes.
- If it is a Sheet Scoped named range as opposed to a workbook scoped.
- In your case it was name pointing to a name, pointing at an actually range of cells changing the format as well.

Thank you for providing feedback and glad I was able to help.

PS: I can't shed any light on how your workbook finished up with those external references.