Extracting Data from Excel to CAD or CAD to Excel

[shivakumar]

Hi All,

This is shiva.

 

I assigned by one task and i am unable to get a correct information. Can any one please help me out how to do this. Task is below.

 

Q) A user will create a new AutoCAD drawing from the new templates and fill in the tiltle block. This information will automatically fill in the Excel sheet with appropriate infromation?

Actually need what ever the information (attributes) we fill in the title block (ex: drawn by, project name, checked by and title block dimensions) should be extract to excel.

I am using 2013 AutoCADLT and 2014 Electrical Autocad..

[steven-g]

Sorry I can't help directly at the moment, I have Autocad LT on my computer, but Excel is on the network and they cannot see each other, I think you will need to look at creating a data link and use fields within an Autocad table, but you may also need to use a macro to update things, I don't think LT has the option to completely do it automatically. The good news for me is that I get Excel installed locally this evening, so I might be able to offer more help later in the week, as this is something similar to what I am hoping to achieve, I just need to find out what is possible with LT. Good luck and let me know if you figure anything out.

[Tuns]

If I'm not mistaken you can link a spreadsheet to an attribute in AutoCAD and whatever you change in Excel will also change in AutoCAD, but I don't know if it works the other way around. Am I wrong here Steven or no?

[steven-g]

As I said not sure yet how far you can take LT. I use data extraction a lot, but that involves extracting to a text file and importing that into excel. I know you can also embed excel cells directly into LT, but because I have excel on a network I can't get at the finer points of datalinks. I know LT doesn't have the option to directly extract attributes to a table. So my guess would be that what Shiva is asking about would at best probably involve a few steps and not be totally automatic.

[Tuns]

I think there's some second party software you can download that does it for you.

[steven-g]

But what is the fun in that?

[Tuns]

True that! The challenge makes it more fun to see if it can be done on LT. I'd attempt but I've got too much work to get done currently. Once I'm done I'll start working on this as well.

[steven-g]

Yeah!! I now have excel, Booo!! first impression, not good, I created a datalink and it won't update reason "unknown", changes to excel update ok but changes to autocad table won't write out.

[Tuns]

I've tried myself as well... It says I need version 2003, 2007, or 2011 Excel for it to work... I have 2007... I don't get it..

[BIGAL]

Excel to autocad / opposite has been around for years and I do means years more than 10+ give up on AutoCAD LT and buy a alternative that has lisp capability and you can do it !!!!!

[Tuns]

Please, why give up when you can try to make it work? I'm stuck with LT because of the company I work for so I have no intention on giving up on it. This is an LT forum after all. It wouldn't be here if people just "gave up" on LT.

[steven-g]

I've tried myself as well... It says I need version 2003, 2007, or 2011 Excel for it to work... I have 2007... I don't get it..

Are both Autocad and Excel on your machine, the problem I had was that Excel was on a network, that we had to logon to, I was constantly switching and using ctrl+C and Ctrl+V to get anything done.

[steven-g]

Hi All,

This is shiva.

 

I assigned by one task and i am unable to get a correct information. Can any one please help me out how to do this. Task is below.

 

Q) A user will create a new AutoCAD drawing from the new templates and fill in the tiltle block. This information will automatically fill in the Excel sheet with appropriate infromation?

Actually need what ever the information (attributes) we fill in the title block (ex: drawn by, project name, checked by and title block dimensions) should be extract to excel.

I am using 2013 AutoCADLT and 2014 Electrical Autocad..

Sorry I didn't give a better response before, I was hoping that 'Datalink' would offer a more automated approach, but I don't think it will. You will need to setup a data extraction template with the information about the attributes you want to extract. It's possible to run a macro that will then extract these details into file that can be read by excel. Next you will need to either import that file into excel and copy and paste the information to the correct place in your spreadsheet, or if you are up to it you could use a macro in excel that would do it automatically.

So the things you want to look into are an attribute extraction template, a macro to extract the data, and then an excel macro to import the data. it can all be done manually and that would take a bit longer, but to be honest manually with the data extract command is very quick.

[Tuns]

Are both Autocad and Excel on your machine, the problem I had was that Excel was on a network, that we had to logon to, I was constantly switching and using ctrl+C and Ctrl+V to get anything done.

Yes both of them are on my computer and not on the network. I don't know what the problem could be.

[shivakumar]

Thanks a lot Steven for your replies. i tried with one attribute command "EATTEXT" which can extract the data from CAD to Excel but not fully. I think AutoCAD LT wont have functions a lot for typical concepts..

 

Shiva..

[steven-g]

Thanks a lot Steven for your replies. i tried with one attribute command "EATTEXT" which can extract the data from CAD to Excel but not fully. I think AutoCAD LT wont have functions a lot for typical concepts..

 

