importing excel variables into AutoCAD

singoi · Mar 1, 2005

Hallo,

I wanna import the variables from excel table to autocad drawing. for example :

i have a drawing which has different dimensions which are been identified as L1, L2 and R1. in excel i wanna give these values for different types of models where in these values changes with a particular identification number. so for each different identification number will have its own particular values for these variables.

i wanna click on particular model and automatically the variables in the drawing should be changed acc. to the model.

please can any one help me how to make up this problem.

singoi

TCARPENTER · Mar 1, 2005

Hi singoi,

Here's a template of how I integrate Excel as pseudo database:

First, set a reference to "Microsoft Excel X.X Object Library" where X.X is your version of Excel/Office.

Next my Excel Globals in the General area of your module:

Code:

Public ExcelApp As Excel.Application
Public objWrkBk As Excel.Workbook
Public objWrkSht As Excel.Worksheet
Public objRng As Excel.Range
Public objCll As Excel.Range

Next, the portion I use to start Excel:

Code:

Public Sub InitializeExcel()
  
  On Error Resume Next
  
  ' Inititialize Excel
  '
  Set ExcelApp = GetObject(, "Excel.Application")
  
  If Err <> 0 Then
    Err.Clear
    Set ExcelApp = CreateObject("Excel.Application")
        
    If Err <> 0 Then
      MsgBox "Could not load Excel.", vbExclamation
      End
    End If
  End If
  
  On Error GoTo 0
  
End Sub

Finally, the portion that reads the values from the Excel worksheet:

Code:

Public Sub GetValues()
  
  InitializeExcel
  
  Set objWrkBk = ExcelApp.ActiveWorkbook
  Set objWrkSht = objWrkBk.ActiveSheet
  Set objRng = objWrkSht.Range("A1").CurrentRegion
  
  For Each objCll In objRng
    ....
  Next objCll
  
End Sub

i wanna click on particular model and automatically the variables in the drawing should be changed acc. to the model.

I'm not quite sure what you mean by this, if you mean clicking on the geometry then you'll need to write a reactor for your drawings objects in AutoLISP, and I'm sorry I can't tell you how to do this.

HTH
Todd

singoi · Mar 1, 2005

Hallo, thanks for your valuable answer.

i will explain you now more clearly.

all the variable parameters will be given in excel table.
example:

a irregular .dwg drawing with dimension
A1,A2,A3,A4,R1 where in the type is IT-100
and similarly there are various types which are denoted as IT-xxx having different values for these dimensions.

after editing all the values in their particular types .. when we need a particular type suppose IT-450 with its values which are given in excel table we will make a small text box where we will type IT-450 and a command button which should execute the same drawing with these values which we have given in excel table.

so this button is supposed to get linked between excel( test.xls) and autoCAD drawing ( test.dwg). after clicking this button this test.dwg should be executed with the same drawing which is drawn but with the different dimensions of A1,A2,A3,A4,R1.

Hope you have understood the problem.

i am really thankful to you for helping me.

singoi

TCARPENTER · Mar 1, 2005

Hi singoi,

Who's driving who? Is your operator in Excel or AutoCAD? or both?

Todd

singoi · Mar 1, 2005

Hallo Todd,

Excel will be backend where we have our values and the front end is autoCAD where in we do the drawings. As the drawings are always same but the dimensions vary to each type .. i wanna make this possible to keep up the time factor in consideration.

the values will be entered in excel cause the entry is very easy, but there should be a command button and a text box which will be programmed..as we click the button the type which is entered in the text box have to be executed.

for example . TYPE 140

this should call the drawing test.dwg with the values of TYPE 140.

singoi.

TCARPENTER · Mar 1, 2005

Hi singoi,

Ok, I think I understand.

You want to press a button in Excel, and have it open a drawing in AutoCAD and adjust the drawing to the proper dimensions.

I would still do it the way I first gave you. After entering the data, I would go back to AutoCAD and then run the routine from AutoCAD.

Whether you do it from Excel or AutoCAD, the routine will still have to loop through your entire sheet until it finds the requested IT-XXX item. I would just modify the GetValues routine to look something like this:

Code:

Public Sub GetValues()
  
  Dim strItemNo as string

  InitializeExcel
  
  strItemNo = InputBox("Enter IT number...")

  Set objWrkBk = ExcelApp.ActiveWorkbook
  Set objWrkSht = objWrkBk.ActiveSheet
  Set objRng = objWrkSht.Range("A1").CurrentRegion
  
  For Each objCll In objRng
    If objCll.Value = strItemNo Then
      ' Found the right row, process it.
      ...
    Else
      ' Couldn't find the requested IT No.
      MsgBox "Could find the requested IT number."
      Exit Sub
    End If
  Next objCll 
End Sub

If you need to do it from Excel, then from Excel, you'll need to add a reference to AutoCAD 200x Type Library and then use this bit of routine to connect:

Code:

