How to update Name Manager for large number of Excel files

[tm1novice]

Hi All,

TM1 9.4 onward releases have TM1RebuildOption variable available in name manager which can be set to default via tm1p.ini file.

But this will work only for new slices/exports done after setting the parameter in tm1p.ini

What if we have large number of excels where to as well we need to add this parameter in name manager ?
Is there a way to add / update name manager for large number excel worksbooks without manual work ? like using a VB script or something ?

Thanks

[TomaszB]

What if we have large number of excels where to as well we need to add this parameter in name manager ?
Is there a way to add / update name manager for large number excel worksbooks without manual work ? like using a VB script or something ?
Thanks

Hi,
Yes you can change it using VB like almost everything in Excel.
Try the below code. It should do it for all workbooks in the given path. Make sure that all of the workbooks do not contain the TM1REBUILDOPTION global property.
Otherwise you should change the code and add some IF logic to avoid errors if the property would be already in one of the workbooks.

Code: Select all

Sub Change_TM1REBUILDOPTION()

    strPath = "\\server\path"
    
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    objExcel.DisplayAlerts = False
    Set objFso = CreateObject("Scripting.FileSystemObject")
    Set objFolder = objFso.GetFolder(strPath)
    
    For Each objFile In objFolder.Files
    
        If objFso.GetExtensionName(objFile.Path) = "xls" Then
            Set objWorkbook = objExcel.Workbooks.Open(objFile.Path)
        
        ' CHANGE THE CODE HERE
            objWorkbook.Names.Add Name:="TM1REBUILDOPTION", RefersToR1C1:="=0"
        'END
        
            objWorkbook.Save
            objWorkbook.Close SaveChanges:=True
        End If
    
    Next
    
    objExcel.Quit

End Sub

Regards
Tom

[Wim Gielis]

Make sure that all of the workbooks do not contain the TM1REBUILDOPTION global property.
Otherwise you should change the code and add some IF logic to avoid errors if the property would be already in one of the workbooks.

Hello Tom

Thank you for contributing your solution.
Excel will not error out if the given name already exists. It will merely update the value to 0.

Another approach, point of view "within Excel" (not to be executed outside of Excel):

Code: Select all

Sub LoopThroughFiles()
    
    FolderName = "\\server\path"

    Application.ScreenUpdating = False
    
    On Error GoTo ExitProc
    
    'add a \ if the folder name did not end with a \
    If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator
    Fname = Dir(FolderName & "*.xls")

    'loop through the files
    Do While Len(Fname)

        With Workbooks.Open(FolderName & Fname)
            .Names.Add Name:="TM1REBUILDOPTION", RefersToR1C1:=0
            .Close True
        End With

        'go to the next file in the folder
        Fname = Dir

    Loop
    
ExitProc:
    
End Sub

[tm1novice]

Hello Tom and Wim,

Thanks for the response. That was really helpful

Thanks.