Action Buttons - VBA to Change Server Name

[chewza]

Hi there

I am trying to use VBA to change the Server name for multiple actions buttons (in multiple workbooks) in Perspectives

There is an article from Wim Gielis which details exactly how to do this:
https://www.wimgielis.com/tm1_actionbuttons_EN.htm

Unfortunately, I cannot figure out how to add the reference to the library as per his instruction:
"First off, you will need to add a reference to a library: Applix TM1 Cube Viewing Controls for Excel. (see Tools > References in the VBEditor)."

Does anyone know which reference file I need to browse for in the VBA code editor (Tools/Add References).
Hoping Wim gets to see this message.

Many thanks!
Chris

[Wim Gielis]

Hi Chris,

It's been a while since I used code like that, probably the 10 years that have elapsed since my article. PAfE buttons are a different beast too.

That being said, the article says what to do without these references (but you lose intellisense and so on).

The references were visible (at least 10 years ago) when I installed Perspectives. Not sure if this is still the case.

[JohnO]

I lead a server rename several years ago and 2 of us co-wrote something to rename all references to the server and all buttons (And there was something specific to do tithTM1User() worksheet function). It all worked fine. Here is the specific subroutine which did the name change. I don't think we needed to have any specific references but I think we may have had the Perspectives add-in loaded though I'm not sure that was necessary. But you can see we referred to shapes rather than having to need explicit reference to the shapes heritage.

I know I re-used / re-purposed this code earlier this year to identify which workbooks had perspectives buttons as part of our transition to PAfE.

----------------------
Sub update_action_button_ServerName(wrkbk As Workbook)

Dim sht As Excel.Worksheet
Dim vShape As Shape

Dim TM1_Server_To_Find As String
Dim TM1_Server_Replacement As String
Dim ti_button As Object

Action_buttons_upated = 0

For Each sht In wrkbk.Worksheets

For Each vShape In sht.Shapes
If Left(vShape.Name, 8) = "TIButton" Then
Set ti_button = sht.OLEObjects(vShape.Name).Object
If ti_button.ServerName = UserForm1.Existing_TM1_Server Then
ti_button.ServerName = UserForm1.New_TM1_Server
Action_buttons_upated = Action_buttons_upated + 1
End If
End If
Next
Next

End Sub

[chewza]

Hi JohnO

I have used the relevant portions of your code, but I keep getting an error message against the "Set tib = sht.OLEObjects(Shape.Name).Object" line of code. Error message = "Run-time error '424' " ; "Object Required". I do have perspectives open. Please refer to my code further below

Any ideas?

Many thanks

Regards
Chris




Dim FSO As Object
Dim sourceFolder As Object
Dim file As Object
Dim wb As Workbook
Dim ws As Worksheet
Dim shp As Shape
Dim tib As Object


Set FSO = CreateObject("Scripting.FileSystemObject")



' Set the source folder and target folder paths
Set ExcelFolder = FSO.GetFolder("D:\ChangeActionButton\")


' Loop through all files in the source folder
For Each file In ExcelFolder.Files

' Open the workbook and check for macro buttons
Set wb = Workbooks.Open(file.Path)
For Each ws In wb.Worksheets
For Each shp In ws.Shapes
If Left(shp.Name, 8) = "TIButton" Then

Set tib = sht.OLEObjects(shp.Name).Object
tib.ServerName = "uat_Model"
'Exit For
End If

Next shp

Next ws


wb.Close SaveChanges:=False

Next file

[chewza]

Hi Wim & John

Thanks very much for your help. @Wim - I referred back to you article, and my code is working.
Please refer to code below.

Thanks both for your help!

Regards
Chris


Sub TM1_ChangeServerName()

Dim obj As OLEObject
Dim tib As TM1XlCubeView_1_4.TIButton
Dim FSO As Object
Dim sourceFolder As Object
Dim file As Object
Dim wb As Workbook
Dim ws As Worksheet
Dim TargetFolder As String
Dim NewServerName As String

Set FSO = CreateObject("Scripting.FileSystemObject")

TargetFolder = "D:\Model_PAth\}Externals"
NewServerName = "uat_Model"


Set ExcelFolder = FSO.GetFolder(TargetFolder)

' Loop through all files in the source folder
For Each file In ExcelFolder.Files

' Open the workbook and check for macro buttons
Set wb = Workbooks.Open(file.Path)
For Each ws In wb.Worksheets

For Each obj In ws.OLEObjects
If obj.progID = "TM1XL.TIButtonCtrl.1" Then
Set tib = obj.Object
'MsgBox tib.Caption
With obj.Object
.ServerName = NewServerName
End With
End If
Next

Next ws

wb.Save
wb.Close SaveChanges:=False

Next file

End Sub

[ardi]

Hi there

I am trying to use VBA to change the Server name for multiple actions buttons (in multiple workbooks) in Perspectives

There is an article from Wim Gielis which details exactly how to do this:
https://www.wimgielis.com/tm1_actionbuttons_EN.htm

Unfortunately, I cannot figure out how to add the reference to the library as per his instruction:
"First off, you will need to add a reference to a library: Applix TM1 Cube Viewing Controls for Excel. (see Tools > References in the VBEditor)."

Does anyone know which reference file I need to browse for in the VBA code editor (Tools/Add References).
Hoping Wim gets to see this message.

Many thanks!
Chris

I never hardcode the TM1 Server Name or TI Process Name in the Action Button properties. I use Excel Named Ranges for everything.

[chewza]

me neither
someone elses code