Public Sub InitializeAutoCAD()
  '
  ' *** InitializeAutoCAD ***
  '
  ' This routine is used to connect or start and then
  ' connect with AutoCAD.
  '
  On Error Resume Next
    
  Set AcadApp = GetObject(, "AutoCAD.Application")
  AcadApp.Visible = True
  If Err Then
    ...Error Handling here...
  Else
    Set AcadDoc = AcadApp.ActiveDocument
   
  End If
        
End Sub

HTH
Todd

singoi · Mar 2, 2005

Hi todd,

thank you very much,

I have created a form with a text box and a command box.

then in excel table i have created a table with few types which i have already mentioned as L1, L2, L3, L4 for various types LT-10, LT-20, LT-30

this excel table is saved as test.xls and the form in VB is also saved as usual.

now i wanna connect this command button in VB to Autocad such that as soon as i give LT-10 in this text box and click the button the Autocad drawing should be executed with the values which are been saved in excel table for LT-10.

how to initiate this in visualbasic.

i am having little bit knowledge in visual basic but not complete.

singoi.

TCARPENTER · Mar 2, 2005

Hi singoi,

Is this VB6, Excel VBA, or AutoCAD VBA?

Todd

singoi · Mar 2, 2005

Hallo Todd,

am i troubling you with my questions?

well , this is a problem where in i am supposed to link excel and autocad from vb6. VB6 will be front end and the data will be entered in excel and the output will be in AutoCAD.

in autoCAD drawing there will be these texts L1,L2,L3,L4 and in excel these values are entered for various types. In Front End VB6 we will select the type---then clicking the command button,it should read the types in excel and when the both types matches, then the values of corresponding type should be written in AutoCAD drawing in corresponding Lx where x=1,2,3,4.

I hope this is complicated ones. isnt it Todd.

TCARPENTER · Mar 3, 2005

Hi singoi,

You're not troubling me at all. Now that I know where your code resides, I can be of a little more assistance.

In your code, you will need to add a reference to BOTH AutoCAD and Microsoft Excel. Then you will need to incoroporate BOTH subroutines I gave you: the InitializeExcel, and the InitializeAutoCAD routines.

If you're still unsure about this, post what code you do have and I'll fill in the blanks for you.

Todd

singoi · Mar 3, 2005

Hi todd,

i have created a VB form where in there is one text box and a command box.

in the form command window i have written the following code.

Public ExcelApp As Excel.Application
Public objwrkbk As Excel.Workbook
Public objwrksht As Excel.Worksheet
Public objrng As Excel.Range
Public objcll As Excel.Range

Private Sub Form_Load()

Form1.Show

End Sub

Public Sub initializeExcel()

On Error Resume Next

' Initialize Excel

Set ExcelApp = GetObject(, "Excel.Application")

If Err <> 0 Then
Err.Clear
Set ExcelApp = CreateObject(" Excel.application")

If Err <> 0 Then
MsgBox " Could not Load Excel.", vbExclamation
End
End If
End If

On Error GoTo 0

End Sub

Public Sub InitializeAutoCAD()

'
' *** Initialize AutoCAD ***
'
' This routine is used to connect or start and then connect with autoCAD.
'

On Error Resume Next

Set AcadApp = GetObject(, "AutoCAD.Application ")
AcadApp.Visible = True

If Err Then
Error Handling
Else

Set AcadDoc = AcadApp.ActiveDocument

End If

End Sub

this is the code i have written .. when i type AcadApp. the box with different possibilities does not open..i was supposed to type Application. is it right?
when i type excel. the opens a list box immediately where in i select application from the list box. why is it not with AcadApp. ... do i need to load any drivers?

now how should i coonect this form to excel and autocad

the excel sheet is named as Test.xls and autocad drawing saved as Test.dwg in particular path.

i hope i can complete this task with your help.

thank you Todd.

singoi

TCARPENTER · Mar 3, 2005

Hi singoi,

Here is a skeleton of what you'll need to connect Excel and AutoCAD. The first section of code is in a regular module, and I have broken them down into simple routines to hopefully keep it simple so you can see what's happening where.

You have two problems with your AcadApp variable, first, you need to dimension it. Second, yes you need a driver. Under Project, then References, look for a reference to AutoCAD 2000 Type Library - NOT AutoCAD/OjbectDBX Common Object Library these are different beasts. Depending on what version of AutoCAD you have, this may read slightly different, but should be close enough so you can find it.

Just copy and paste this first section into a module.

Code:

' Public variables this routine will need.
Public ExcelApp As Excel.Application  'Excel app.
Public objWrkBk As Excel.Workbook     'Workbook (test.xls).
Public objWrkSht As Excel.Worksheet   'Worksheet (Sheet1).
Public objRng As Excel.Range          'All cells on Sheet1.
Public objCll As Excel.Range          'Each cell in objRng.
Public AcadApp As AcadApplication     'AutoCAD app.
Public AcadDoc As AcadDocument        'AutoCAD drawing (test.dwg)

Private Sub Form_Load()
  Form1.Show
End Sub

