Macros – Remove diagrams from the repository using an Excel workbook

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

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:

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: