topic Re: Excell to Cad (2007 Autocad and Excel 2000) in VBA Forum

Excell to Cad (2007 Autocad and Excel 2000)

Anonymous — Fri, 23 Jan 2009 23:54:39 GMT

I'm trying to automate the insertion of details into AutoCAD based on user
input in a form.

I have recently created new details that cover the most sold options for our
buildings. I got tired of having only a handful of generic details, and
having to modify them the same every time. So now I have 72 options for a
roof eave detail. I have all the details listed in an excel spreadsheet (see
attached). I then created a lisp routine, to where the user only had to
enter in the detail number and pick a certain point in the drawing. I have a
template set up that has circles for the insertion points of the details. So
when the details are inserted, at these points, they are displayed properly
in the sheets.

This works real slick, but then I got to thinking I could make this even
easier. Why insert one detail at a time when I could enter all at once. I
thought if I could create a box that the user would just pick options and
click a button, then have all the correct details inserted. Then I thought
why stop at inserting details. Why not have it also fill in some of the
construction notes. Then I kept thinking of other things that could be
automated by this same form. I realized if I was going to accomplish any of
this, I had to focus on completing one task at a time. So I am going to
focus on inserting details.

I know very little vba and just basic lisp. I have read some tutorials on
vba with regards to having AutoCAD and Excel communicating with each other.
And have gone thru some tutorials dealing with just vba basics. I have also
modified vba, that someone else has created, to meet my needs

I have created the userform with everything that I need, then I got stuck.
I'm drawing a blank as to where to start with the code.
I know that after the user has picked all the options, they will then click
the execute button. From there vba needs to go to the excel spreadsheet,
with all the details listed, and sort through to find the right detail
number. Then either something has to create a list of all the right details
or have it insert one at a time.

Can this even be done with what I have set up far, or do I need to go a
different route.
I know I need to go thru some more tutorials. Anybody know of ones that are
geared toward what I am trying to accomplish?
Thanks for any help.

Re: Excell to Cad (2007 Autocad and Excel 2000)

Anonymous — Sat, 24 Jan 2009 01:24:54 GMT

Hi Larry,

In the VBAIDE if you double click on the Execute button it will create a
Sub routine like;



Sub ButtonName_Click ()



End Sub

Any code you put inside the Sub will be executed when you click on the
button when running your program.

To see this happening you can add a line as shown.



Sub ButtonName_Click ()

   MsgBox "I just clicked on my button"

End Sub

What I suggest you do is to write a series of Functions which you call
from this Sub. That way you can work on one item at a time.



Sub ButtonName_Click ()

   Me.Hide  '<-- Hide the form while you are working

   CreateLinkToExcel

   GetRoofShapeDataFromExcel

   PlotRoofShapeData

   etc

   CloseLinkToExcel

   Me.Show  '<-- Show the form again if needed		

End Sub

Function CreateLinkToExcel ()



End Function



Function GetRoofShapeDataFromExcel ()



End Function



Function PlotRoofShapeData()



End Function



etc.

Don't be frightened to create lots of Functions. It is much easier to
test and debug lots of small functions rather than a smaller number of
large ones. Only rarely should any function exceed the amount of code
you can view on the screen without scrolling

Also, if you look in the Help files you will find code to do nearly all
the small functions you need, except that the code is formatted to show
the outcome of an action and you need to modify it to do as you want.

Lastly, unlike Lisp, you should put "Option Explicit" as the top line in
each module and form code set. This forces you to declare variables
before using them and makes debugging easier.

It helps you to catch typos if you declare variables like this;

Dim sExcelFileName as String

When you type it everywhere else - type in lower case and when the
cursor leaves the line it will change the case to match the declaration
- if you haven't made a typo.

Regards

Laurie Comerford

