copy

Friday, 31 October 2014



Send AutoCAD Commands From Excel & VBA


Introduction



About three weeks ago I received an email request from Mohammed. He asked me if it is possible to send AutoCAD commands directly form Excel. To cut a long story short, the answer is yes and the proposed solution is based on the SendCommand method of AutoCAD, which sends a command string from a VB or VBA application to the document to be processed. If the drawing specified isn’t active, it will be made active. This method processes any AutoCAD command-line function, including LISP expressions. So, based on AutoCAD VBA help, the structure of SendCommand method is given below:

object.SendCommand(Command)

Object: (AutoCAD) Document. The object this method applies to.
Command: String; input-only. The command to send to the document.

Note that the sample workbook that you will find in the Downloads section works only with valid AutoCAD commands (obviously). Moreover, unlike the typical AutoCAD behavior, you must first select an object and then apply any modifications to it. So, for example, if you want to move a polyline, you must first send the select command and then the move; the opposite will NOT work!



VBA code



A large portion of the code is used to initialize the AutoCAD object, as well as the active/new drawing. The code is consisted of two loops; the first one concatenates the contents of every column into a single string, whereas the second one sends the concatenated string to AutoCAD via SendCommand method. One tricky part here was the usage of carriage-return character (vbCr) on Select and Select All (AI_SELALL) commands, which allows the objects to remain selected after the loop moves to the next command (which will make the object editing). However, the latter is not required for the newest version of AutoCAD (2015 - version 20).

Option Explicit

'Declaring the API Sleep subroutine.
#If VBA7 And Win64 Then
    'For 64 bit Excel.
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    'For 32 bit Excel.
    Public Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#End If

Sub SendAutoCADCommands()

    '------------------------------------------------------------------------------------------------------------------------------
    'The macro sends the commands that exist in the sheet named "Send AutoCAD Commands" to the opened or to a new AutoCAD drawing.
    'It uses the AutoCAD SendCommand method to send the user commands. Note that it works only for VALID AutoCAD commands!
    'Moreover, unlike the typical AutoCAD behavior, you must first select an object and then apply any modifications to it.
    'So, if you want to move an object, first send the select command and then the move; the opposite will NOT work!!!
    
    'The code uses late binding, so no reference to external AutoCAD (type) library is required.
    'It goes without saying that AutoCAD, as well as VBA in AutoCAD, must be installed at your computer before running this code.
    
    'Written By:    Christos Samaras
    'Date:          15/10/2014
    'Last Update:   15/12/2014
    'E-mail:        xristos.samaras@gmail.com
    'Site:          http://www.myengineeringworld.net
    '------------------------------------------------------------------------------------------------------------------------------
        
    'Declaring the necessary variables.
    Dim acadApp     As Object
    Dim acadDoc     As Object
    Dim acadCmd     As String
    Dim sht         As Worksheet
    Dim LastRow     As Long
    Dim LastColumn  As Integer
    Dim i           As Long
    Dim j           As Integer
    
    'Set the sheet name that contains the commands.
    Set sht = ThisWorkbook.Sheets("Send AutoCAD Commands")
    
    'Activate the Send AutoCAD Commands sheet and find the last row.
    With sht
        .Activate
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    End With
        
    'Check if there is at least one command to send.
    If LastRow < 13 Then
        MsgBox "There are no commands to send!", vbCritical, "No Commands Error"
        sht.Range("C13").Select
        Exit Sub
    End If
    
    'Check if AutoCAD application is open. If it is not opened create a new instance and make it visible.
    On Error Resume Next
    Set acadApp = GetObject(, "AutoCAD.Application")
    If acadApp Is Nothing Then
        Set acadApp = CreateObject("AutoCAD.Application")
        acadApp.Visible = True
    End If
        
    'Check (again) if there is an AutoCAD object.
    If acadApp Is Nothing Then
        MsgBox "Sorry, it was impossible to start AutoCAD!", vbCritical, "AutoCAD Error"
        Exit Sub
    End If
    
    'Maximize AutoCAD window.
    acadApp.WindowState = 3 '3 = acMax  in early binding
    On Error GoTo 0
    
    'If there is no active drawing create a new one.
    On Error Resume Next
    Set acadDoc = acadApp.ActiveDocument
    If acadDoc Is Nothing Then
        Set acadDoc = acadApp.Documents.Add
    End If
    On Error GoTo 0

    'Check if the active space is paper space and change it to model space.
    If acadDoc.ActiveSpace = 0 Then '0 = acPaperSpace in early binding
        acadDoc.ActiveSpace = 1     '1 = acModelSpace in early binding
    End If
        
    With sht
    
        'Loop through all the rows of the sheet that contain commands.
        For i = 13 To LastRow
            
            'Find the last column.
            LastColumn = .Cells(i, .Columns.Count).End(xlToLeft).Column
            
            'Check if there is at least on command in each row.
            If LastColumn > 2 Then
                
                'Create a string that incorporates all the commands that exist in each row.
                acadCmd = ""
                For j = 3 To LastColumn
                    If Not IsEmpty(.Cells(i, j).Value) Then
                        acadCmd = acadCmd & .Cells(i, j).Value & vbCr
                    End If
                Next j
                 
                'Check AutoCAD version.
                If Val(acadApp.Version) < 20 Then
                    'Prior to AutoCAD 2015, in Select and Select All commands (AI_SELALL) the carriage-return
                    'character 'vbCr' is used, since another command should be applied in the selected items.
                    'In all other commands the Enter character 'Chr$(27)' is used in order to denote that the command finished.
                    If InStr(1, acadCmd, "SELECT", vbTextCompare) > 0 Or InStr(1, acadCmd, "AI_SELALL", vbTextCompare) Then
                       acadDoc.SendCommand acadCmd & vbCr
                    Else
                       acadDoc.SendCommand acadCmd & Chr$(27)
                    End If
                Else
                    'In the newest version of AutoCAD (2015) the carriage-return
                    'character 'vbCr' is applied in all commands.
                    acadDoc.SendCommand acadCmd & vbCr
                End If
            
            End If
            
            'Pause a few milliseconds  before proceed to the next command. The next line is probably optional.
            'However, I suggest to not remove it in order to give AutoCAD the necessary time to execute the command.
            Sleep 20
            
        Next i
        
    End With
    
    'Inform the user about the process.
    MsgBox "The user commands were successfully sent to AutoCAD!", vbInformation, "Done"
      
