PDA

View Full Version : Read excel cell values from autocad (excel 2007)



pmav99
08-23-2009, 11:15 AM
Hi everybody

What i want to do is to take some values from various excel cells and use them to make a drawing in autocad through autocad VBA. Is this possible? Are there going to be problems with the various versions of excel/autocad? I am using excel 2007 but i' d like the macro to run for excel 2003 also

thanks in advance
P.

mdmackillop
08-24-2009, 07:10 AM
This a a script creation file I made up a few years back for Excel to Autocad. Hopefully it will give you the basics. The file will be created in C:\AAA\

pmav99
08-24-2009, 08:57 AM
dear mdmackillop

Thank you for your answer. I 've been doing some research since yesterday and i think i 've found a solution. But firstly i 'll comment your file.

The script file doesn't work although i think i got the basic idea. When i select a name (cell with blue color) and click the button i get the message "Script file creation failed" and obviously the script file is not created. I am not sure what is going wrong.

There is a name "adj2" with a reference error but i don't know if this is causing any problems.

Your approach is intresting but I used a different one. Perhaps what you tried to do would be easier if you used AutoLisp. Then again maybe not. I am not an expert :(. Anyway this is what i did:

(in case there any misunderstandings this is autocad VBA)
Option Explicit

Dim ExcelApp As Object
Dim mspace As AcadModelSpace

Function ExcelConnect()
' This function connects excel with autocad

On Error Resume Next

Set ExcelApp = GetObject(, "Excel.Application")
If Err Then
Err.Clear
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True
If Err Then
MsgBox Err.Description
Exit Function
End If
End If

End Function

Function ExcelClose()
' This function closes the connection between excel ana Autocad
Set ExcelApp = Nothing
End Function

Function GetCellValue(row As Integer, column As Integer) As Variant
' This function returns the value of a given cell in excel.
GetCellValue = ExcelApp.ActiveSheet.Cells(row, column).value
End Function

Sub ExcelToAcad()

' declaration of variables
Dim A(2) As Double
Dim B(2) As Double
Dim line1 As AcadLine
Dim circle1 As AcadCircle

ExcelConnect

' giving cell values to points A (from cells C5/D5) and point B (from cells C6/D6)
' The default value for the z coordinate is 0 (drawing in xy plane)
A(0) = GetCellValue(5, 3) ' Ax
A(1) = GetCellValue(5, 4) ' Ay
B(0) = GetCellValue(6, 3) ' Bx
B(1) = GetCellValue(6, 4) ' By

Set mspace = ThisDrawing.ModelSpace

' creating a line from A to B
Set line1 = mspace.AddLine(A, B)

' Creating a circle with center at point A and a diameter of 0.2 drawing units
Set circle1 = mspace.AddCircle(A, 0.2)

' Regenerating the active Viewport
ThisDrawing.Regen (acActiveViewport)

' Closing the excel-autocad link
ExcelClose
End Sub

If you have any suggestions i will be happy to hear them.
Also does anybody know if there are going to be any problems with the various excel/autocad versions?
ty

mdmackillop
08-24-2009, 11:40 AM
Hi
You need a folder C:\AAA or the code will fail. This path can be changed in the code if required.
I needed this for a single purpose only, which now I no longer use
Regards
MD

Tommy
08-25-2009, 05:40 AM
Hi pmav99,

I would suggest that you change the data type for GetCellValue from variant to double. Otherwise you may get strange results since you are not type casting back and forth. I dont' think there will be any problem between the versions as long as you are using VBA.

pmav99
08-28-2009, 03:17 AM
@mdmackillop

Yes you are right

@Tommy

I set the type as variant in order to use the function both for numbers and strings, but i must admit that you are right. I' ll propably make 2 functions, one for each type (numbers, strings).

efz
03-03-2010, 02:47 PM
lol its sick what you can do!

noyjoreb
03-29-2010, 07:52 AM
Option Explicit

Dim ExcelApp As Object
Dim mspace As AcadModelSpace

Function ExcelConnect()
' This function connects excel with autocad

On Error Resume Next

Set ExcelApp = GetObject(, "Excel.Application")
If Err Then
Err.Clear
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True
If Err Then
MsgBox Err.Description
Exit Function
End If
End If

End Function

Function ExcelClose()
' This function closes the connection between excel ana Autocad
Set ExcelApp = Nothing
End Function

Function GetCellValue(row As Integer, column As Integer) As Variant
' This function returns the value of a given cell in excel.
GetCellValue = ExcelApp.ActiveSheet.Cells(row, column).value
End Function

Sub ExcelToAcad()

' declaration of variables
Dim A(2) As Double
Dim B(2) As Double
Dim line1 As AcadLine
Dim circle1 As AcadCircle

ExcelConnect

' giving cell values to points A (from cells C5/D5) and point B (from cells C6/D6)
' The default value for the z coordinate is 0 (drawing in xy plane)
A(0) = GetCellValue(5, 3) ' Ax
A(1) = GetCellValue(5, 4) ' Ay
B(0) = GetCellValue(6, 3) ' Bx
B(1) = GetCellValue(6, 4) ' By

Set mspace = ThisDrawing.ModelSpace

' creating a line from A to B
Set line1 = mspace.AddLine(A, B)

' Creating a circle with center at point A and a diameter of 0.2 drawing units
Set circle1 = mspace.AddCircle(A, 0.2)

' Regenerating the active Viewport
ThisDrawing.Regen (acActiveViewport)

' Closing the excel-autocad link
ExcelClose
End Sub


anybody can help about this code? im trying to copy and paste to autocad vba but not running... any suggestion plssss... ty

arash2113
12-09-2012, 01:43 AM
Option Explicit

Dim ExcelApp As Object
Dim mspace As AcadModelSpace

Function ExcelConnect()
' This function connects excel with autocad

On Error Resume Next

Set ExcelApp = GetObject(, "Excel.Application")
If Err Then
Err.Clear
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = True
If Err Then
MsgBox Err.Description
Exit Function
End If
End If

End Function

Function ExcelClose()
' This function closes the connection between excel ana Autocad
Set ExcelApp = Nothing
End Function

Function GetCellValue(row As Integer, column As Integer) As Variant
' This function returns the value of a given cell in excel.
GetCellValue = ExcelApp.ActiveSheet.Cells(row, column).value
End Function

Sub ExcelToAcad()

' declaration of variables
Dim A(2) As Double
Dim B(2) As Double
Dim line1 As AcadLine
Dim circle1 As AcadCircle

ExcelConnect

' giving cell values to points A (from cells C5/D5) and point B (from cells C6/D6)
' The default value for the z coordinate is 0 (drawing in xy plane)
A(0) = GetCellValue(5, 3) ' Ax
A(1) = GetCellValue(5, 4) ' Ay
B(0) = GetCellValue(6, 3) ' Bx
B(1) = GetCellValue(6, 4) ' By

Set mspace = ThisDrawing.ModelSpace

' creating a line from A to B
Set line1 = mspace.AddLine(A, B)

' Creating a circle with center at point A and a diameter of 0.2 drawing units
Set circle1 = mspace.AddCircle(A, 0.2)

' Regenerating the active Viewport
ThisDrawing.Regen (acActiveViewport)

' Closing the excel-autocad link
ExcelClose
End Sub


anybody can help about this code? im trying to copy and paste to autocad vba but not running... any suggestion plssss... ty


Dear sir
The problem of this code is in line 3 and about the definition of the variable MSSPACE because this name is owns to one of the AutoCAD command & AutoCAD cannot accept it as a variable so you can change the name and all of the variable’s name that used in this code… then you can enjoy this code…
GOODLUCK