Author Topic: Both way link between Excel and AutoCad  (Read 28540 times)

0 Members and 1 Guest are viewing this topic.

ChuckHardin

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #15 on: January 27, 2012, 01:56:57 PM »
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.

Code: [Select]

Dim objBlkRef As AcadBlockReference

If TypeOf Object Is AcadBlockReference Then
      Set objBlkRef = Object
      varInsPnt = objBlkRef.InsertionPoint
      strHandle = objBlkRef.Handle
endif


VELI555

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #16 on: January 29, 2012, 08:39:33 AM »
Ok I see. Thanks guys. I get back to you, likely with more questions.

ChuckHardin

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #17 on: January 30, 2012, 12:34:08 PM »
Great we will be here to help.

FRaccie

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #18 on: January 31, 2012, 08:18:35 AM »
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

VELI555

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #19 on: January 31, 2012, 08:42:37 AM »
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.

dgorsman

  • Water Moccasin
  • Posts: 2437
Re: Both way link between Excel and AutoCad
« Reply #20 on: January 31, 2012, 10:31:05 AM »
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.
If you are going to fly by the seat of your pants, expect friction burns.

try {GreatPower;}
   catch (notResponsible)
      {NextTime(PlanAhead);}
   finally
      {MasterBasics;}

VELI555

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #21 on: February 01, 2012, 08:26:35 AM »
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.

VELI555

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #22 on: February 02, 2012, 09:29:53 AM »
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.

ChuckHardin

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #23 on: February 02, 2012, 01:13:13 PM »
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.


Code: [Select]
'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

VELI555

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #24 on: February 03, 2012, 09:23:05 AM »
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.

VELI555

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #25 on: February 10, 2012, 05:30:16 AM »
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.

ChuckHardin

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #26 on: February 10, 2012, 09:28:36 AM »
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

VELI555

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #27 on: February 13, 2012, 07:23:39 AM »
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?

ChuckHardin

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #28 on: February 13, 2012, 01:58:59 PM »
Hope my translations are right.

Code: [Select]
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

« Last Edit: February 13, 2012, 02:03:01 PM by ChuckHardin »

VELI555

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #29 on: March 27, 2012, 03:11:22 AM »
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