Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 293
Default Named Range Hic-Up

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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Named Range Hic-Up

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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 293
Default Named Range Hic-Up

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


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Named Range Hic-Up

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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 293
Default Named Range Hic-Up

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




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Named Range Hic-Up

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Referencing a named range based upon Range name entry in cell Barb Reinhardt Excel Worksheet Functions 14 June 20th 07 07:19 PM
Array as a "named range" - formula ok in cells, but error as "named range" tskogstrom Excel Discussion (Misc queries) 11 December 28th 06 04:44 PM
inserting a named range into new cells based on a named cell Peter S. Excel Discussion (Misc queries) 1 June 4th 06 03:53 AM
Compare a selected Range with a Named range and select cells that do not exist PCLIVE Excel Programming 1 October 18th 05 07:09 PM
If any cell in named range = 8 then shade named range JJ[_8_] Excel Programming 3 August 26th 05 11:09 PM


All times are GMT +1. The time now is 12:14 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"