PDA

View Full Version : Excel crashing after running macro to work with autocad and after deleting column



elimgo
09-29-2016, 01:15 PM
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.:yes


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
09-30-2016, 07:03 AM
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
09-30-2016, 08:16 AM
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
09-30-2016, 10:32 AM
Hi Sam :friends:

That seems a neat code.
I will try it

Thank you :)