topic Re: Automation using excel sheet in AutoCAD Forum

Automation using excel sheet

Anonymous — Sun, 08 Sep 2019 18:35:17 GMT

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?

Re: Automation using excel sheet

TerryDotson — Sun, 08 Sep 2019 23:16:01 GMT

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.

Re: Automation using excel sheet

leeminardi — Mon, 09 Sep 2019 15:37:27 GMT

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.

Re: Automation using excel sheet

Anonymous — Sat, 28 Nov 2020 21:52:55 GMT

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.

Re: Automation using excel sheet

leeminardi — Sun, 29 Nov 2020 14:29:28 GMT

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.

Re: Automation using excel sheet

Anonymous — Sun, 29 Nov 2020 15:33:45 GMT

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.

Re: Automation using excel sheet

leeminardi — Mon, 30 Nov 2020 02:34:26 GMT

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.

Re: Automation using excel sheet

Anonymous — Thu, 03 Dec 2020 18:18:27 GMT

Worked like a charm. Thanks a lot.

Re: Automation using excel sheet

HomerMTY — Fri, 28 Feb 2025 18:36:14 GMT

Can the excel data be saved as dxf file?