Named Range Hic-Up

[Minitman]

A reference in the Insert>Name>Define menu called RefName keeps
changing when I save and reopen a set of workbooks

To fix it, I have to go into the Insert>Name>Define menu and find the
named range called RefName and remove the added directory references.

The code for RefName should just read:

=MCL6.xls!MCL_Name

which works. Instead it is showing:

='\\Steve\Transfer Items\Recovered Schedules\MCL6.xls'!MCL_Name

which does not work, even though MCL6.xls is open!!!

Every time this workbook is opened, I have to go in and manually
remove this segment of the formula:

\\Steve\Transfer Items\Recovered Schedules\

to get the code to work.

Is there anyway to do this in the Workbook_Open event with vba? If
not, is there anyway to do this?

Any help would be greatly appreciated.

-Minitman

 

[Dave Peterson]

I used xl2003 to test and I couldn't get it to include the path if that file was
open. Is there any chance that you've mapped the \\steve\... share to a letter,
then opened the mcl6.xls workbook via the drive letter?

Maybe that's confusing excel. (I'm not on a network, so I couldn't test that.)

But names are pretty nice in excel. You could loop through all the names and
inspect each and determine if they should be fixed--or you could just reassign
that name once more. (You don't even have to delete it first!)

This has a few safety checks in it:

Option Explicit
Sub Auto_Open()
Dim MCLWkbkName As String
Dim MCLFilePath As String
Dim MCLWkbk As Workbook

MCLWkbkName = "MCL6.xls"
'include the trailing backslash!
MCLFilePath = "\\Steve\Transfer Items\Recovered Schedules\"

'my testing location/filename.
'MCLWkbkName = "book99.xls"
'MCLFilePath = "C:\my documents\excel\"

Set MCLWkbk = Nothing
On Error Resume Next
Set MCLWkbk = Workbooks(MCLWkbkName)
On Error GoTo 0

If MCLWkbk Is Nothing Then
'try to open it
On Error Resume Next
Set MCLWkbk = Workbooks.Open(MCLFilePath & MCLWkbkName)
On Error GoTo 0
If MCLWkbk Is Nothing Then
MsgBox "Not currently open and failed to open!"
'what should happen
Else
ThisWorkbook.Activate 'give it focus
ThisWorkbook.Names.Add Name:="RefName", _
RefersToR1C1:="='" & MCLWkbkName & "'!MCL_Name"
End If
End If
End Sub

 

[Dave Peterson]

The refname is a name in a workbook that points at a name/range in a separate
workbook.

Well, that's my bet.

 

[Minitman]

Hey Barb,

Thanks for the reply.

Good question.

The validation list in the monthly workbook cannot access a range in
another workbook - it's not allowed, so the work around.

Now it seems to be working ???

Not sure what happened

 

[Minitman]

Hey Dave,

Thanks for the reply.

I just reopened the workbooks and now they seem to be working fine???

I think I may have had 2 or 3 of the monthly workbooks open at the
same time when the problem arose. It didn't occur to me that this
might be the problem

I already have this code in the monthly Workbook_Open sub:

On Error Resume Next
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
Set wb = Nothing
Set wb = Workbooks("MCL6.xls")

If wb Is Nothing Then 'workbook not open, so open it
ChDir "\\Steve\Transfer Items\Recovered Schedules"
Workbooks.Open Filename: _
="\\Steve\Transfer Items\Recovered Schedules\MCL6.xls"
Set wb = Nothing
Else
Set wb = Nothing
End If

With Application
.DisplayAlerts = True
.ScreenUpdating = True
.Calculate
End With

On Error GoTo 0


-Minitman

 

[Dave Peterson]

Since you're specifying the complete path and file name in the .open statement,
you don't have to use ChDir--unless you want to make that the current
directory????

 

[Minitman]

Hey Dave,

It has to do the fact that these files are sitting on a network drive
that is accessed from multiple computers on my network.

I found the code I was looking for. Here it is:

ActiveWorkbook.Names.Add Name:="RefName", RefersToR1C1:= _
"=MCL6.xls!MCL_Name"

I place it right after MCL6.xlt is loaded in the Workbook_Open event.
It seems to be working.

-Minitman

 

[Dave Peterson]

Workbooks.Open Filename: _
Since you supplied the complete path, you don't need to change directories first
for the .open statement.

========

I'm confused about your use of the .xlt (a template file) and the .xls
extension.

 

[Minitman]

OPPS!!!

..xlt was a typo.

========

This works most of the time. I think I found where the problem
originates. I am working on 48 of these monthly schedules at about 2
to 5 at a time. It appears that the named range in a new monthly
workbook opens just fine but then changes when I open a new monthly
workbook. The named range in the new workbook is ok but, the previous
workbooks name range now has the full path again!!!

It seems that was the problem all along...

Any thoughts on how to fix this?

-Minitman

 

[Dave Peterson]

The only time I've seen the full path in a link was when the sending file wasn't
open.

I wonder if you're opening a different file with the same name, but in a
different folder.

The only suggestion I could offer was the code in the previous post.

 

[Minitman]

Hey Dave,

I was afraid of that. I need to go into each monthly workbook and
duplicate the working code from the new monthly into the old workbooks
I'm working on. Time consuming but, it appears, necessary.

Thanks for the help, I really appreciate it.

-Minitman