After a while, you may have added lots of projects in your ER/Studio Repository.
In this blog post, I’ll share a script which can:
- generate a worksheet with all the Diagrams added to the Repository
- remove diagrams from the Repository using a list provided through a spreadsheet.
To prevent some errors, if you want to delete diagrams, the sheet should be (re)named: To delete
Rename the sheet
Script
'#Language "WWB-COM" 'TITLE: wRepoDeleteDiagramsListFromExcel.BAS 'DESCRIPTION: This macro deletes Diagrams from the Repository using an Excel workbook. ' ' The Sheet should be named "TO DELETE" ' Const TITLE = "wRepoDeleteDiagramsListFromExcel" Dim iSuccess As Integer Dim iRows As Integer ' Dim MS Excel variables. Dim Excel As Object Dim curRow As Integer Dim curCol As Integer Dim sheet As Object Dim range As Object Public Const CLR_GREY = RGB(192, 192, 192) Option Explicit Sub Main On Error GoTo Error_handle Dim Excel As Object Dim bPrompt As Boolean Dim bDoIt As Boolean Debug.Clear iSuccess = 0 iRows = 0 Begin Dialog UserDialog 600,182,TITLE,.DialogFunc ' %GRID:10,7,1,1 PushButton 20,7,180,35,"Get all diagram names",.getDiagrams Text 20,63,210,14,"Path to diagrams list:",.tPath TextBox 20,84,500,21,.Path PushButton 530,84,60,21,"Browse",.Browse CheckBox 20,119,320,21,"Prompt before delete",.prompt_chbx OKButton 350,147,110,28 CancelButton 480,147,110,28 End Dialog Dim dlg As UserDialog 'initialize dialog defaults, 0 = unchecked, 1 = checked dlg.prompt_chbx = 1 'start_dialog: 'start dialog If Dialog(dlg) = -1 Then 'initialize excel object and make visible Set Excel = CreateObject("Excel.Application") Excel.Visible = True Excel.workbooks.Open dlg.Path bPrompt = dlg.prompt_chbx = 1 On Error GoTo Error_sheet 'get sheet info from excel object Set sheet = Excel.worksheets("TO DELETE") On Error GoTo Error_handle Set range = sheet.usedrange 'range variables for loop Dim db_count As Integer Dim start_range As Integer 'get count for loop db_count = range.rows.Count start_range = 2 'ignore header row of sheet curCol = 1 'start at the first column of the spread sheet Dim i As Integer Dim sDiagram$ iRows = db_count - start_range + 1 For i = start_range To db_count curCol = 1 curRow = i 'get Diagram from spread sheet. sDiagram = GetStringCell(range, curRow, curCol, 0, 0) If sDiagram <> "" Then If bPrompt Then bDoIt = False Select Case MsgBox("Would you like to delete this diagram: " & vbCrLf & vbCrLf & sDiagram & "?", vbQuestion+vbYesNoCancel, TITLE) Case vbYes bDoIt = True Case vbCancel Exit For End Select End If If (bPrompt And bDoIt) Or (Not bPrompt) Then Debug.Print "D: " & sDiagram If (DiagramManager.RepoDeleteDiagram(sDiagram) = 1) Then iSuccess = iSuccess + 1 End If End If End If After: Next i Excel.workbooks.Close Excel.visible = False MsgBox "Diagrams deleted." & vbCrLf & vbCrLf & "Success: " & iSuccess & vbCrLf & "Ignored/Failure: " & (iRows - iSuccess), vbOkOnly, TITLE Exit Sub Error_handle: Debug.Print "An error occured: " & vbCrLf & DiagramManager.GetLastErrorString & vbCrLf & vbCrLf Err.Clear ' Resume Next GoTo After Error_sheet: MsgBox "Worksheet 'TO DELETE' not found in the workbook.", vbExclamation, TITLE Excel.workbooks.Close Excel.visible = False End If End Sub Sub GetAllDiagrams() Dim sos As StringObjects Dim so As StringObject Set Excel = CreateObject("Excel.Application") Excel.Workbooks.Add curRow = 1 curCol = 1 PrintCell "Diagrams", curRow, curCol, 1, 0 Excel.worksheets(1).Name = "Diagrams" 'This should be manually updated by the user who wants "to delete" diagrams in the repo Excel.Range("A1:A1").interior.Color = CLR_GREY Excel.Columns("A:A").columnwidth = 100 Excel.Columns("A:A").WrapText = True Excel.Rows("1:1").Font.Bold = True Set sos = DiagramManager.RepoDiagrams() Excel.Visible = True For Each so In sos Debug.Print so.StringValue PrintCell so.StringValue, curRow, curCol, 1, 0 Next so MsgBox "Diagrams list completed", vbInformation, TITLE End Sub Function GetStringCell (range As Object, row As Integer, col As Integer, rowInc As Integer, colInc As Integer) As String GetStringCell = Trim(range.cells(row,col).Value) curRow = curRow + rowInc curCol = curCol + colInc End Function ' Print a cell Sub PrintCell(value As String, row As Integer, col As Integer, rowInc As Integer, colInc As Integer) 'sample ' PrintCell ent.EntityName, curRow, curCol, 0, 1, clrFore, clrBack, 10, False Excel.Cells(row, col).Value = value curRow = curRow + rowInc curCol = curCol + colInc End Sub Rem See DialogFunc help topic for more information. Private Function DialogFunc(DlgItem$, Action%, SuppValue&) As Boolean Select Case Action% Case 2 ' Value changing or button pressed If DlgItem = "Browse" Then 'browse to excel file if used pushes browse button. Put path in text box. DlgText "path", GetFilePath(,"All Excel Files (*.xlsx;*.xls;*.xlsm)|*.xlsx;*.xls;*.xlsm|Excel Workbook (*.xlsx)|*.xlsx|Excel Macro-enabled Workbook (*.xslm)|*.xslm|Excel 97-2003 Workbook (*.xls)|*.xls|All Files (*.*)|*.*",,"Open SpreadSheet", 0) DialogFunc = True ElseIf DlgItem = "OK" And DlgText("path") = "" Then 'don't exit dialog if a path is not specified MsgBox("Please enter a valid path.",,"Error!") DialogFunc = True ElseIf DlgItem = "getDiagrams" Then GetAllDiagrams DialogFunc = True End If End Select End Function
Usage
⚠️ This macro deletes diagrams from the repository! This is not undoable!
Using ER/Studio Data Architect, connect to your Repository.
Then, you can run the macro to generate a workbook with all the diagrams existing in your Repository.
You can remove from this list, all the Diagrams you want to keep (remove the rows, do not filter the data).
Once your list only contains the Diagrams you want to delete from your Repository, you can rename the worksheet:
To delete
Save the Excel spreadsheet.
From the macro UI, browse to select your workbook:
wRepoDeleteDiagramsListFromExcel
Finally, click the OK button to delete the diagrams.
If this macro doesn’t exactly do what you are expecting, feel free to update it.
Bonus
A short video which shows how to create a macro from a script in ER/Studio Data Architect: