Automation using excel sheet

[Anonymous]

Hello all,

Im looking for a way to automate my daily drawing routine. I was looking for a solution when i came up with excel and VBA. Basically what im trying to achieve is, based on user input to excel sheet, autocad will create the drawing. 

The drawing consist mostly from rectangles drawn at exact distances from each other see the attachment.

Is it possible to achieve the results im looking for with excel and VBA? If yes, are there any good resources where i can find in depth tutorials how to accomplish this task, all i could find is "Introduction to Visual Basic® for Applications for Autodesk® AutoCAD®" at autodesk pages.

Or is there any other way how i can automate this based on user input? 

[TerryDotson]

Or is there any other way how i can automate this based on user input?

For generating drawings based on parameters, ExcelDraw may be something to consider.

[leeminardi]

There are a number of options open to use Excel to generate an AutoCAD drawing including vlisp,  AutoCAD/VBA and Excel/VBA.

I find it easier to use Excel/VBA to generate a script than using AutoCAD/VBA.

Here is a sample that uses VBA/Excel to generate an AutoCAD script file.  Given the following data in Excel:

The following Excel/VBA macro will generate a script file for some of the elements in your drawing (I wasn't sure if the first window really had a different width than the rest so I assumed it didn't).

Sub main()
Dim nWin As Integer, widthWin As Double, heightWin As Double, dimOff As Double
Dim p1x As Double, p1y As Double, p2x As Double, p2y As Double
Dim dim1x As Double, dim1y As Double, dim2x As Double, dim2y As Double
Dim dim3x As Double, dim3y As Double, i As Integer
Dim msg As String
Dim fn_file
Application.ScreenUpdating = False
'get file name
fn_file = InputBox("Enter file name: ", , "Drawing-Data")
fn_file = fn_file & ".scr"
Open fn_file For Output As #2
' get window data
Range("b2:b2").Select
nWin = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
widthWin = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
heightWin = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
dimOff = ActiveCell.Value ' offset value for dim text
p1x = 0
p1y = 0
p2y = p1y + heightWin
dim1x = p1x
dim1y = p2y
' create rectangle statements for each window
For i = 1 To nWin
    p2x = p1x + widthWin
    msg = "rectang " & p1x & "," & p1y & " " & p2x & "," & p2y
    p1x = p2x
    Print #2, msg
Next i
' compute points for dimension line and add it
dim2x = p2x
dim3x = (dim1x + dim2x) / 2
dim3y = p2y + dimOff
msg = "dimlinear " & dim1x & "," & dim1y & " " & dim2x & "," & dim1y & " " & dim3x & "," & dim3y
Print #2, msg
Close #2
Application.ScreenUpdating = True
End Sub

Arrays could be used to store the coordinates of points which might make the coding more efficient but this should give you a feel for one way to generate a drawing from Excel data.

[Anonymous]

A year and a bit more I found this discussion useful. Feels like shot in the dark, but here it goes -

your comment and added file was wonderfully useful and easy to understand for a person who just now starts to search for easier life. 

But!
I have unsuccessfully searched what and how I have to edit your code to continue the pattern
 (information about different windows continuous in B, C, D... Q) and what would I need to change to add names to the parts?

If there is anyone - help. 

[leeminardi]

It is not clear what you want to put into the cells of columns B, C, D,...  The first thing to do is create a sample Excel file with the data you want to capture and how that relates to the data you want to contain in the AutoCAD script.

For the file in my post the data was all in a single column so the program selects the cell at the top of the column (B2) and then assigns the contents of that cell to the variable nWin. The statement ActiveCell.Offset(1, 0).Select selects the cell 1 row down and 0 cells across from the currently selected cell and assigns its contents to widthWin. The process is repeated going down a row at a time.  If you want to get values from the Excel worksheet by going across a row and down you could create a loop (For or While statement) to step across a row, column by column, and then step down a row to get more data column by column.  Again, you should start with the layout of your data in Excel.

[Anonymous]

Thanks for taking time replaying. I do not know the best keywords to find information about this specific VBA AutoCad deal.. yet. So research done today and your replay now and before helps a great deal. 
The idea is to make script from excel table what would look in the end with rectangle shape and it's label as shown in the sketch below. 

[leeminardi]

I think the attached Excel file with VBA program will do what you want, or close to it. 

Give a worksheet that looks like this:

It will generate a script that looks like this:

Here's the result in AutoCAD:

Here's the code:

Sub main()
' Creates AutoCAD script to make window drawings
'  LRM  12/29/2020
'
Dim nWin As Integer, widthWin As Double, heightWin As Double, dimOff As Double
Dim p1x As Double, p1y As Double, p2x As Double, p2y As Double
Dim dim1x As Double, dim1y As Double, dim2x As Double, dim2y As Double
Dim dim3x As Double, dim3y As Double, i As Integer
Dim msg As String
Dim fn_file
Application.ScreenUpdating = False
'get file name
fn_file = InputBox("Enter file name: ", , "Drawing-Data")
fn_file = fn_file & ".scr"
Open fn_file For Output As #2
' get window data
Range("b2:b2").Select
msg = ""
p1x = 0
p1y = 0
deltax = 500 ' distance between windows
labely = -200 ' distance label is below window

While ActiveCell.Value <> ""
    nWin = ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
    widthWin = ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
    heightWin = ActiveCell.Value
    ActiveCell.Offset(1, 0).Select
    dimOff = ActiveCell.Value ' offset value for dim text
    ActiveCell.Offset(1, 0).Select
    glasslabel = ActiveCell.Value
    p2y = p1y + heightWin
    dim1x = p1x
    dim1y = p2y
    ' create rectangle statements for each window
    For i = 1 To nWin
        p2x = p1x + widthWin
        msg = "rectang " & p1x & "," & p1y & " " & p2x & "," & p2y
        p1x = p2x
        Print #2, msg
    Next i
    ' compute point for horizontal dimension line and add it
    dim2x = p2x
    dim3x = (dim1x + dim2x) / 2
    dim3y = p2y + dimOff
    msg = "dimlinear " & dim1x & "," & dim1y & " " & dim2x & "," & dim1y & " " & dim3x & "," & dim3y
    Print #2, msg
    ' compute point for vertical dimension line and add it
    dim4x = p2x
    dim5x = p2x + dimOff
    dim5y = (dim1y + dim2y) / 2
    msg = "dimlinear " & dim4x & "," & dim1y & " " & dim4x & "," & dim2y & " " & dim5x & "," & dim5y
    Print #2, msg
    ylabel = p1y - 4 * dimOff
    ' create labe text
    msg = "(command " & Chr(34) & "_.TEXT" & Chr(34) & " " & Chr(34) & dim1x & "," & ylabel & Chr(34) & " " & _
       Chr(34) & Chr(34) & " " & Chr(34) & Chr(34) & " " & Chr(34) & glasslabel & Chr(34) & ")"
    Print #2, msg
    ActiveCell.Offset(-4, 1).Select
    p1x = p1x + deltax
Wend
Close #2
Application.ScreenUpdating = True
End Sub

I had a hard time getting the text command to work.  There is probably a better way.  I used Chr to create quotes that are needed by the VLISP statement to avoid confusion with the VBA processing of quotes for text.

Be sure to set text height and dimension style size accordingly before running the script.

[Anonymous]

Worked like a charm. Thanks a lot.

[HomerMTY]

Can the excel data be saved as dxf file?