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:
To prevent some errors, if you want to delete diagrams, the sheet should be (re)named: To delete
'#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
⚠️ 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:
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.
A short video which shows how to create a macro from a script in ER/Studio Data Architect:
