.NET

Reply
*Nate Hunter
Message 1 of 16 (185 Views)

Excel to AutoCAD Table

185 Views, 15 Replies
03-03-2006 11:34 AM
I am having problems inputting excel info into an AutoCAD table. The code
below works great with small amounts of info but when I have more the 520
(just an example # I have been using) excel cells to go through When
inputting into the AutoCAD Table takes a very long time. Any ideas how to
speed this up? Below is my code:


Dim MyExcel As Excel.Application
Dim InsPt(0 To 2) As Double
Dim MyExcelApp As New Excel.Application
Dim MyExcelWB As Excel.Workbook
Dim MyExcelS As Excel.Worksheet
Dim intRows As Integer
Dim intCols As Integer
Dim i As Long

Set MyExcelWB = MyExcelApp.Workbooks.Open(MyPath & "\" & MyFile)
Set MyExcelS = MyExcelWB.Sheets(1)

intRows = MyExcelS.UsedRange.Rows.Count - 4
intCols = MyExcelS.UsedRange.Columns.Count

Set myTable = ThisDrawing.ModelSpace.AddTable(InsPt, intRows, intCols,
1, 1)
myTable.HeaderSuppressed = True
myTable.TitleSuppressed = True

For Y = 1 To intRows
pb1.Value = pb1.Value + 1
'go through each column in the table
For X = 1 To intCols
myTable.SetCellAlignment Y - 1, X - 1, acMiddleCenter
myTable.SetCellTextHeight Y - 1, X - 1, "7.5"

'Find the text in the cell of Excel
'*****************
'This next line is the line that takes forever to complete.
myTable.SetText Y - 1, X - 1, UCase(MyExcelS.Cells(Y + 4,
X).Text)
Next X
Next Y
*Terry W. Dotson
Message 2 of 16 (185 Views)

Re: Excel to AutoCAD Table

03-03-2006 12:36 PM in reply to: *Nate Hunter
Nate Hunter wrote:

> ... into the AutoCAD Table takes a very long time. Any ideas how to
> speed this up?

If AutoCAD Version > 2005 then set the RegenerateTableSuppressed.

Terry
*Norman Yuan
Message 3 of 16 (185 Views)

Re: Excel to AutoCAD Table

03-03-2006 12:53 PM in reply to: *Nate Hunter
The slowness is expected when you have to garb a few hundreds of value by
looping through cells in Excel sheet, using auotmation, For each value, the
communication has to pass through 2 applications' (ACAD and Excel) process
boundary. Not to mention the added layer of .NET Interop. Also, populating
AcadTable object itself in Acad is quite heavy process.

In your case, at least you can improve reading data from Excel part
significantly by using ADO/DAO/ADO.NET to read needed data into Acad in one
shot. Basically you can use MS Jet Oledb provider to open a ADO/DAO
RecordSet/ADO.NET DataSet/DataTable/Array, and then populate the Acad table.

"Nate Hunter" wrote in message
news:5101549@discussion.autodesk.com...
I am having problems inputting excel info into an AutoCAD table. The code
below works great with small amounts of info but when I have more the 520
(just an example # I have been using) excel cells to go through When
inputting into the AutoCAD Table takes a very long time. Any ideas how to
speed this up? Below is my code:


Dim MyExcel As Excel.Application
Dim InsPt(0 To 2) As Double
Dim MyExcelApp As New Excel.Application
Dim MyExcelWB As Excel.Workbook
Dim MyExcelS As Excel.Worksheet
Dim intRows As Integer
Dim intCols As Integer
Dim i As Long

Set MyExcelWB = MyExcelApp.Workbooks.Open(MyPath & "\" & MyFile)
Set MyExcelS = MyExcelWB.Sheets(1)

intRows = MyExcelS.UsedRange.Rows.Count - 4
intCols = MyExcelS.UsedRange.Columns.Count

Set myTable = ThisDrawing.ModelSpace.AddTable(InsPt, intRows, intCols,
1, 1)
myTable.HeaderSuppressed = True
myTable.TitleSuppressed = True

For Y = 1 To intRows
pb1.Value = pb1.Value + 1
'go through each column in the table
For X = 1 To intCols
myTable.SetCellAlignment Y - 1, X - 1, acMiddleCenter
myTable.SetCellTextHeight Y - 1, X - 1, "7.5"

'Find the text in the cell of Excel
'*****************
'This next line is the line that takes forever to complete.
myTable.SetText Y - 1, X - 1, UCase(MyExcelS.Cells(Y + 4,
X).Text)
Next X
Next Y
*Nate Hunter
Message 4 of 16 (185 Views)

Re: Excel to AutoCAD Table

03-03-2006 12:53 PM in reply to: *Nate Hunter
how do I do that? Mytable.RegenerateTableSuppressed = True????


"Terry W. Dotson" wrote in message
news:5101649@discussion.autodesk.com...
Nate Hunter wrote:

> ... into the AutoCAD Table takes a very long time. Any ideas how to
> speed this up?

If AutoCAD Version > 2005 then set the RegenerateTableSuppressed.

Terry
*Nate Hunter
Message 5 of 16 (185 Views)

Re: Excel to AutoCAD Table

03-03-2006 01:05 PM in reply to: *Nate Hunter
You are the man. this is exactly what I was looking for. My table no
inputs very quick. Thanks for your help




"Terry W. Dotson" wrote in message
news:5101649@discussion.autodesk.com...
Nate Hunter wrote:

> ... into the AutoCAD Table takes a very long time. Any ideas how to
> speed this up?

If AutoCAD Version > 2005 then set the RegenerateTableSuppressed.

Terry
*Terry W. Dotson
Message 6 of 16 (185 Views)

Re: Excel to AutoCAD Table

03-03-2006 01:24 PM in reply to: *Nate Hunter
Norman Yuan wrote:

> ... you can improve reading data from Excel part
> significantly by using ADO/DAO/ADO.NET to read needed data ...

Agreed, assuming you don't need any other cell properties.

Terry
*Nate Hunter
Message 7 of 16 (185 Views)

Re: Excel to AutoCAD Table

03-06-2006 05:53 AM in reply to: *Nate Hunter
Great advice. Do any of you have an example on how to do this? I am also
looking to change the cell background color. I tried:

Dim col As New AcadAcCmColor
col.SetRGB 173, 173, 173
myTable.SetCellBackgroundColor Y, X, col

but with no luck. I do not receive any errors but I also do not see any
color to my cells. Any Ideas??


"Terry W. Dotson" wrote in message
news:5101720@discussion.autodesk.com...
Norman Yuan wrote:

> ... you can improve reading data from Excel part
> significantly by using ADO/DAO/ADO.NET to read needed data ...

Agreed, assuming you don't need any other cell properties.

Terry
*Terry W. Dotson
Message 8 of 16 (185 Views)

Re: Excel to AutoCAD Table

03-06-2006 11:22 AM in reply to: *Nate Hunter
Nate Hunter wrote:

> Great advice. Do any of you have an example on how to do this?

ADO/DAO is widely documented on the web.

> Dim col As New AcadAcCmColor

Set col = AcadApplication.GetInterfaceObject("AutoCAD.AcCmColor.16")

> col.SetRGB 173, 173, 173
> myTable.SetCellBackgroundColor Y, X, col

My example works in VBA. There may be differences in .NET.

Terry
*Nate Hunter
Message 9 of 16 (185 Views)

Re: Excel to AutoCAD Table

03-06-2006 12:27 PM in reply to: *Nate Hunter
I am also using VBA. Where do I place the color code? Maybe I am trying
to do the wrong thing. I am trying to get the cell background fill to color
253

Here is what I have and I get no color on my table.

Dim col As New AcadAcCmColor
Set col = AcadApplication.GetInterfaceObject("AutoCAD.AcCmColor.16")

For y = 1 To intRows
For x = 1 To intCols
myTable.RegenerateTableSuppressed = True
myTable.SetCellBackgroundColor y, x, col
myTable.SetText y - 1, x - 1, UCase(MyExcelS.Cells(y + 4, x).Text '
gets the line from excel and places in AutoCAD.
Next x
Next y

I really appreciate your help on this.


"Terry W. Dotson" wrote in message
news:5102873@discussion.autodesk.com...
Nate Hunter wrote:

> Great advice. Do any of you have an example on how to do this?

ADO/DAO is widely documented on the web.

> Dim col As New AcadAcCmColor

Set col = AcadApplication.GetInterfaceObject("AutoCAD.AcCmColor.16")

> col.SetRGB 173, 173, 173
> myTable.SetCellBackgroundColor Y, X, col

My example works in VBA. There may be differences in .NET.

Terry
Distinguished Contributor
Posts: 1,691
Registered: ‎12-15-2003
Message 10 of 16 (185 Views)

Re: Excel to AutoCAD Table

03-06-2006 01:52 PM in reply to: *Nate Hunter
I found the following book useful for learning ADO.NET.
http://www.microsoft.com/MSPress/books/4825.asp

I already had ADO experience but ADO.NET is completely different.
I would recommend you go straight for ADO.NET.

Regards - Nathan

You are not logged in.

Log into access your profile, ask and answer questions, share ideas and more. Haven't signed up yet? Register

Announcements
Are you interested in helping shape the Autodesk Community?
We’re looking at a few different ways to improve the “All Forums” landing page and need your feedback! If interested, please take a few minutes to fill out the following Usability Study. Thank you for your time!

Need installation help?

Start with some of our most frequented solutions to get help installing your software.

Ask the Community