Excel crashing after running macro to work with autocad and after deleting column

[elimgo]

Hi all,
I have a macro to copy selected cells and paste it into specific format, it’s basically a coordinate(easting and northing) list with semicolon delimiter, and then save it into notepad.


After that my macro open autocad and call a lisp that I wrote in autocad to import the notepad into point with description.
I use excel and autocad 2007.


My macro works as expected. However I have small irritating problem. Which is after I run the macro and the point is plotted on the autocad, if i deleted columns in excel by selecting by highlighting in the “ A B C D….” bar (For example I deleted column F to column I by highlighting the I to F bar), and then I click the “File” or “Edit” in the autocad toolbar. The excel will crash. It will close itself and then opened again in recovered mode.
When I click the “file” or “edit” the mouse cursor is showing the “thinking” animation for a second. And then the excel just crashed.


After several testing, the workaround is to perform an action in excel. For example if I copy and paste a random cell after I deleted columns, when I click the “File” or “Edit” it will not crash the excel. I’m guessing it’s a problem with the way I wrote my code and something related to selection.


It’s just that I want this code to be also used by my friends and I want it to be free of bugs.


I wonder if there’s something that I can improve in my vba code? I tried of many ways that I can think of but to no avail.
I hope that there’s someone with similar problem that know the solution or someone that can enlighten me with the problem


Thank you very much.

Edit: See next post. ST

[SamT]

Excel Code From OP's Zip file: (With some comments removed)


This script is to draw point with description in the current active autocad drawing
Selected cells will be concatenated to Navipac wp2 format and then saved in L:\Plot to CAD\XLtoCAD.wp2
And then it will open autocad or if autocad already opened will tell the cad to type command "wew", which is custom LISP to draw point from wp2 format file
Point will be saved in layer "Point", and description will be saved in layer "Description" with magenta color
Agung Hutomo 2016

'sub to work with cad is from howtoautocad.com/excel-autocad--a-match-made-in-heaven-again/

Sub open_Cad()
Dim warning As Integer

'ENSURES USER KNOWS OPEN DRAWING WILL BE EDITED, YES NO TO PROCEED
warning = MsgBox("Selected Coordinates will be plotted into currently opened drawing." & vbCrLf & "Would you like to continue?", vbYesNo, "Data Loss Warning")

Select Case warning
'if they select yes to proceed
Case 6
    Set ACAD = AcadApplication 

    On Error Resume Next 'This tells VBA to ignore errors
    Set ACAD = GetObject(, "AutoCAD.Application") 
    On Error GoTo 0 

    If ACAD Is Nothing Then 
        Set ACAD = New AcadApplication 
        ACAD.Visible = True 
    End If
Case 7
    Exit Sub
End Select

ACAD.ActiveDocument.SendCommand ("wew ") 'Print a message to the AutoCAD® command line
ACAD.ActiveDocument.SendCommand ("regen ")
    
MsgBox "The coordinates was successfully exported to Autocad!", vbInformation, "Finished"

End Sub

Sub concatwptocad()
'concatenate selected desc, e, n into wp2 format

    Dim ActSheet As Worksheet
    Dim SelRange As Range
    Dim warn As Integer
            
    Set ActSheet = ActiveSheet
    Set SelRange = Selection
    
    Application.ScreenUpdating = False
            
    ActSheet.Select
    SelRange.Select
    Selection.Copy
    
    Sheets.Add After:=ActSheet
    Range("A1").Select
    ActiveSheet.Paste
    Range("D1").Select
    Application.CutCopyMode = False
    
    If Range("A2") = vbNullString Then
    ActiveCell.FormulaR1C1 = "=CONCATENATE(char(34),RC[-3],char(34),char(59),RC[-2],char(59),RC[-1],char(59),""0.000"",char(59),14.1,char(59),4.1,char(59),14.1,char(59),char(34),""Arial"",char(34),char(59),""0.00"",char(59),-2.1,char(59),char(34),char(34),char(59),""0.00"",char(59),char(34),char(34),char(59),1,char(59),""0.000"",char(59),""0.000"",char(59),""0.000"",char(59),0,char(59),0.05)"
    Else
    ActiveCell.FormulaR1C1 = "=CONCATENATE(char(34),RC[-3],char(34),char(59),RC[-2],char(59),RC[-1],char(59),""0.000"",char(59),14.1,char(59),4.1,char(59),14.1,char(59),char(34),""Arial"",char(34),char(59),""0.00"",char(59),-2.1,char(59),char(34),char(34),char(59),""0.00"",char(59),char(34),char(34),char(59),1,char(59),""0.000"",char(59),""0.000"",char(59),""0.000"",char(59),0,char(59),0.05)"
    
    Range("D1").Select
    Selection.AutoFill Destination:=Range("D1:D" & Range("A" & Rows.Count).End(xlUp).Row)
    End If
    
    With Application
    If Range("A2") = vbNullString Then
    Range("D1").Select
    Selection.Copy
    Else
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Application.CutCopyMode = False
    End If

    Call Copytonotepad
    End With
    
    Call delsh    
    ActSheet.Activate
    
    Call open_Cad
    
    Application.ScreenUpdating = True
    Set SelRange = Nothing
    Set ActSheet = Nothing
