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
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
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 :)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.