Macro: Import Attributes

This macro for importing attributes into ER/Studio Data Architect was provided by  in a webinar last summer. Check it out!

ImportAttrib3.txt
'1. Entity Name
'2. Table Name
'3. Attribute Name
'4. Column Name
'5. Data type
'6. Table Definition
'7. Attribute Definition
'8. Nullability - 'NULL' or 'NOT NULL'


'Location of the spread-sheet
Const FILE_NAME As String = "C:\Documents and Settings\oktaya\Desktop\DataAttribUpload.xls"
Sub Main

	Dim theDiagram As Diagram
	Dim theModel As Model
	Dim theEntity As Entity
	Dim theAttribute As AttributeObj

	'define excel variables
	Dim Excel As Object
	Dim sheet As Object
	Dim range As Object
	Dim rowCount As Integer
	Dim I As Integer
	Dim entity_name As String
	Dim table_name As String
	Dim attribute_name As String
	Dim column_name As String
	Dim table_definition As String
	Dim attr_definition As String
	Dim datatype As String
	Dim nullability As String
	Dim datawidth As Integer
	Dim nPos As Integer
	Dim sDataType As String
	Dim s As String
	Dim nSepPos As Integer
	Dim datascale As Integer

	'initialize excel object and make visible
	Set Excel = CreateObject("Excel.Application")
	Excel.Visible = True
	Excel.workbooks.open FILE_NAME
	'get sheet info from excel object
	Set sheet = Excel.worksheets(1)
	Set range = sheet.usedrange
	rowCount = range.rows.Count

	Set theDiagram = DiagramManager.ActiveDiagram
	Set theModel = theDiagram.ActiveModel
	For I = 2 To rowCount
		entity_name = Trim(range.cells(I,1).Value)
		table_name = Trim(range.cells(I,2).Value)
		attribute_name = Trim(range.cells(I,3).Value)
		column_name = Trim(range.cells(I,4).Value)
		sDataType = Trim(range.cells(I,5).Value)
		table_definition = Trim(range.cells(I,6).Value)
		attr_definition =  Trim(range.cells(I,7).Value)
		nullability =  UCase(Trim(range.cells(I,8).Value))
		nPos = InStr(sDataType, "(")
		If nPos = 0 Then
			datatype = sDataType
			datawidth = 0
		Else
			datatype = Mid(sDataType, 1, nPos - 1)
			sDataType = Mid(sDataType, nPos +1, Len(sDataType))
			nPos = InStr(sDataType, ")")
			s = Mid(sDataType, 1, nPos - 1)
			nSepPos = InStr(s, ",")
			If nSepPos = 0 Then
				datawidth = Mid(s, 1, nPos - 1)
				datascale = 0
			Else
				datawidth = Mid(s, 1, nSepPos - 1)
				datascale = Mid(s, nSepPos + 1, Len(sDataType))
			End If
		End If
		Set theEntity = theModel.Entities(entity_name)
		If theEntity Is Nothing Then
			Set theEntity = theModel.Entities.Add(0, 0)
			theEntity.EntityName = entity_name
			theEntity.TableName = table_name
			theEntity.Definition = table_definition
		End If
		Set theAttribute = theEntity.Attributes(attribute_name)
		If theAttribute Is Nothing Then
			Set theAttribute = theEntity.Attributes.Add(attribute_name, False)
			theAttribute.ColumnName = column_name
			theAttribute.AttributeName = attribute_name
			theAttribute.Definition = attr_definition
			theAttribute.Datatype = datatype
			theAttribute.DataLength = datawidth
			theAttribute.DataScale = datascale

			If nullability = "NULL" Or nullability = "NOT NULL" Then
				theAttribute.NullOption = nullability
			End If
		End If
	Next I
	MsgBox "Done"
End Sub