Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Referencing a named range based upon Range name entry in cell | Excel Worksheet Functions | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Compare a selected Range with a Named range and select cells that do not exist | Excel Programming | |||
If any cell in named range = 8 then shade named range | Excel Programming |