Solved: Sub to Open workbooks, and update named ranges

[xluser2007]

Hi All,

We conduct valuations every December and June.

The Dec07 valuation had a bunch of spreadsheets that had named ranges ending in "_Dec07".

We are in the Jun08 valuation. I have w orkbook that contains a list of the se relevant workbooks. I wish to write a macro that will:

Open up the list of workbooks.
For each workbook opened, search through each named range ending in "_Dec07" and replace it with "_Jun08".
Make sure the new named range for Jun08 has been substitutred over its Dec07 counterpart i.e. if a range was "data_Dec07". Step 2 should create and equivalent "data_Jun08". We need to substitute all "data_Dec07" range references with "data_Jun08".
Once all substitutions with "_Jun08" named ranges have been made, we can delete all the named ranges ending in "_Dec07".
Move onto the next workbook. and repeat steps 1-4.Now I have started with this code.

And for Step 1, I have:

Option Explicit

'-------------------------------------
' DEFINE any Private/ MODULE CONTANTS
'-------------------------------------

' "mclng_startrow" the starting ROW reference for the workbooks that the
' macro loops through on each worksheet

Private Const mclng_startrow As Long = 5

' "mclng_wbksCOLUMN" is the starting COLUMN reference for the workbooks list that
' the macro loops through and OPENS

Private Const mclng_wbksCOLUMN As String = "C"

Sub OPEN_workbooks(UpdateLinksSheetReference As String)

'------------------------------------------------------------------------

' STEP 1:
' Dimension all objects as we are using 'Option Explicit'

Dim UpdateSht As Worksheet
Dim linkwkbk As Workbook
Dim row As Long

'------------------------------------------------------------------------

' STEP 2:
' Toggle OFF screenupdating and automatic calculation
' This is for efficiency

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

'------------------------------------------------------------------------

' STEP 3: ERROR Handling

Set UpdateSht = ThisWorkbook.Sheets(UpdateLinksSheetReference)

If UpdateSht.Range("D1") <> 0 Then

' Display error message to user, and ask them to re-check link workbook
' strings and then Exit Sub

Call _
MsgBox("One of your workbooks links is incorrect - please correct it and re run?", _
vbCritical Or vbSystemModal, "ERROR")

Exit Sub

Else

'------------------------------------------------------------------------

' STEP 4: Renaming the relevant links

' mclng_startrow defined as a private CONSTANT above

row = mclng_startrow

Do While (UpdateSht.Range(mclng_wbksCOLUMN & row).Value <> "") Or _
(UpdateSht.Range(mclng_wbksCOLUMN & row).Value <> vbNullString)

Set linkwkbk = Workbooks.Open(UpdateSht.Range(mclng_wbksCOLUMN & row).Value, UpdateLinks:=0)


' INSERT Steps 2-4



row = row + 1

Loop

'------------------------------------------------------------------------

' STEP 5:

'Clear memory of stored objects by setting them to Nothing

Set linkwkbk = Nothing

'------------------------------------------------------------------------

End If

End Sub

Could anyone help me with Steps 2-4 as above please?

[xluser2007]

For Step 3, the substitution,

I have been tinkering aorund, and found some code from Aaron Blood from Ozgrid that does rapid Global Sheet selection and operations across a workbook.

I have a sketch of how I'd like it to be used for my purpose, but don;t quite know how to apply it to the Dec07 opened workbooks.

Here is the adapted code:

' Adapted from Aaron Blood's Rapid create values only of the entire workbook code
' http://www.ozgrid.com/forum/showthread.php?t=38064

Option Explicit

Sub Create_Values_only_Activeworkbook()

Dim sh As Worksheet, HidShts As New Collection
For Each sh In ActiveWorkbook.Worksheets
If Not sh.Visible Then
HidShts.Add sh
sh.Visible = xlSheetVisible
End If
Next sh

Worksheets.Select
Cells.Select

' Change the "What" reference and the "Replacement" in the next line in line with the macro
Selection.Replace What:="namedrangeendingin_DEC07", Replacement:="namedrangeendingin_JUN08", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

For Each sh In HidShts
' sh.Delete
sh.Visible = xlSheetHidden
Next sh

End Sub
This is where I was up to as of now.

[Bob Phillips]

Here is a simple function to change the names