Larry wrote:
> I'm trying to automate the insertion of details into AutoCAD based on user
> input in a form.
>
> I have recently created new details that cover the most sold options for our
> buildings. I got tired of having only a handful of generic details, and
> having to modify them the same every time. So now I have 72 options for a
> roof eave detail. I have all the details listed in an excel spreadsheet (see
> attached). I then created a lisp routine, to where the user only had to
> enter in the detail number and pick a certain point in the drawing. I have a
> template set up that has circles for the insertion points of the details. So
> when the details are inserted, at these points, they are displayed properly
> in the sheets.
>
> This works real slick, but then I got to thinking I could make this even
> easier. Why insert one detail at a time when I could enter all at once. I
> thought if I could create a box that the user would just pick options and
> click a button, then have all the correct details inserted. Then I thought
> why stop at inserting details. Why not have it also fill in some of the
> construction notes. Then I kept thinking of other things that could be
> automated by this same form. I realized if I was going to accomplish any of
> this, I had to focus on completing one task at a time. So I am going to
> focus on inserting details.
>
> I know very little vba and just basic lisp. I have read some tutorials on
> vba with regards to having AutoCAD and Excel communicating with each other.
> And have gone thru some tutorials dealing with just vba basics. I have also
> modified vba, that someone else has created, to meet my needs
>
> I have created the userform with everything that I need, then I got stuck.
> I'm drawing a blank as to where to start with the code.
> I know that after the user has picked all the options, they will then click
> the execute button. From there vba needs to go to the excel spreadsheet,
> with all the details listed, and sort through to find the right detail
> number. Then either something has to create a list of all the right details
> or have it insert one at a time.
>
> Can this even be done with what I have set up far, or do I need to go a
> different route.
> I know I need to go thru some more tutorials. Anybody know of ones that are
> geared toward what I am trying to accomplish?
> Thanks for any help.
>
>
> ------------------------------------------------------------------------
>
>
> ------------------------------------------------------------------------
>

Re: Excell to Cad (2007 Autocad and Excel 2000)

Anonymous — Sat, 24 Jan 2009 03:47:15 GMT

Hello,

After learning the basic basics like variable types, for next, if then, etc., modifying examples and other code to meet your needs is a good way to learn. Start out basic and expand and refine. Here is a sample that shows autoCad connecting to an open instance of Excel, could be refined to a certain filename...

{code}
Option Explicit

Sub xl2acad()

Dim texthere(0 To 500) As Variant
Dim str As String
Dim XL As Object
Dim xlSheet As Object
Dim xlcurcell As Excel.Range
Dim thisSpace As Integer
thisSpace = ThisDrawing.ActiveSpace
'Dim texthere As AcadText
Set XL = GetObject(, "Excel.Application")
Set xlSheet = XL.ActiveWorkbook.ActiveSheet
'MsgBox xl.ActiveCell.Value
xlSheet.UsedRange.Select
'For Each xlcurcell In xl.ActiveCell.CurrentRegion.Cells
'MsgBox xlcurcell
'Next xlcurcell
Dim dblPt As Variant
Dim dblPtOrig As Variant
Dim curCol As Integer
Dim curRow As Integer
Dim textalign As Integer
curCol = 0: curRow = 0
Dim x As Integer
Dim textalignpt(0 To 2) As Double
textalign = 0
dblPt = ThisDrawing.Utility.GetPoint(, vbCr & "Insertion point: ")
dblPtOrig = dblPt
x = 0
For Each xlcurcell In xlSheet.UsedRange
'MsgBox xlcurcell
If curCol <> 0 And curCol <> xlcurcell.Column Then ' if column change
dblPt(0) = dblPt(0) + xlcurcell.ColumnWidth
If xlcurcell.HorizontalAlignment = xlHAlignCenter Then
textalignpt(0) = dblPt(0) + xlcurcell.ColumnWidth / 2
textalignpt(1) = dblPt(1)
textalignpt(2) = 0#
' dblPt(0) = dblPt(0) + xlcurcell.ColumnWidth / 2
textalign = acHorizontalAlignmentCenter
Else
textalign = acHorizontalAlignmentLeft

End If
End If
If curRow <> 0 And curRow <> xlcurcell.Row Then ' if row change
dblPt(1) = dblPt(1) - xlcurcell.RowHeight / 9
dblPt(0) = dblPtOrig(0)
End If

If xlcurcell.ColumnWidth <> 0 Then
If thisSpace = 1 Then
Set texthere(x) = ThisDrawing.ModelSpace.AddText(xlcurcell.text, dblPt, 1)
Else
Set texthere(x) = ThisDrawing.PaperSpace.AddText(xlcurcell.text, dblPt, 1)
End If
End If
curCol = xlcurcell.Column
curRow = xlcurcell.Row
If textalign <> 0 Then
texthere(x).HorizontalAlignment = textalign
texthere(x).TextAlignmentPoint = textalignpt
End If
texthere(x).Update
Next xlcurcell
End Sub
{code}

