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



On Wed, 17 Jun 2009 20:17:48 -0500, Dave Peterson
wrote:

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



Minitman wrote:

A reference in the InsertNameDefine menu called RefName keeps
changing when I save and reopen a set of workbooks

To fix it, I have to go into the InsertNameDefine 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