End Sub

Note that if you have AutoCAD 2010 or a newer version, you will have to download and install the VBA module, otherwise the code will probably fail. 
All links were copied from the Autodesk's website.



Warnings and suggestions



  • Note that the use of SendCommand is NOT a panacea! It’s probably just a hack that sometimes has to be used because Autodesk hasn't given a complete VBA object model. As Autodesk suggests, "you should never use this method to issue a command for which there is an ActiveX method available"; to add text for example use the AddText method.
  • This method is generally synchronous. However, if the command sent with this method requires any user interaction (such as picking a point on the screen) then this method will continue as soon as the user input begins (i.e. MTEXT, TEXT etc.).
  • Please bear in mind that the particular code was NOT tested for every AutoCAD command that can be executed via command line and probably will NOT work for every command. However, tests for some of the most “popular” ones showed that it works without problem (check the demonstration video for example). You just have to write the command sequence in Excel very carefully.
  • When you select an object try to create a selection rectangle that will include that object. This means that the selection rectangle – its coordinates – should be larger than the object dimensions.
  • The coordinates should be given in the format x,y for 2D and x,y,z for 3D objects; i.e. 0,100 and 200,200,100. 
  • The "angular" coordinates should be given in the x<y for 2D and in x<y<z format for 3D; i.e. 0<45 and 100<45<90. 
  • Note that the cells in the sample workbook are formatted as text, in order to avoid problems when the user enters decimal coordinates and the decimal symbol in his/her Windows settings (in Region and Language option at Control Panel) is set to be the comma (",") and not the dot ("."). Tip: AutoCAD uses a dot/point for decimal separator as a standard for displaying and entering decimal numbers. However, Dimensions are the exception to this rule, but, you can set their decimal separator by using the DIMDSEP command.
  • Probably the best way to use the sample workbook is to try to replicate every step that you follow when you are using AutoCAD. So, try to not forget command arguments or leave commands unfinished. Needless to say that some experimentation is required in order to get the expected result, but I think it worth the time.


Demonstration video



The short video below demonstrates the result of the above VBA code (31 AutoCAD commands were used); the blog name will be drawn into a new AutoCAD document using lines, polylines as well as AutoCAD objects.



Did you like this post? If yes, then share it with your friends. Thank you!



Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info