TheSwamp
Code Red => VB(A) => Topic started by: VELI555 on January 24, 2012, 06:07:50 AM
-
Hi everybody.
I am new member here and I have tried to find answer to my problem. I am a student and I am working on my thesis right now. I have a problem here. How can I make a both way link between Excel and AutoCad. By that I mean if I move a block in AutoCad drawing it would be great if the Excel could update the new coordinates to Excel worksheet. And other way around if I change the blocks coordinates in Excel it would move the block in AutoCad drawing. I have red that this can be done by VBA but I don't know how? Could you help me with this? Thank you already. ( I apologize possible mistakes in spelling)
Best Regards
Veli
-
You would need 2 VBA apps:
1. From AutoCAD open and update Excel or a direct update of a csv file.
2. From Excel open and update Autocad or use Open Design software.
If it is just block refs this should be fairly easy if the block name is unique or you save the object handle in excel. WARNING: If a drawing has to be recovered the handle could change or the block name could change!!
This is why my blocks have a Record Number Attribute that is unique for each block insertion.
Hope this helps in getting you started.
-
Hi Chuck
So you give every block a different number in to the attribute data. And you use this number to recognize each block. Right? What do you mean by using two different VBA apps?
-
Hi Chuck
So you give every block a different number in to the attribute data. And you use this number to recognize each block. Right? What do you mean by using two different VBA apps?
Every object in AutoCAD has a unique ID number - a "handle". You would use this to update the spreadsheet.
You don't need two apps. I think he was pointing out that it could be done from AutoCAD to Excel or vice versa. The code to do it from Excel to AutoCAD might be a bit longer, but not by much.
I might have an old sample app kicking around somewhere. If I find it, I'll post it.
-
Ok thanks for the info, but isn't the "handle" only greated when you export block information to somewhere? And if you send that info again the AutoCad will greate a new handle even though it was the same block? A sample app would be wery great thank you.
-
If you recover a dwg i have noticed that some times it will recreate a block ref and give it a different handle
WARNING: If a drawing has to be recovered the handle could change or the block name could change!!
From my first post.
as for the two VBA apps:
Say I open the excel spread sheet without opening AutoCAD
Make a change on the insertion point save and close then I
send the dwg as an attachment to someone.
If the VBA app is only in the dwg did making a change in excel make the change in the dwg??
If i open the dwg use the move command or grab the grip and move the block save and close.
If I only have a VBA app in excel and I email the excel file without opening it it doesn't have the changes in it.
-
More on the handle thing:
I have a custom GIS system written in AutoCAD 2004 (NOT MAP).
I use the handle and an attribute for two way connectivity and redundancy.
In the Database I store the Handle and a Incremented long value (Rec_Num).
Then after a recover of the dwg file we can run a Database reconnect function that
1. creates a selection set of the blocks with the Rec_Num att
2 For each object searches the database and saves the correct handle if it doesn't match.
We have seen up to about 150 handle changes in the map per recovery.
-
Here is some AutoCAD code to get you started (it is in AutoCAD 2004)
You will need to add a reference to Microsoft Excel in the VBA Project
All code is in the ThisDrawing Module
Option Explicit
Private ExcelApp As Excel.Application
Private Sub AcadDocument_ObjectAdded(ByVal Object As Object)
Dim objBlkRef As AcadBlockReference
Dim varInsPnt As Variant
Dim strHandle As String
'New Entity
'Add to Excel File
If TypeOf Object Is AcadBlockReference Then
Set objBlkRef = Object
varInsPnt = objBlkRef.InsertionPoint
strHandle = objBlkRef.Handle
If UpdateExcel(strHandle, varInsPnt, "New") = False Then
Debug.Print "There was an Error Updating Excel"
End If
End If
End Sub
Private Sub AcadDocument_ObjectModified(ByVal Object As Object)
Dim objBlkRef As AcadBlockReference
Dim varInsPnt As Variant
Dim strHandle As String
'Existing Entity
'Should be in Excel File
If TypeOf Object Is AcadBlockReference Then
Set objBlkRef = Object
varInsPnt = objBlkRef.InsertionPoint
strHandle = objBlkRef.Handle
If UpdateExcel(strHandle, varInsPnt, "Existing") = False Then
Debug.Print "There was an Error Updating Excel"
End If
End If
End Sub
Private Function UpdateExcel(sHandle As String, varpnt As Variant, sAction As String) As Boolean
Dim objWorkBook As Workbook
Dim objSheet As Worksheet
Dim objUsedRange As Range
Dim lngRows, lngRow As Long
Dim strFile As String
Dim blnFound As Boolean
Dim intcnt As Integer
On Error GoTo Err_Control
strFile = Replace(ThisDrawing.FullName, ".dwg", ".xlsx")
If ConnectToExcel = True Then
Set objWorkBook = ExcelApp.Workbooks.Open(strFile)
Set objSheet = objWorkBook.Worksheets(1)
Set objUsedRange = objSheet.UsedRange
'Get Last used row
lngRows = objUsedRange.Rows.Count + 1
Select Case sAction
Case "New"
'Add new blocks info
objSheet.Cells(lngRows, 1) = sHandle
For intcnt = LBound(varpnt) To UBound(varpnt)
objSheet.Cells(lngRows, intcnt + 2) = varpnt(intcnt)
Next
Case "Existing"
'Find Handle in Used Rows
For lngRow = 1 To lngRows - 1
If sHandle = objSheet.Cells(lngRow, 1) Then
For intcnt = LBound(varpnt) To UBound(varpnt)
objSheet.Cells(lngRow, intcnt + 2) = varpnt(intcnt)
Next
blnFound = True
End If
Next
If blnFound = False Then 'Didn't find it add it
objSheet.Cells(lngRows, 1) = sHandle
For intcnt = LBound(varpnt) To UBound(varpnt)
objSheet.Cells(lngRows, intcnt + 2) = varpnt(intcnt)
Next
End If
Case Else 'this should not happen unless you mistype the action name
End Select
objWorkBook.Save
objWorkBook.Close
Set ExcelApp = Nothing
UpdateExcel = True
End If
Exit_Here:
Exit Function
Err_Control:
Select Case Err.Number
Case 1004 'File doesn't exist (Not sure if anything else causes this error)
'create a new workbook and save as
Set objWorkBook = ExcelApp.Workbooks.Add
objWorkBook.SaveAs strFile
Resume
Case Else
Debug.Print Err.Number & ": " & Err.Description
Resume Exit_Here
End Select
End Function
Private Function ConnectToExcel() As Boolean
On Error GoTo Err_Control
Set ExcelApp = GetObject("Excel.Application")
ConnectToExcel = True
Exit_Here:
ExcelApp.AlertBeforeOverwriting = False
Exit Function
Err_Control:
Select Case Err.Number
Case Else
Set ExcelApp = CreateObject("Excel.Application")
ConnectToExcel = True
Resume Exit_Here
End Select
End Function
-
Ok. I have to take a closer look about what you wrote. I am not familiar with VBA myself but I can get help with it. Thank you very much for your help.
-
We can help you here but we need more information.
ie What do you know about coding, What versions of AutoCAD and Excel, What is the full scoop of the project, Do you have a running copy of both on the computer you have...
Also What is this project for and what are you having to turn in?
I do not want to just give you code for you to get a grade on. I do want help you understand the code and how to think it through. I do not know how everyone else feels about this it is just my feelings. Helping someone get code to work for their job is one thing but giving code to someone for a grade is totally different IMHO. That being said, stick around and we can hash the code till you understand it and can write what you need with just a little help here and the F1 key.
-
Souds great ChuckHardin. My intent wasn't just take a code and have a credit of it and leave this forum. My intent was to get some kind of base where to start from. I am a man of honor and I give the honor where it belongs. Like I said I am student and I am working my thesis. I am trying to solve different kind of possibilties to automate AutoCad (and other planning program) for one company. And one problem I have, is this two way link between AutoCad and Excel. Like if there is a X and Y coordinates in cells of their own in Excel, and if you change thous coordinates in Excel it would change the location of a block in AutoCad and other way around. I have no expirience about coding, but my teachers can help me with that. But if you guys can help me also it would be great. There is never too much help around. But my answering might take time becaus I dont use english as my motherlanguage and sometimes I have to solve what you wrote to me. I am using several computers so I have might have different versions of AutoCad and Exel in different computers. But I think usually I use AutoCad 2011 and Excel 2010. It would also be wery great to really understand the code. I will state this forum in my thesis bibliography and if you want I can also state names (or namemarks) of the persons which have helped me.
-
Good deal thanks for explaining. I will post code with lots of comments and help as much as I can. I will also explain the errors in the last code and why they happen. Give me about an hour or so and I will post back. Thanks for understanding my feelings on this.
-
First Lets just write out what you need to do in plain writing:
The AutoCAD side of the app (For editing the drawing.)
For every block added or edited
Get the handle
Get the insertion point
Get the Block name
Get the attributes
Open the excel spread sheet
Save the values into the appropriate column
next block
Save the spread sheet
close it and excel
When should we do this?
the options are:
After object added
1. If there are prompts for Attribute Values this can break.
2. This would open excel and add a single line every time we added a block = Slows the operator down every time
After object modified
1. This does not handle erasing blocks from the drawing
2. This would open excel and loop through the rows to find the row to update every time we modified a block = Slows the operator down every time
After Saving the drawing
1. We would be better off to delete all the blocks out of the spread sheet and add all the values
2. This would handle blocks being erased
3. This would handle someone not saving the drawing before exiting
4. This will also do save as and recreate a new spread sheet with the same name as the new drawing file.
5. Opens excel 1 time and runs through all blocks after the operator is done editing the drawing = Slows the save command down
-
Ok, I have to take a closer look, but one thing I have to ask. Chuck wrote "get the handle", but where can I see the handle of a block?
-
If you LIST any object in AutoCAD (using the standard LIST command) you'll see an entry for HANDLE.
-
here is where it would be in code:
Just Dim a variable in the AutoCAD VBA Editor
Then type the variable name with a "." on the end and it SHOULD magically give you a drop down list of properties and methods.
Select what you think you want then click your cursor in that word and hit [F1]. This should open the help file and give you a short explanation of the property or method.
Dim objBlkRef As AcadBlockReference
If TypeOf Object Is AcadBlockReference Then
Set objBlkRef = Object
varInsPnt = objBlkRef.InsertionPoint
strHandle = objBlkRef.Handle
endif
-
Ok I see. Thanks guys. I get back to you, likely with more questions.
-
Great we will be here to help.
-
I have just made something like this in Visual Basic .NET using the ObjectARX 2012 API.
Builtin Excel support makes this an easy go, I just had some trouble with writing to the block attributes,
but the great guys/girls here @TheSwamp helped me conquer that hurdle.
If this could be of help to you, just drop a Private Message.
Greetz FRaccie
-
Thanks FRaccie. I am not familiar with programming but does ObjectArx use C++ programming language? I have never used it and I have red from internet that it is quite difficult language to learn.
-
Thats a bit of a mixed message. VB.NET is *only* available as managed dotNET API, whereas ObjectARX is only C++ (and as you note, very complex). VB.NET is mildly more complex than VBA but a relatively easy jump across. Kind of like jumping a puddle vs. swimming the Atlantic.
-
Ok, thanks for explaining dgorsman (always when I see your namemark it reminds me of a very good writer Dave Grossman). I think C++ is out of my league. I think I try to manage with VBA for now. But I really appreciate your offer FRaccie. If things changes I send you a private message. Thank you very much.
-
ChuckHardin can I ask somthing about your code? What does this part of it do? Its the first part of it, but I dont quite understand it.
Private Sub AcadDocument_ObjectAdded(ByVal Object As Object)
Dim objBlkRef As AcadBlockReference
Dim varInsPnt As Variant
Dim strHandle As String
'New Entity
'Add to Excel File
If TypeOf Object Is AcadBlockReference Then
Set objBlkRef = Object
varInsPnt = objBlkRef.InsertionPoint
strHandle = objBlkRef.Handle
If UpdateExcel(strHandle, varInsPnt, "New") = False Then
Debug.Print "There was an Error Updating Excel"
End If
End If
End Sub
I think I managed to add a reference to Microsoft Excel in the VBA Project. My teacher helped me.
-
This is one of the pieces that needs to change where it is housed but the code will stay basically the same with some additions. DID NOT Check this code just typed it in the box here. I would also suggest using a Sub for all of this and calling it from the EndSave Event.
'Private Sub AcadDocument_ObjectAdded(ByVal Object As Object) 'This is what is triggering the code execution and needs to be put in the End Save event:
Private Sub AcadDocument_EndSave(ByVal FileName As String)
dim objEnty as ACADEntity
Dim objBlkRef As AcadBlockReference
Dim varInsPnt As Variant
Dim strHandle As String
'Need to do a Selection Set that gets all blockRefs in the dwg
Dim objSelSet as ACADSelectionSet
'set the selection set
Set objSelSet = Thisdrawing.PickfirstSelectionSet
'Create a filter that gets only Block References
Dim intType(0 ) As Integer
Dim varData(0) As Variant
intType(0) = 0: varData(0) = "INSERT"
'Fill the Selection Set
objSelSet.Select Mode:=acSelectionSetAll, FilterType:=intType, FilterData:=varData
'Now step through the Selection Set for each Entity and apply the following code:
'For lngCnt = 0 to objSelSet.count-1 'Kinda like this
' Set objEnty = objSelset.items(lngcnt)
If TypeOf objEnty Is AcadBlockReference Then 'It is a Block Ref
Set objBlkRef = objEnty 'Set it so the drop down works after typing a "."
varInsPnt = objBlkRef.InsertionPoint 'Get the insertion point
strHandle = objBlkRef.Handle 'Get the Handle
'This code could be changed to update excel directly instead of calling another function
If UpdateExcel(strHandle, varInsPnt, "New") = False Then
Debug.Print "There was an Error Updating Excel"
End If
End If
'next 'Next entity in selection set
End Sub
-
Thanks again ChuckHardin. I will get into that code again on monday when I can try it. At home I can only use AutoCad LT. Have a nice weekend.
-
I have another question about the code you sent me ChuckHardin. When I move the block in AutoCad dwg. I get this error message"type mismatch" and the part of the code which the VBA marks incorrect is this:
Dim objBlkRef As AcadBlockReference
Dim varInsPnt As Variant
Dim strHandle As String
Dim UpdateExcel As Variant
'Existing Entity
'Should be in Excel File
If TypeOf Object Is AcadBlockReference Then
Set objBlkRef = Object
varInsPnt = objBlkRef.InsertionPoint
strHandle = objBlkRef.Handle
If UpdateExcel(strHandle, varInsPnt, "Existing") = False Then
Debug.Print "There was an Error Updating Excel"
'MsgBox ("blokkia muutettiin")
End If
End If
End Sub
Can you tell me what should be the contents of these variants: varInsPnt and strHandle? I also attached a picture where you can see the values of those variables. In that picture, I think that the varInsPnt(0) is X-coordinate (or Y) and the varInsPnt(2) is Y-coordinate (or X I am not sure) so the varInsPnt(3) must bee Z-coordinate. the strHandle must bee the handle of the block but why is the UpdateExcel empty? Can you also tell me what means the entity in Excel (I mean this comment here: 'Existing Entity 'Should be in Excel File) and which kind of form the information should be in Excel? I hope you understand what I mean.
-
UpdateExcel(strHandle, varInsPnt, "Existing") = False
is a function You need to write to update the excel file.
I wrote a quick one in one of my first post in this thread.
If you don't have it in your code it will stop the execution on that line.
varInsPnt(0)=x
varInsPnt(1)=y
varInsPnt(2)=z
strHandle=The unique identifier in the drawing for that object
Also remember you might want to wait till the save command finishes to run the code once
After save you have the new file name with path.
You can select all inserts in the drawing
Loop through the blocks
save a new excel workbook with all the block information.
done
-
I think I still have something wrong here. When I move a block in drawing. I still get the type mismatch failure. I have that function you mentioned in the code. But somehow when I get the failure and go to VBA window to see the code and move the mouse over the varInsPnt variable it will show me the strHandle="19F" value, but when I open the locals Window I see the X.Y and Z coordinates. I attached a picture so you can see what I mean. Are the tree values (x,y,z coordinates) under the one variable (varInsPnt) somehow, or is there something missing, and if they are why does the strHandle="19F" value shown when I move the mouse over the varInsPnt variable?
-
Hope my translations are right.
Option Explicit
Private ExcelApp As Excel.Application
Private Sub AcadDocument_EndSave(ByVal FileName As String)
Debug.Print FileName
Stop 'Put this in here so you can see how the code works
'Just press [F8] to step to the next line
DoExcelUpdate Replace(FileName, ".dwg", ".xlsx")
End Sub
Private Sub DoExcelUpdate(sFileName As String)
Dim objWorkBook As Workbook
Dim objSheet As Worksheet
Dim objUsedRange As Range
Dim lngRow As Long
Dim objSelSet As AcadSelectionSet
Dim intType(0) As Integer
Dim varData(0) As Variant
Dim objEnty As AcadEntity
Dim objBlkRef As AcadBlockReference
Dim varInsPnt As Variant
Dim strHandle As String
Dim lngCnt As Long
On Error GoTo Err_Control
'Open Excel
If ConnectToExcel = False Then
MsgBox "Ei voitu yhdistää Excel!" & vbCrLf & "Poistuminen nyt!"
End If
If FileExist(sFileName) = True Then
'Set objSheet
Set objWorkBook = ExcelApp.Workbooks.Open(sFileName)
Set objSheet = objWorkBook.Worksheets(1)
Set objUsedRange = objSheet.UsedRange
'Clear Excel Spread Sheet
objUsedRange.ClearContents
Else
'Create Sheet and return objSheet
Set objWorkBook = ExcelApp.Workbooks.Add
objWorkBook.SaveAs sFileName
Set objSheet = objWorkBook.Worksheets(1)
End If
'Set Column Headers
'Not sure of the Translation
objSheet.Cells(1, 1) = "blokkia nimi" 'lohkonimi Block Name
objSheet.Cells(1, 2) = "kahva" 'Handle
objSheet.Cells(1, 3) = "X"
objSheet.Cells(1, 4) = "Y"
objSheet.Cells(1, 5) = "Z"
lngRow = 2 'This is the next row after the header row
'Set the selection set
Set objSelSet = ThisDrawing.PickfirstSelectionSet
'Create a filter that gets only Block References
intType(0) = 0: varData(0) = "INSERT"
'Fill the Selection Set
objSelSet.Select Mode:=acSelectionSetAll, FilterType:=intType, FilterData:=varData
'Now step through the Selection Set for each Entity and apply the following code:
For Each objEnty In objSelSet 'Kinda like this
'Set objEnty = objSelSet.items(lngCnt)
If TypeOf objEnty Is AcadBlockReference Then 'It is a Block Ref
Set objBlkRef = objEnty 'Set it so the drop down works after typing a "."
varInsPnt = objBlkRef.InsertionPoint 'Get the insertion point
strHandle = objBlkRef.Handle 'Get the Handle
'If you want Attribute values you will need to do a Function that returns the Att Values
Debug.Print objBlkRef.Name & " Handle: " & strHandle & " Insertion Point: " & CStr(varInsPnt(0)) & ", " & CStr(varInsPnt(1)) & ", " & CStr(varInsPnt(2))
'Put in excel here
objSheet.Cells(lngRow, 1) = objBlkRef.Name
objSheet.Cells(lngRow, 2) = strHandle
objSheet.Cells(lngRow, 3) = CStr(varInsPnt(0))
objSheet.Cells(lngRow, 4) = CStr(varInsPnt(1))
objSheet.Cells(lngRow, 5) = CStr(varInsPnt(2))
'Row adder
lngRow = lngRow + 1
End If
Next 'Next entity in selection set
'Save sheet
objWorkBook.Save
objWorkBook.Close
Set ExcelApp = Nothing
Exit_Here:
Exit Sub
Err_Control:
Select Case Err.Number
Case Else
Debug.Print Err.Number & ": " & Err.Description
Resume Exit_Here
End Select
End Sub
Public Function FileExist(strFile As String) As Boolean
'ei tiedosto olemassa?
If Dir(strFile, vbNormal Or vbReadOnly Or vbHidden Or vbSystem Or vbArchive) = "" Then
FileExist = False
Else
FileExist = True
End If
End Function
-
Hi Everybody
Some time has pass since my last visit here. I have been busy doing my work. Chuck Hardin has helped me a lot to get the link between Excel and AutoCad work in both ways. I want to thank him for that. But now he has been busy so I would like to ask help from the other members here. We managed to get the link work (thanks to Chuck) both ways. But now we would also like to get the rotation value of a block to excel and when changin the value in Excel it would rotate the block in AutoCad? Do anyone know how it would be done?
Best regards
Veli