.NET
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Excel to AutoCAD Table

15 REPLIES 15
Reply
Message 1 of 16
Anonymous
1216 Views, 15 Replies

Excel to AutoCAD Table

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
15 REPLIES 15
Message 2 of 16
Anonymous
in reply to: Anonymous

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
Message 3 of 16
Anonymous
in reply to: Anonymous

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
Message 4 of 16
Anonymous
in reply to: Anonymous

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
Message 5 of 16
Anonymous
in reply to: Anonymous

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
Message 6 of 16
Anonymous
in reply to: Anonymous

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
Message 7 of 16
Anonymous
in reply to: Anonymous

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
Message 8 of 16
Anonymous
in reply to: Anonymous

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
Message 9 of 16
Anonymous
in reply to: Anonymous

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
Message 10 of 16
NathTay
in reply to: Anonymous

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
Message 11 of 16
Anonymous
in reply to: Anonymous

Nate Hunter wrote:

> I am also using VBA.

Then your posting in the wrong group!

Terry
Message 12 of 16
Anonymous
in reply to: Anonymous

I realize I am posting in the wrong group. the program started out in .Net
but once I could only find someone who is writted in VBA I switched over for
example purposes. This is the only group that I have found any information
about working with tables. My intent is to move my code into .NET when I am
finished finding out what I need to do.

So any ideas what my problem may be?


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

> I am also using VBA.

Then your posting in the wrong group!

Terry
Message 13 of 16
Mikko
in reply to: Anonymous

Dim acadMoS As IAcadModelSpace2 = acadDoc.ModelSpace
Dim pt(2) As Double
pt(0) = 5.0
pt(1) = 5.0
pt(2) = 0.0
Dim MyTable As AcadTable
MyTable = acadMoS.AddTable(pt, 5, 5, 1, 2)
Dim cr As AcadAcCmColor
cr = AcadApp.GetInterfaceObject("AutoCad.AcCmColor.16")
cr.SetRGB(255, 255, 0)
MyTable.SetCellBackgroundColor(3, 2, cr)
MyTable.SetCellBackgroundColorNone(3, 2, False)
Message 14 of 16
Anonymous
in reply to: Anonymous

Alright you got me on the right track. Thank you very much. I have one
last question for you. I am trying to fill every other line in my table.
How can I go through every other line and set a background fill to it. Here
is my code:

y = my row & x = my column

Set NewColor = New AcadAcCmColor
NewColor.SetRGB 173, 173, 173
myTable.SetCellBackgroundColor y - 1, x - 1, NewColor
If strOddEvenTable = "Even" Or strOddEvenTable = "" Then
myTable.SetBackgroundColorNone y - 1, True
strOddEvenTable = "Odd"
Else
strOddEvenTable = "Even"
myTable.SetBackgroundColorNone y - 1, False
End If




wrote in message news:5104040@discussion.autodesk.com...
Dim acadMoS As IAcadModelSpace2 = acadDoc.ModelSpace
Dim pt(2) As Double
pt(0) = 5.0
pt(1) = 5.0
pt(2) = 0.0
Dim MyTable As AcadTable
MyTable = acadMoS.AddTable(pt, 5, 5, 1, 2)
Dim cr As AcadAcCmColor
cr = AcadApp.GetInterfaceObject("AutoCad.AcCmColor.16")
cr.SetRGB(255, 255, 0)
MyTable.SetCellBackgroundColor(3, 2, cr)
MyTable.SetCellBackgroundColorNone(3, 2, False)
Message 15 of 16
Anonymous
in reply to: Anonymous

I am sorry I meant to say every other row. I am trying to fill the cell
background for every other row.

Any Ideas??

Thanks again for your help. Below is my code:

"Nate Hunter" wrote in message
news:5104104@discussion.autodesk.com...
Alright you got me on the right track. Thank you very much. I have one
last question for you. I am trying to fill every other line in my table.
How can I go through every other line and set a background fill to it. Here
is my code:

y = my row & x = my column

Set NewColor = New AcadAcCmColor
NewColor.SetRGB 173, 173, 173
myTable.SetCellBackgroundColor y - 1, x - 1, NewColor
If strOddEvenTable = "Even" Or strOddEvenTable = "" Then
myTable.SetBackgroundColorNone y - 1, True
strOddEvenTable = "Odd"
Else
strOddEvenTable = "Even"
myTable.SetBackgroundColorNone y - 1, False
End If




wrote in message news:5104040@discussion.autodesk.com...
Dim acadMoS As IAcadModelSpace2 = acadDoc.ModelSpace
Dim pt(2) As Double
pt(0) = 5.0
pt(1) = 5.0
pt(2) = 0.0
Dim MyTable As AcadTable
MyTable = acadMoS.AddTable(pt, 5, 5, 1, 2)
Dim cr As AcadAcCmColor
cr = AcadApp.GetInterfaceObject("AutoCad.AcCmColor.16")
cr.SetRGB(255, 255, 0)
MyTable.SetCellBackgroundColor(3, 2, cr)
MyTable.SetCellBackgroundColorNone(3, 2, False)
Message 16 of 16
Kamala
in reply to: Anonymous

Can i knowhow to work out with this in .Net i think there is a lot of change can anyone help with me the basics how to draw a table so that i can try to make up for the other half..
Thanks in advance
Kamala

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk DevCon in Munich May 28-29th


Autodesk Design & Make Report

”Boost