Function ReplaceNames(ByRef wb As Workbook, _
ByVal OldName As String, _
ByVal NewName As String)
Dim nme As Name

With wb

For Each nme In .Names

If nme.Name Like "*" & OldName & "*" Then

.Names.Add Name:=Replace(nme.Name, OldName, NewName), RefersTo:=nme.RefersTo
nme.Delete
End If
Next nme
End With
End Function


Wouldn't it be better to change it to a name that doesn't reflect the period. The workbook name would reflect the period, so if you used say DAT_Curr_Period instead of Data_Dec07, you would never have this bother. I am not a fan of rippling information down every level.

Design is key!

[xluser2007]

Here is a simple function to change the names



Function ReplaceNames(ByRef wb As Workbook, _
ByVal OldName As String, _
ByVal NewName As String)
Dim nme As Name

With wb

For Each nme In .Names

If nme.Name Like "*" & OldName & "*" Then

.Names.Add Name:=Replace(nme.Name, OldName, NewName), RefersTo:=nme.RefersTo
nme.Delete
End If
Next nme
End With
End Function

Wouldn't it be better to change it to a name that doesn't reflect the period. The workbook name would reflect the period, so if you used say DAT_Curr_Period instead of Data_Dec07, you would never have this bother. I am not a fan of rippling information down every level.

Design is key!
Bob,

thanks for the Function.

Your last point of Design being the key is the one that struck me the most.

It's funny, I actually thought of designing it this way this valuation i.e. change named ranges from Dec07 to Jun08 so that the updater (whoever it is next time) actually bothers to check the spreadsheets they are updating going forward i.e. open them up and check them be it for named ranges or otherwise!

I actually went and redefined the all the names as dynamic ranges this time round, so upon reflecting your advice the DAT_Curr_Period form of named ranges will do just fine i.e the named ranges will adjust for all new data anyway, why change the actual names, good suggestion!

Sometimes all it takes is for someone to poke at a fundamental hole in the structure to show that it isn't necessary. In this case just jumping to VBA when not required.

Thanks Bob :).

(Though I may have a go later, just for learning programming at the above problem and ask you questions if necessary - but I'll mark this thread solved ).

[Bob Phillips]

I know what you mean about trying to force them to think about what they are doing, but you can only lead a horse to water. What I have found is that you implement all sorts of safety systems that they are menat to check, and they just go through them blind, saying yes where they should and so on ... they go through the motions, but don't necessarily check.

In my experience, the person is either conscientious and will follow laid outr procedures properly with proper intent, or they are sloppy and they go through the motions. Therefore, adding loads of checks in the code is just lost time.

This is my biggest beef with current management philosophies, they think everything can be reduced to process, whereas I think it is all down to getting the right staff, valuing them, and training.

[xluser2007]

I know what you mean about trying to force them to think about what they are doing, but you can only lead a horse to water. What I have found is that you implement all sorts of safety systems that they are menat to check, and they just go through them blind, saying yes where they should and so on ... they go through the motions, but don't necessarily check.
That's right. ever since being a member of VBAX and MrExcel, I'm constantly mlearning of new and funky tools. Its very easy to jump the gun and shoot for these techniques to make sure processes are automated and error-proof going forward, and place least strain on updating for the users, and allow them more time on the actual analysis.

There is a fine line as you have pointed out though between over engineering and not really ensuring that the user understands the process.


In my experience, the person is either conscientious and will follow laid outr procedures properly with proper intent, or they are sloppy and they go through the motions. Therefore, adding loads of checks in the code is just lost time.
Yeah, I guess I;m trying to be conscientious of these tasks going forward and trying hard to encourage "best practices" with Excel.

I have some really intelligent colleagues, but find that with updating they "stick to a winning system" of manual updating of files and often produce quick fixes to problems when it comes to Excel.

So its hard not to try the above techniques, but I agree over checking can only go so far.


This is my biggest beef with current management philosophies, they think everything can be reduced to process, whereas I think it is all down to getting the right staff, valuing them, and training.
Very interesting coming from a full time programmewr and developer. this bit is very true. I would modify slightly and say that for the specific task of analysis be it building a forevcasting model for example, the data checking should be a rigorous automated process, but the analysis performed and understanding of the data checking requires thorough training and hiring of people who care about the work that they produce.