Shiva..

The command you need to use is ATTEXT, but it needs a template extraction template to work, LT doesn't suppoert EATTEXT

[Tyke]

I don't know if this helps here, but I have an Excel workbook from where I need to copy data from a series of cells into LT as MTEXT. I have a VBA macro for EXCEL (NOT LT) where I just need to set the cursor on one cell in the row of data I need and it reads the contents of each cell of interest, concatenates them together with the necessary line feed control characters and then copies that string to the clipboard. In LT I do a paste clip and the text is inserted on the current layer as MTEXT. If anybody is interested I would be willing to post the Excel macro.

[steven-g]

I don't know if this helps here, but I have an Excel workbook from where I need to copy data from a series of cells into LT as MTEXT. I have a VBA macro for EXCEL (NOT LT) where I just need to set the cursor on one cell in the row of data I need and it reads the contents of each cell of interest, concatenates them together with the necessary line feed control characters and then copies that string to the clipboard. In LT I do a paste clip and the text is inserted on the current layer as MTEXT. If anybody is interested I would be willing to post the Excel macro.

I'd be interested I use Excel VBA a lot, with LT to create scripts, for things like layer management, and also for attribute extraction.

[Tyke]

I'd be interested I use Excel VBA a lot, with LT to create scripts, for things like layer management, and also for attribute extraction.

 

OK steven, here's the VBA Code for you to insert into Excel. Under the menu item Tools > References you will need to add a reference to 'Microsoft Forms 2.0 Object Library' to get it to copy the string in the Data Object to the clipboard.

 

You place your cursor in the row in your Excel worksheet where your data is and then run the macro. It then copies the contents of cell C on the selected row into a string variable with a preceding label. It then moves to cell D appends a linefeed control character and also the contents of cell D. It continues on in a similar manner to cell H. The string variable is then assigned to the Data Object and that is copied to the clipboard. I placed the macro in an ActiveX control in the worksheet to optimise its use. In LT just click the Paste Clip button to dynamically insert the contents of the clipboard into your drawing. When you use Paste Clip it inserts the contents of the clipboard as MTEXT, IF it is in ASCII format, otherwise it will pate an OLE object. That's why I used 'Chr(10)' and not 'vbCr' as a linefeed, it would then have inserted an OLE object. Modify it to your needs and have fun.

 

Attribute VB_Name = "Data_Lne"
Sub CopyData()
Attribute CopyData.VB_ProcData.VB_Invoke_Func = " \n14"
'
'
   Dim sData As String
   Dim sRow  As String
   Dim sCol  As String
   Dim sCell As String
   Dim sACell As String
   Dim sARow  As String
   Dim DataObj As MSForms.DataObject
   Set DataObj = New MSForms.DataObject
   
'
   sData = ""
   sACell = ActiveCell.Cells.Address
   sARow = ActiveCell.Row
   sCol = "C": sRow = sARow
   sCell = sCol & sRow
   
   ' Plot number header
   Range(sCell).Select
   sData = "Plot number: "
   sData = sData & ActiveCell.FormulaR1C1
   
   ' Surname
   sCol = "D"
   sCell = sCol & sRow
   Range(sCell).Select
   sData = sData & Chr(10) & "Name: "
   sData = sData & ActiveCell.FormulaR1C1

   ' Vorname
   sCol = "E"
   sCell = sCol & sRow
   Range(sCell).Select
   If ActiveCell.FormulaR1C1 <> "" Then sData = sData & ", " & ActiveCell.FormulaR1C1
   
   ' Adress
   sCol = "F"
   sCell = sCol & sRow
   Range(sCell).Select
   sData = sData & Chr(10) & "Adress: "
   If ActiveCell.FormulaR1C1 <> "" Then sData = sData & ActiveCell.FormulaR1C1
   
   ' Town
   sCol = "G"
   sCell = sCol & sRow
   Range(sCell).Select
   sData = sData & Chr(10) & "Town: "
   If ActiveCell.FormulaR1C1 <> "" Then sData = sData & ActiveCell.FormulaR1C1
   
   ' Reference
   sCol = "H"
   sCell = sCol & sRow
   Range(sCell).Select
   sData = sData & Chr(10) & "Reference: "
   If ActiveCell.FormulaR1C1 <> "" Then sData = sData & ActiveCell.FormulaR1C1
   
   ' copy data to clipboard
   DataObj.SetText sData
   DataObj.PutInClipboard

End Sub

[steven-g]

Interesting approach, I think I will be able to adapt that for standard notes, It's easier to put the notes together in excel using formulas, pulling materials etc. from the present project. This way gets it straight to the clipboard without having to select text from the formula bar.