How to Use AutoIT to connect shapes in Excel

[DeltaVMan]

Summary: I want to use AutoIT to generate a flow chart in Excel by connecting shapes.

Issue: I cannot get AutoIT to manipulate connector lines to connect shapes.

Details: Excel 2016, AutoIT Version 3.5.4 

Background:

If I create flowchart shapes in an Excel Spreadsheet: Step Shape "Step1", Decision Shape "Trans1", and Connector Shape "Step1_To_Trans1" the following Excel VBA Macro will connect "Step1" to "Trans1".

Sub Macro16()
    ActiveSheet.Shapes.Range(Array("Step-1_To_Trans-1")).Select
    Selection.ShapeRange.ConnectorFormat.BeginConnect ActiveSheet.Shapes("Step1"), 3
    Selection.ShapeRange.ConnectorFormat.EndConnect ActiveSheet.Shapes("Trans1"), 1
    Selection.ShapeRange.RerouteConnections
End Sub

I cannot seem to make the "ShapeRange.ConnectorFormat.BeginConnect" Method work in AutoIT.

AutoIT Function from attached code shown below:

Func AddConnector_Function($Connector_Type, $Begin_X, $End_X, $Begin_Y, $End_Y, $Begin_Name, $End_Name)
   $oExcel.Sheets ("Flow_Diagram" ).Select
   Local $Connector_Name = $Begin_Name & "_To_" & $End_Name

   With $oExcel
      $BeginObject = .ActiveSheet.Shapes.Range($Begin_Name)
      $EndObject = .ActiveSheet.Shapes.Range($End_Name)
      .ActiveSheet.Shapes.AddConnector($Connector_Type, $Begin_X, $End_X, $Begin_Y, $End_Y).Select ;Connector type, Begin X location, End X location, Begin Y location, End Y location
      $ConnectorObject = .Selection.ShapeRange
      $ConnectorObject.Name = $Connector_Name
      $BeginObject.Select
      $ConnectorObject.ConnectorFormat.BeginConnect = $BeginObject.Name & ", " & 3
      $EndObject.Select
      $ConnectorObject.ConnectorFormat.EndConnect = $EndObject.Name & ", " &  1
      $ConnectorObject.Select
      $ConnectorObject.RerouteConnections

   EndWith
EndFunc

Flow Diagram.xlsx

Connect Shapes Example.au3

[water]

BeginConnect is a method so you need to pass the parameters like this:

$ConnectorObject.ConnectorFormat.BeginConnect($BeginObject.Name, 3)

 

Edited July 4, 2016 by water

[DeltaVMan]

I had tried the code shown below with no luck.

I receive error message: 

: ==> The requested action with this object has failed.:
$ConnectorObject.ConnectorFormat.BeginConnect($End_Name, 3)
$ConnectorObject.ConnectorFormat^ ERROR
>Exit code: 1    Time: 22.88
 

I even verified with a message box that the $Begin_Name is being read correctly "Step1".

Func AddConnector_Function($Connector_Type, $Begin_X, $End_X, $Begin_Y, $End_Y, $Begin_Name, $End_Name)
   $oExcel.Sheets ("Flow_Diagram" ).Select
   Local $Connector_Name = $Begin_Name & "_To_" & $End_Name

   With $oExcel
      $BeginObject = .ActiveSheet.Shapes.Range($Begin_Name)
      $EndObject = .ActiveSheet.Shapes.Range($End_Name)
      .ActiveSheet.Shapes.AddConnector($Connector_Type, $Begin_X, $End_X, $Begin_Y, $End_Y).Select ;Connector type, Begin X location, End X location, Begin Y location, End Y location
      $ConnectorObject = .Selection.ShapeRange
      $ConnectorObject.Name = $Connector_Name
      Msgbox(0,"debug", "$Begin_Name Object Name is " & $Begin_Name)
      $ConnectorObject.ConnectorFormat.BeginConnect($End_Name, 3)
      $ConnectorObject.ConnectorFormat.EndConnect($EndObject, 1)
      $ConnectorObject.RerouteConnections

   EndWith
EndFunc

[AutoBert]

3 hours ago, DeltaVMan said:

AutoIT Version 3.5.4

This version is unknown, use actual stable.

[water]

Add a COM error handler to your script to get more detailed error information. Check ObjEvent in the help file to get an example.

[DeltaVMan]

Thanks to "Water" for the COM error handler info!

I was trying to pass text $Begin_Name instead of an object type (.ActiveSheet.Shapes($Begin_Name) in the Method $ConnectorObject.ConnectorFormat.BeginConnect.

Below is the updated AddConnector_Function that works!

Func AddConnector_Function($Connector_Type, $Begin_X, $End_X, $Begin_Y, $End_Y, $Begin_Name, $End_Name)
   ; Error monitoring. This will trap all COM errors while alive.
   Local $oErrorHandler = ObjEvent("AutoIt.Error", "ErrFunc")

   $oExcel.Sheets ("Flow_Diagram" ).Select
   Local $Connector_Name = $Begin_Name & "_To_" & $End_Name
   Local $Connector_Top = 1
   Local $Connector_Right = 2
   Local $Connector_Bottom = 3
   Local $Connector_Left = 4

   With $oExcel
      .ActiveSheet.Shapes.AddConnector($Connector_Type, $Begin_X, $End_X, $Begin_Y, $End_Y).Select ;Connector type, Begin X location, End X location, Begin Y location, End Y location
      $ConnectorObject = .Selection.ShapeRange
      $ConnectorObject.Name = $Connector_Name
      $ConnectorObject.ConnectorFormat.BeginConnect(.ActiveSheet.Shapes($Begin_Name), $Connector_Bottom)
      $ConnectorObject.ConnectorFormat.EndConnect(.ActiveSheet.Shapes($End_Name), $Connector_Top)
      $ConnectorObject.RerouteConnections
      ;If @error Then Return

   EndWith

   ; Check for errors
    If @error Then MsgBox($MB_SYSTEMMODAL, "COM Error", "@error is set to COM error number." & @CRLF & "@error = 0x" & Hex(@error))

   #forceref $oErrorHandler

EndFunc

[water]

Glad you could solve your problem.

BTW: When posting code could you please use AutoIt code tags (Button "<>" in the editor)? Makes reading your code much easier