End Sub

Private Sub Copytonotepad()

    Dim f As Integer, c As Range
    f = FreeFile
    Open "L:\Plot to CAD\XLtoCAD.wp2" For Output As #f
    For Each c In Selection
        Print #f, Replace(c.Value, vbLf, vbCrLf)
    Next c
    Close #f
    Exit Sub
End Sub

[SamT]

Don't know if it will help, but this is how I would write your code. Note that even though it Compiles, there may be some typos in this,

Option Explicit

Sub concatwptocad()
     'concatenate selected desc, e, n into wp2 format
     
    Dim NewSht As Worksheet
    Const DQ As String = """ 'DoubleQuote
    Const SC As String = ";" 'SemiColon
     
    Application.ScreenUpdating = False
     
    Selection.Copy
     
    Sheets.Add After:=ActiveSheet
    Set NewSht = ActiveSheet
    
    With NewSht
      .Range("A1").PasteSpecial xlPasteValues
      Application.CutCopyMode = False
       
      .Range("D1").FormulaR1C1 = "=CONCATENATE(DQ,RC[-3],DQ,SC,RC[-2]," _
        & "SC,RC[-1],SC,""0.000"",SC,14.1,SC,4.1,SC, " _
        & "14.1,SC,DQ,""Arial"",DQ,SC,""0.00"",SC,-2.1," _
        & "SC,DQ,DQ,SC,""0.00"",SC,DQ,DQ,SC," _
        & "1,SC,""0.000"",SC,""0.000"",SC,""0.000"",SC,0,SC,0.05)"
      
      .Range("D1").AutoFill Destination:=.Range("D1:D" & .Range("A1").CurrentRegion.Rows.Count)
       
       CopyToNotepad Range(.Range("D1"), Cells(Rows.Count, "D").End(xlUp)).Select
    End With
     
    'Call delsh 'Unknown code
    ActSheet.Activate
     
    Call open_Cad
     
    Application.ScreenUpdating = True
End Sub

Private Sub CopyToNotepad(Rng As Range)
     
    Dim f As Integer, c As Range
    f = FreeFile
    
    Open "L:\Plot to CAD\XLtoCAD.wp2" For Output As #f
    
    For Each c In Rng
        Print #f, Replace(c.Value, vbLf, vbCrLf)
    Next c
    
    Close #f
End Sub

Sub open_Cad()

Dim ACAD As Object
    
    If MsgBox("Selected Coordinates will be plotted into currently opened drawing." _
      & vbCrLf & "Would you like to continue?", _
      vbYesNo, "Data Loss Warning") <> 6 Then Exit Sub
        
        Set ACAD = AcadApplication
         
        On Error Resume Next
        Set ACAD = GetObject(, "AutoCAD.Application")
        On Error GoTo 0
         
        If ACAD Is Nothing Then
            Set ACAD = New AcadApplication
            ACAD.Visible = True
        End If
     
      ACAD.ActiveDocument.SendCommand ("wew ") 'Print a message to the AutoCAD® command line
      ACAD.ActiveDocument.SendCommand ("regen ")
     
    MsgBox "The coordinates was successfully exported to Autocad!", vbInformation, "Finished"
End Sub

[elimgo]

Hi Sam

That seems a neat code.
I will try it

Thank you