In the VBA coding environment (in case you don't know) you can highlight objects and methods and properties and press F1. When I'm working with both Excel and autoCad, I like to keep a dummy copy of excel VBA IDE open (Tools Macros VBA Editor) and copy/paste for easier F1 help access.

Another useful tool to understand what is going on in your program is to have View Locals Window on w/ Breakpoints set at useful spots, either click in the left code margin or right-click Toggle Breakpoint.

If i were doing this i'd have the excel sheet in one table, or maybe 4 (i.e. floors, roofs, walls, hvac), and have them in named ranges. A possible shortcut would be to concatenate all of the columns together, except the blockid, in excel in a hidden column (dealing with the inch(") symbol somehow). The form could collect the results and concatenate them and then do a Vlookup from within the program on the excel spreadsheet looking for the blockid and then insert the block.

anyway... hope its useful.
cadger

Re: Excell to Cad (2007 Autocad and Excel 2000)

Anonymous — Tue, 27 Jan 2009 18:24:48 GMT

Boy this is a lot of fun. I have been playing
around with a bunch of little codes to see what they do.

So far I have been able to open a certain Excel
file and set a certain worksheet as current.

<code>

Private Sub CommandButton3_Click()
Dim
xlApp As Excel.Application
Dim xlBook As Workbook
Dim
xlSheet As Worksheet

Set xlApp =
CreateObject("Excel.Application")
Set xlBook =
xlApp.Workbooks.Open("c:\Projects\misc\DETAILS\Detail-Number-List.xls")

Set xlSheet = Worksheets("Detail2")
Sheets("Detail2").Select

xlApp.Visible = True
End Sub

</code>

Also figured out how to open the file without
having it displayed. Have been able to take the optionbutton true value and turn
it into text that can be inserted into excel for searching.

<code>

Public Sub obFLRim2x4_Click()
Dim
FLRim2x4 As String
If obFLRim2x4.Value = True
Then
FLRim2x4 = "2 x 4"
End If
End
Sub

</code>

Have went and created separate worksheets in the
workbook. That way each detail has its own sheet.

Next I have to figure out how to make Excel search
using the values that I have. I have a vba code for using the autofilter
function, but haven't figured out how to get it to work in this code. The code
is from another routine that was created by someone else that I modified. It has
textbox instead of optionbuttons.

style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

<cadger> wrote in message
href="news:6111229@discussion.autodesk.com">news:6111229@discussion.autodesk.com...
Hello,
After learning the basic basics like variable types, for next, if then, etc.,
modifying examples and other code to meet your needs is a good way to learn.
Start out basic and expand and refine. Here is a sample that shows autoCad
connecting to an open instance of Excel, could be refined to a certain
filename... {code} Option Explicit Sub xl2acad() Dim texthere(0 To 500) As
Variant Dim str As String Dim XL As Object Dim xlSheet As Object Dim xlcurcell
As Excel.Range Dim thisSpace As Integer thisSpace = ThisDrawing.ActiveSpace
'Dim texthere As AcadText Set XL = GetObject(, "Excel.Application") Set
xlSheet = XL.ActiveWorkbook.ActiveSheet 'MsgBox xl.ActiveCell.Value
xlSheet.UsedRange.Select 'For Each xlcurcell In
xl.ActiveCell.CurrentRegion.Cells 'MsgBox xlcurcell 'Next xlcurcell Dim dblPt
As Variant Dim dblPtOrig As Variant Dim curCol As Integer Dim curRow As
Integer Dim textalign As Integer curCol = 0: curRow = 0 Dim x As Integer Dim
textalignpt(0 To 2) As Double textalign = 0 dblPt =
ThisDrawing.Utility.GetPoint(, vbCr & "Insertion point: ") dblPtOrig =
dblPt x = 0 For Each xlcurcell In xlSheet.UsedRange 'MsgBox xlcurcell If
curCol <> 0 And curCol <> xlcurcell.Column Then ' if column change
dblPt(0) = dblPt(0) + xlcurcell.ColumnWidth If xlcurcell.HorizontalAlignment =
xlHAlignCenter Then textalignpt(0) = dblPt(0) + xlcurcell.ColumnWidth / 2
textalignpt(1) = dblPt(1) textalignpt(2) = 0# ' dblPt(0) = dblPt(0) +
xlcurcell.ColumnWidth / 2 textalign = acHorizontalAlignmentCenter Else
textalign = acHorizontalAlignmentLeft End If End If If curRow <> 0 And
curRow <> xlcurcell.Row Then ' if row change dblPt(1) = dblPt(1) -
xlcurcell.RowHeight / 9 dblPt(0) = dblPtOrig(0) End If If
xlcurcell.ColumnWidth <> 0 Then If thisSpace = 1 Then Set texthere(x) =
ThisDrawing.ModelSpace.AddText(xlcurcell.text, dblPt, 1) Else Set texthere(x)
= ThisDrawing.PaperSpace.AddText(xlcurcell.text, dblPt, 1) End If End If
curCol = xlcurcell.Column curRow = xlcurcell.Row If textalign <> 0 Then
texthere(x).HorizontalAlignment = textalign texthere(x).TextAlignmentPoint =
textalignpt End If texthere(x).Update Next xlcurcell End Sub {code} In the VBA
coding environment (in case you don't know) you can highlight objects and
methods and properties and press F1. When I'm working with both Excel and
autoCad, I like to keep a dummy copy of excel VBA IDE open (Tools Macros VBA
Editor) and copy/paste for easier F1 help access. Another useful tool to
understand what is going on in your program is to have View Locals Window on
w/ Breakpoints set at useful spots, either click in the left code margin or
right-click Toggle Breakpoint. If i were doing this i'd have the excel sheet
in one table, or maybe 4 (i.e. floors, roofs, walls, hvac), and have them in
named ranges. A possible shortcut would be to concatenate all of the columns
together, except the blockid, in excel in a hidden column (dealing with the
inch(") symbol somehow). The form could collect the results and concatenate
them and then do a Vlookup from within the program on the excel spreadsheet
looking for the blockid and then insert the block. anyway... hope its useful.
cadger

Re: Excell to Cad (2007 Autocad and Excel 2000)

Anonymous — Wed, 28 Jan 2009 00:41:57 GMT

One additional bonus with excel vba programming is that you can simply go to 'Tools Macro Record New Macro' to record new code and to see the results of actions. Alot of what's recorded is overkill and can be deleted and other portions of it need to be manipulated a little so that it will actually work inside of a program, but recording can be useful.

Re: Excell to Cad (2007 Autocad and Excel 2000)

Anonymous — Wed, 28 Jan 2009 16:38:18 GMT

That is some really great advice.
Thanks!!!!

Ran into a problem. I am getting a Runtime Error
1004.

When I add "RoofOver6in" to
"CommandButton1_Click" I get the error. If I leave it out, it runs great. Any
Ideas?

<code>

Private Sub CommandButton1_Click()

WallStud2x4
WallSidingT111
RoofPitch2in12

RoofRafter2x8
RoofInsulR21

End Sub

Public Sub
WallStud2x4()
      If ob2x4wall.Value = True
Then

Rows("1:1").Select

Selection.AutoFilter
      Selection.AutoFilter
Field:=3, Criteria1:="2 x 4"
    End If
End
Sub

Public Sub
WallSidingT111()
      If obT111Siding.Value = True
Then
      Selection.AutoFilter Field:=4,
Criteria1:="T1-11"
      End If
End
Sub

Public Sub RoofPitch2in12()
If
obRoofPitch2in12.Value = True Then

Selection.AutoFilter Field:=5, Criteria1:="2 in 12"
End If
End
Sub

Public Sub RoofRafter2x8()
If
obRoofRaft2x8.Value = True Then
Selection.AutoFilter
Field:=6, Criteria1:="2 x 8"
End If
End Sub

Public Sub RoofInsulR21()
If
obRoofInsulR21.Value = True Then

Selection.AutoFilter Field:=7, Criteria1:="R-21"
End If
End
Sub

Public Sub RoofOver6in()
If
obRoofOver6in.Value = True Then
Selection.AutoFilter
Field:=8, Criteria1:="6 in"
End If
End Sub

</code>

style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

<cadger> wrote in message
href="news:6113024@discussion.autodesk.com">news:6113024@discussion.autodesk.com...
One
additional bonus with excel vba programming is that you can simply go to
'Tools Macro Record New Macro' to record new code and to see the results of
actions. Alot of what's recorded is overkill and can be deleted and other
portions of it need to be manipulated a little so that it will actually work
inside of a program, but recording can be useful.

Re: Excell to Cad (2007 Autocad and Excel 2000)

Anonymous — Wed, 28 Jan 2009 16:49:57 GMT

I think I figured it out. Calling up to many subs.
When I broke it up, it works just fine.

style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

"Larry" <-> wrote in message
href="news:6113384@discussion.autodesk.com">news:6113384@discussion.autodesk.com...

That is some really great advice.
Thanks!!!!

Ran into a problem. I am getting a Runtime Error
1004.

When I add "RoofOver6in" to
"CommandButton1_Click" I get the error. If I leave it out, it runs great. Any
Ideas?

<code>

Private Sub CommandButton1_Click()

WallStud2x4
WallSidingT111
RoofPitch2in12

RoofRafter2x8
RoofInsulR21

End Sub

Public Sub
WallStud2x4()
      If ob2x4wall.Value = True
Then

Rows("1:1").Select

Selection.AutoFilter
      Selection.AutoFilter
Field:=3, Criteria1:="2 x 4"
    End If
End
Sub

Public Sub
WallSidingT111()
      If obT111Siding.Value =
True Then
      Selection.AutoFilter Field:=4,
Criteria1:="T1-11"
      End If
End
Sub

Public Sub RoofPitch2in12()
If
obRoofPitch2in12.Value = True Then

Selection.AutoFilter Field:=5, Criteria1:="2 in 12"
End If
End
Sub

Public Sub RoofRafter2x8()
If
obRoofRaft2x8.Value = True Then

Selection.AutoFilter Field:=6, Criteria1:="2 x 8"
End If
End
Sub

Public Sub RoofInsulR21()
If
obRoofInsulR21.Value = True Then

Selection.AutoFilter Field:=7, Criteria1:="R-21"
End If
End
Sub

Public Sub RoofOver6in()
If
obRoofOver6in.Value = True Then

Selection.AutoFilter Field:=8, Criteria1:="6 in"
End If
End
Sub

</code>

style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">

<cadger> wrote in message
href="news:6113024@discussion.autodesk.com">news:6113024@discussion.autodesk.com...
One
additional bonus with excel vba programming is that you can simply go to
'Tools Macro Record New Macro' to record new code and to see the results of
actions. Alot of what's recorded is overkill and can be deleted and other
portions of it need to be manipulated a little so that it will actually work
inside of a program, but recording can be
useful.

Re: Excell to Cad (2007 Autocad and Excel 2000)

Anonymous — Wed, 28 Jan 2009 22:18:23 GMT

Ok now I'm stuck. I haven't been able to figure out how to get the data out
of a cell in Excell. I looked at VLookup, but with that you have to know
what the value is. I don't know what the value is. I'm trying to retrive
that information. I know the cell, with the information that I am looking
for, will always be in column 1 and the second visible row. Is there a way
to say second visible row and column1 in vba?

Re: Excell to Cad (2007 Autocad and Excel 2000)

Anonymous — Thu, 29 Jan 2009 18:01:48 GMT

Me.TextBox1.Value = 

Range("A:A").SpecialCells(xlCellTypeVisible)(2).Value

I thought this might work, but still only returns the value of cell A2.
Anybody have any ideas?

Re: Excell to Cad (2007 Autocad and Excel 2000)

Anonymous — Thu, 29 Jan 2009 20:17:54 GMT

Got it.

Me.TextBox1.Value = 

Columns(1).SpecialCells(xlCellTypeVisible).Areas(2)(1).Value

"Larry" <-> wrote in message news:6114380@discussion.autodesk.com...

Me.TextBox1.Value =

Range("A:A").SpecialCells(xlCellTypeVisible)(2).Value

I thought this might work, but still only returns the value of cell A2.
Anybody have any ideas?

Re: Excell to Cad (2007 Autocad and Excel 2000)

Anonymous — Tue, 03 Feb 2009 18:47:08 GMT

Well I got it created and working. Now I need to go back and place error
handlers and put finishing touches on it. Then I will expand it to do more
things.

Thanks for the help.