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

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

--

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.

Barb Reinhardt wrote:

I'm confused. Why do you need RefName in the first place if it's based on
MCL_Name?

"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


--

Dave Peterson

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



On Wed, 17 Jun 2009 18:18:01 -0700, Barb Reinhardt
wrote:

I'm confused. Why do you need RefName in the first place if it's based on
MCL_Name?

"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

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

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 wrote:

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

--

Dave Peterson

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



On Thu, 18 Jun 2009 07:18:32 -0500, Dave Peterson
wrote:

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 wrote:

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

Workbooks.Open Filename: _
="\\Steve\Transfer Items\Recovered Schedules\MCL6.xls"

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 wrote:

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


On Thu, 18 Jun 2009 07:18:32 -0500, Dave Peterson
wrote:

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 wrote:

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

--

Dave Peterson

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


On Fri, 19 Jun 2009 07:27:59 -0500, Dave Peterson
wrote:

Workbooks.Open Filename: _
="\\Steve\Transfer Items\Recovered Schedules\MCL6.xls"

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 wrote:

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


On Thu, 18 Jun 2009 07:18:32 -0500, Dave Peterson
wrote:

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 wrote:

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

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 wrote:

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

On Fri, 19 Jun 2009 07:27:59 -0500, Dave Peterson
wrote:

Workbooks.Open Filename: _
="\\Steve\Transfer Items\Recovered Schedules\MCL6.xls"

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 wrote:

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


On Thu, 18 Jun 2009 07:18:32 -0500, Dave Peterson
wrote:

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 wrote:

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

--

Dave Peterson