DeltaVMan

How to Use AutoIT to connect shapes in Excel

7 posts in this topic

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

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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

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

 

Edited by water
1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
3 hours ago, DeltaVMan said:

AutoIT Version 3.5.4

This version is unknown, use actual stable.

1 person likes this

Share this post


Link to post
Share on other sites

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

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now