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

0 Members and 1 Guest are viewing this topic.

VELI555

  • Guest
Both way link between Excel and AutoCad
« 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

ChuckHardin

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #1 on: January 24, 2012, 01:20:05 PM »
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.

VELI555

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #2 on: January 25, 2012, 07:20:11 AM »
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?

Matt__W

  • Seagull
  • Posts: 12954
  • I like my water diluted.
Re: Both way link between Excel and AutoCad
« Reply #3 on: January 25, 2012, 09:40:32 AM »
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.
Autodesk Expert Elite
Revit Subject Matter Expert (SME)
Owner/FAA sUAS Pilot @ http://skyviz.io

VELI555

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

ChuckHardin

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #5 on: January 25, 2012, 05:01:06 PM »
If you recover a dwg i have noticed that some times it will recreate a block ref and give it a different handle

Quote
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.

ChuckHardin

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #6 on: January 25, 2012, 05:08:15 PM »
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.

 

ChuckHardin

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #7 on: January 25, 2012, 06:30:37 PM »
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
Code: [Select]
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

VELI555

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #8 on: January 26, 2012, 01:42:50 AM »
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.

ChuckHardin

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #9 on: January 26, 2012, 01:51:30 AM »
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.

VELI555

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #10 on: January 26, 2012, 03:41:46 AM »
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.

ChuckHardin

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #11 on: January 26, 2012, 09:39:48 AM »
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.

ChuckHardin

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #12 on: January 26, 2012, 01:21:42 PM »
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

 

VELI555

  • Guest
Re: Both way link between Excel and AutoCad
« Reply #13 on: January 27, 2012, 01:24:20 AM »
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?

Matt__W

  • Seagull
  • Posts: 12954
  • I like my water diluted.
Re: Both way link between Excel and AutoCad
« Reply #14 on: January 27, 2012, 08:25:28 AM »
If you LIST any object in AutoCAD (using the standard LIST command) you'll see an entry for HANDLE.
Autodesk Expert Elite
Revit Subject Matter Expert (SME)
Owner/FAA sUAS Pilot @ http://skyviz.io