data model in excel

**suhasmahajan85** · 02-02-2011, 05:11 AM

Hi, I need help in excel vba macro.

I am creating excel based tool for data modelling. which will connect to database e.g oracle, sql server and then fetch the table information.

e.g table name- emp then its all column . till this I have achieved it. now m getting all tables with its column name , primary key...

Now my question is with the help of above data I want to preint table in excel and want to show relationship between them PK FK by drawing a line between them.

e.g
EMPLOYEE DEPT
eno (pk) dno (pk)
ename eno (fk)
eaddrs dname

now it should show in well tabular form in excel and should draw line between employee.eno to departnment.eno.

can we do it with help of macro.

regards,
Suhas

**TMS** · 02-02-2011, 06:22 AM

I'm not sure that I can help with your specific problem, but the code below will draw a line and an elbow connector between two cells. I'm not sure how to calculate the end position for the elbow connector.

So, what you would need to do is locate the start and end cells and determine the start and end of your arrows.

Option Explicit

Sub Test()

Dim CellLeft As Single
Dim CellTop  As Single
Dim CellWidth  As Single
Dim CellHeight  As Single

Dim AxB As Single
Dim AyB As Single
Dim AxE As Single
Dim AyE As Single

With Range("D8")
    CellLeft = .Left
    CellTop = .Top
    CellWidth = .Width
    CellHeight = .Height
    AxB = CellLeft + CellWidth
    AyB = CellTop + (CellHeight / 2)
End With

With Range("G11")
    CellLeft = .Left
    CellTop = .Top
    CellWidth = .Width
    CellHeight = .Height
    AxE = CellLeft
    AyE = CellTop + (CellHeight / 2)
End With

Debug.Print AxB, AyB, AxE, AyE

' Draw a Straight Line with Arrow head
' expression.AddLine(BeginX, Beginy, EndX, EndY)
' ActiveSheet.Shapes.AddLine(291.75, 115.5, 392.25, 160.5).Select
With ActiveSheet.Shapes.AddLine(AxB, AyB, AxE, AyE).Line
    .EndArrowheadStyle = msoArrowheadTriangle
    .EndArrowheadLength = msoArrowheadLengthMedium
    .EndArrowheadWidth = msoArrowheadWidthMedium
End With

' Draw an Elbow Connector with Arrow head
' expression.AddConnector(Type, BeginX, BeginY, EndX, EndY)
' ActiveSheet.Shapes.AddConnector(msoConnectorElbow, 293.25, 114#, 93.75, 45#).Select
' Not quite sure how to calculate the end positions ... but you can experiment ;-)
With ActiveSheet.Shapes.AddConnector( _
    msoConnectorElbow, AxB, AyB, AxE / 4.5, AyE / 5 _
        ).Line
    .EndArrowheadStyle = msoArrowheadTriangle
End With

End Sub

Regards