Public Sub InitializeExcel()
  
  '
  ' *** Initialize Excel ***
  '
  ' This routine is used to connect or start and then connect with Excel.
  '
  On Error Resume Next

  Set ExcelApp = GetObject(, "Excel.Application")

  If Err <> 0 Then
    Err.Clear
    Set ExcelApp = CreateObject(" Excel.application")
    
    If Err <> 0 Then
      MsgBox " Could not Load Excel.", vbExclamation
      End
    End If
  End If

  On Error GoTo 0

End Sub

Public Sub InitializeAutoCAD()
  '
  ' *** Initialize AutoCAD ***
  '
  ' This routine is used to connect or start and then connect with AutoCAD.
  '
  
  On Error Resume Next
  
  Set AcadApp = GetObject(, "AutoCAD.Application ")
  AcadApp.Visible = True
  
  If Err Then
    MsgBox "Error: " & Err.Description
    End
  End If
    
End Sub

Public Function OpenWorkSheet() As Boolean
  '
  ' *** OpenWorkSheet ***
  '
  ' This routine is used to open the specified Excel spreadsheet.
  '
  
  On Error GoTo OpenWorkSheet_Error
  
  Set objWrkBk = ExcelApp.Workbooks.Open("Test.xls")
  Set objWrkSht = objWrkBk.Sheets("Sheet1")
  Set objRng = objWrkSht.Range("A1").CurrentRegion
  
OpenWorkSheet_Exit:
  OpenWorkSheet = True
  Exit Function
  
OpenWorkSheet_Error:
  OpenWorkSheet = False
  
End Function

Public Sub ProcessDrawing(L1 As Double, L2 As Double, L3 As Double, L4 As Double)

  Set AcadDoc = AcadApp.Documents.Open("test.dwg")
  
  '... Here you'll plug in your entity manipulation code...

End Sub

In this last portion, you'll need to plug in your code to either manipulate your geometry or fill in your dimension text (however you plan to tackle this problem).

This next portion is the code behind the command button. On my form, I named the text box txtSearch. Just copy and paste this section into the click event of your command button.

Code:

Private Sub Command1_Click()
  Dim strSearch As String
  Dim L1, L2, L3, L4 As Double
   
  InitializeExcel
  
  InitializeAutoCAD
  
  ' Search Excel for the value listed in the Text box
  '
  strSearch = Form1.txtSearch
  
  ' Make sure the test.xls file can be loaded.
  '
  If OpenWorkSheet Then
  
    For Each objCll In objRng
      If objCll.Value = strSearch Then
        ' Found the requested item number.
        '
        Exit For
      End If
    Next objCll
    
    ' Load the required dimensions.
    '
    L1 = objCll.Offset(0, 1).Value
    L2 = objCll.Offset(0, 2).Value
    L3 = objCll.Offset(0, 3).Value
    L4 = objCll.Offset(0, 4).Value
    
    ProcessDrawing L1, L2, L3, L4
  Else
    MsgBox "OpenWorksheet failed"
  End If
End Sub

HTH
Todd

singoi · Mar 3, 2005

Hi Todd,

just now i have tried this as you have told.

still i am getting a problem.

compile error: ByRef argument type mismatch

this is the error coming and placing the rror exactly at " ProcessDrawing L1, L2, L3, L4 ...placing exactly on L1

is there any way that i send u my files in zip format todd.

singoi

TCARPENTER · Mar 3, 2005

Hi singoi,

Sure send them to edgi3ATjunoDOTcom. Just correct the address to look like a real email address.

Todd

braves9822 · Nov 9, 2005

Singoi,

I am new to VBA. I work in AutoCAD. I draw items that have bill of material information and dimensions and other information on the drawings. i have a program called Excellink that will export and import the block attributes from AutoCAD into Excel and from Excel into AuotCAD. The program will only import one at a time. I am trying to see if a VBA can help me out. I want to be able to run the VBA or Lisp to ask me what rows in excel that i want to import and have it open a file from a column, import the data into the block that is on the spreadsheet and save and close the AutoCAD file as one of the cells. I can send you the excel and AutoCAD file so you can see what i am trying to do. I can also send the VBA that I have started to see if I am going in the right direction.

Thank you,
Scot Champagne
The Shaw Group

singoi · Nov 22, 2005

Hallo Scot,

i am interested in your response..Please can you send me the files as i want to go through it.

jeevan dot singavarapu at gmail dot com

Thank you

Singoi

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

importing excel variables into AutoCAD

singoi

Technical User

TCARPENTER

Programmer

singoi

Technical User

TCARPENTER

Programmer

singoi

Technical User

TCARPENTER

Programmer

singoi

Technical User

TCARPENTER

Programmer

singoi

Technical User

TCARPENTER

Programmer

singoi

Technical User

TCARPENTER

Programmer

singoi

Technical User

TCARPENTER

Programmer

braves9822

Technical User

singoi

Technical User

Similar threads

Part and Inventory Search

Sponsor