- Home
- /
- Architecture, Engineering & Construction
- /
- AutoCAD Forums
- /
- VBA Forum
- /
- Excel to Acad Table
Excel to Acad Table
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Excel to Acad Table
Not applicable
526 Views
7 Replies
527 Views
7 Replies
Replies (7)
Message 2 of 8
Not applicable
03-08-2006
11:08 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
I'll put this out here in hopes someone can improve it. You will need to
make adjustments to provide some information that
I get through calls to other functions, particularly in the function that
makes the table style.
This works, but it's eternally slow. Takes way to long to populate a table.
Near as I can tell, it's the .settext function where the value gets written
into the table cell that takes such a long time.
I put this out here in hope that maybe someone out there can improve it and
make it practical.
Please share any ideas
Public Function tl_Nz(v1 As Variant, v2 As Variant) As Variant
tl_Nz = v1
If IsNull(v1) Then
tl_Nz = v2
End If
End Function
Public Sub tl_xls_to_table (ByVal filnam As String)
Dim dir As String
On Error GoTo Egress
Dim Excel As Object
Set Excel = GetObject(, "Excel.Application")
If Err.number <> 0 Then
Err.Clear
Set Excel = CreateObject("Excel.Application")
If Err.number <> 0 Then
MsgBox "Could not start Excel.", vbExclamation
Exit Sub
End If
End If
Dim rownum As Integer
Dim excelbook As Workbook
Dim excelsheet As Worksheet
Dim cnt, cnt1 As Integer
On Error GoTo Handler
With Excel
Set excelbook = .Workbooks.Open(filnam)
excelbook.Activate
End With
Dim rowcnt, colcnt As Long
Dim xldat() As String
Set excelsheet = excelbook.ActiveSheet
With excelsheet
.Select
rowcnt = .UsedRange.Rows.Count
colcnt = .UsedRange.Columns.Count
ReDim xldat(1 To rowcnt, 1 To colcnt)
For rownum = 1 To rowcnt
For cnt1 = 1 To colcnt
xldat(rownum, cnt1) = tl_Nz(.Cells(rownum, cnt1), "")
Next cnt1
Next rownum
End With
cnt = 0
excelbook.Close
Set excelbook = Nothing
Excel.Quit
Set Excel = Nothing
Debug.Print "Finished reading xl at " & Now
Dim MyModelSpace As IAcadModelSpace2
Set MyModelSpace = ActiveDocument.ModelSpace
Dim pt(2) As Double
pt(0) = 0: pt(1) = 0: pt(2) = 0
Dim MyTable As AcadTable
tl_make_tablestyle
Set MyTable = MyModelSpace.AddTable(pt, rowcnt, colcnt, 12, 120)
ZoomExtents
Debug.Print "Starting table population at " & Now
With MyTable
For rownum = 1 To rowcnt
For cnt1 = 1 To colcnt
If Not IsEmpty(xldat(rownum, cnt1)) Then
.SetText rownum - 1, cnt1 - 1, xldat(rownum, cnt1)
End If
Next cnt1
Next rownum
End With
Debug.Print "Done at " & Now
GoTo Egress
Handler:
MsgBox Err.number & " " & Err.Description
Err.Clear
Resume Next
Egress:
If Not excelbook Is Nothing Then
excelbook.Save
excelbook.Close
End If
If Not Excel Is Nothing Then
Excel.Quit
End If
Set excelbook = Nothing: Set Excel = Nothing: Set excelsheet = Nothing
End Sub
'''this makes a table style. The scl value indicates the scale factor for
your drawing (i.e. 96 for 1/8"). The various stylenam variables
'''are the names of text styles to be used in the table.
Public Sub tl_make_tablestyle()
Dim scl As Double
Dim dict As AcadDictionary
Dim stylenam As String
Dim styledat As Variant
Dim tstyle As AcadTableStyle
scl = tl_getvar("tl_scale")
stylenam = "HCM_" & CStr(Fix(scl))
If scl = 0 Then
Exit Sub
End If
Set dict = ActiveDocument.Dictionaries("acad_tablestyle")
On Error Resume Next
Set tstyle = dict(stylenam)
If Err.number <> 0 Then
Set tstyle = dict.AddObject(stylenam, "acdbtablestyle")
Err.Clear
End If
On Error GoTo Handler
If Not tstyle Is Nothing Then
Dim colr As AcadAcCmColor
Set colr = New AcadAcCmColor
colr.ColorIndex = acWhite
Dim style1, style2, style3 As String
styledat = tl_do_make_textstyle("anntext")
style1 = styledat(0)
styledat = tl_do_make_textstyle("title_2")
style2 = styledat(0)
styledat = tl_do_make_textstyle("title_3")
style3 = styledat(0)
With tstyle
.Description = "HCM table - " & tl_scaletxt(scl, False) & "
scale"
.SetTextHeight 1, 0.09375 * scl
.SetTextHeight 4, 0.125 * scl
.SetTextHeight 2, 0.1875 * scl
.VertCellMargin = 0.0625 * scl
.HorzCellMargin = 0.0625 * scl
.SetTextStyle 1, style1
.SetTextStyle 2, style3
.SetTextStyle 4, style2
.SetAlignment 1, acBottomLeft
.SetGridColor 1, 2, colr
.SetGridColor 4, 1, colr
.SetGridColor 40, 7, colr
ActiveDocument.SetVariable "ctablestyle", .Name
End With
End If
Exit Sub
Handler:
MsgBox Err.number & " " & Err.Description
Err.Clear
Resume Next
End Sub
wrote in message news:5105042@discussion.autodesk.com...
I have read through many of the threads regarding transfer of information
between Excel and Acad. Can you transfer Excel information right into cells
of Acad's table? I would appreciate any help to code this.
Thanks,
Bill
make adjustments to provide some information that
I get through calls to other functions, particularly in the function that
makes the table style.
This works, but it's eternally slow. Takes way to long to populate a table.
Near as I can tell, it's the .settext function where the value gets written
into the table cell that takes such a long time.
I put this out here in hope that maybe someone out there can improve it and
make it practical.
Please share any ideas
Public Function tl_Nz(v1 As Variant, v2 As Variant) As Variant
tl_Nz = v1
If IsNull(v1) Then
tl_Nz = v2
End If
End Function
Public Sub tl_xls_to_table (ByVal filnam As String)
Dim dir As String
On Error GoTo Egress
Dim Excel As Object
Set Excel = GetObject(, "Excel.Application")
If Err.number <> 0 Then
Err.Clear
Set Excel = CreateObject("Excel.Application")
If Err.number <> 0 Then
MsgBox "Could not start Excel.", vbExclamation
Exit Sub
End If
End If
Dim rownum As Integer
Dim excelbook As Workbook
Dim excelsheet As Worksheet
Dim cnt, cnt1 As Integer
On Error GoTo Handler
With Excel
Set excelbook = .Workbooks.Open(filnam)
excelbook.Activate
End With
Dim rowcnt, colcnt As Long
Dim xldat() As String
Set excelsheet = excelbook.ActiveSheet
With excelsheet
.Select
rowcnt = .UsedRange.Rows.Count
colcnt = .UsedRange.Columns.Count
ReDim xldat(1 To rowcnt, 1 To colcnt)
For rownum = 1 To rowcnt
For cnt1 = 1 To colcnt
xldat(rownum, cnt1) = tl_Nz(.Cells(rownum, cnt1), "")
Next cnt1
Next rownum
End With
cnt = 0
excelbook.Close
Set excelbook = Nothing
Excel.Quit
Set Excel = Nothing
Debug.Print "Finished reading xl at " & Now
Dim MyModelSpace As IAcadModelSpace2
Set MyModelSpace = ActiveDocument.ModelSpace
Dim pt(2) As Double
pt(0) = 0: pt(1) = 0: pt(2) = 0
Dim MyTable As AcadTable
tl_make_tablestyle
Set MyTable = MyModelSpace.AddTable(pt, rowcnt, colcnt, 12, 120)
ZoomExtents
Debug.Print "Starting table population at " & Now
With MyTable
For rownum = 1 To rowcnt
For cnt1 = 1 To colcnt
If Not IsEmpty(xldat(rownum, cnt1)) Then
.SetText rownum - 1, cnt1 - 1, xldat(rownum, cnt1)
End If
Next cnt1
Next rownum
End With
Debug.Print "Done at " & Now
GoTo Egress
Handler:
MsgBox Err.number & " " & Err.Description
Err.Clear
Resume Next
Egress:
If Not excelbook Is Nothing Then
excelbook.Save
excelbook.Close
End If
If Not Excel Is Nothing Then
Excel.Quit
End If
Set excelbook = Nothing: Set Excel = Nothing: Set excelsheet = Nothing
End Sub
'''this makes a table style. The scl value indicates the scale factor for
your drawing (i.e. 96 for 1/8"). The various stylenam variables
'''are the names of text styles to be used in the table.
Public Sub tl_make_tablestyle()
Dim scl As Double
Dim dict As AcadDictionary
Dim stylenam As String
Dim styledat As Variant
Dim tstyle As AcadTableStyle
scl = tl_getvar("tl_scale")
stylenam = "HCM_" & CStr(Fix(scl))
If scl = 0 Then
Exit Sub
End If
Set dict = ActiveDocument.Dictionaries("acad_tablestyle")
On Error Resume Next
Set tstyle = dict(stylenam)
If Err.number <> 0 Then
Set tstyle = dict.AddObject(stylenam, "acdbtablestyle")
Err.Clear
End If
On Error GoTo Handler
If Not tstyle Is Nothing Then
Dim colr As AcadAcCmColor
Set colr = New AcadAcCmColor
colr.ColorIndex = acWhite
Dim style1, style2, style3 As String
styledat = tl_do_make_textstyle("anntext")
style1 = styledat(0)
styledat = tl_do_make_textstyle("title_2")
style2 = styledat(0)
styledat = tl_do_make_textstyle("title_3")
style3 = styledat(0)
With tstyle
.Description = "HCM table - " & tl_scaletxt(scl, False) & "
scale"
.SetTextHeight 1, 0.09375 * scl
.SetTextHeight 4, 0.125 * scl
.SetTextHeight 2, 0.1875 * scl
.VertCellMargin = 0.0625 * scl
.HorzCellMargin = 0.0625 * scl
.SetTextStyle 1, style1
.SetTextStyle 2, style3
.SetTextStyle 4, style2
.SetAlignment 1, acBottomLeft
.SetGridColor 1, 2, colr
.SetGridColor 4, 1, colr
.SetGridColor 40, 7, colr
ActiveDocument.SetVariable "ctablestyle", .Name
End With
End If
Exit Sub
Handler:
MsgBox Err.number & " " & Err.Description
Err.Clear
Resume Next
End Sub
I have read through many of the threads regarding transfer of information
between Excel and Acad. Can you transfer Excel information right into cells
of Acad's table? I would appreciate any help to code this.
Thanks,
Bill
Message 3 of 8
Not applicable
03-08-2006
11:56 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
No need for code...
In Excel, copy the cells you want, then in AutoCAD select Past Special, then
select AutoCAD Entities. The result will be table object with the
information you copied from Excel.
Matt W
"I did absolutely nothing, and it was everything I thought it could be."
wrote in message news:5105042@discussion.autodesk.com...
I have read through many of the threads regarding transfer of information
between Excel and Acad. Can you transfer Excel information right into cells
of Acad's table? I would appreciate any help to code this.
Thanks,
Bill
In Excel, copy the cells you want, then in AutoCAD select Past Special, then
select AutoCAD Entities. The result will be table object with the
information you copied from Excel.
Matt W
"I did absolutely nothing, and it was everything I thought it could be."
I have read through many of the threads regarding transfer of information
between Excel and Acad. Can you transfer Excel information right into cells
of Acad's table? I would appreciate any help to code this.
Thanks,
Bill
Message 4 of 8
Not applicable
03-08-2006
12:08 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
See posts in autocad.customization.dotnet. (Excel to AutoCAD Table). I
have been having dialog about this very subject in this newsgroup. It
pretty much explains what to do. A word of advice from one who has gone
through the pains of the threads is to limit the table rows to 25 at a time
and have a table style set up with the formatting. The last advice I will
have for you is to use myTable.RegenerateTableSuppressed = True whenever you
do anything to the table. This will speed you up dramatically. If you
still get stuck give me a shout back and I will try to post some of my code.
It is pretty lengthy!!
Nate
"Matt W" wrote in message
news:5105394@discussion.autodesk.com...
No need for code...
In Excel, copy the cells you want, then in AutoCAD select Past Special, then
select AutoCAD Entities. The result will be table object with the
information you copied from Excel.
Matt W
"I did absolutely nothing, and it was everything I thought it could be."
wrote in message news:5105042@discussion.autodesk.com...
I have read through many of the threads regarding transfer of information
between Excel and Acad. Can you transfer Excel information right into cells
of Acad's table? I would appreciate any help to code this.
Thanks,
Bill
have been having dialog about this very subject in this newsgroup. It
pretty much explains what to do. A word of advice from one who has gone
through the pains of the threads is to limit the table rows to 25 at a time
and have a table style set up with the formatting. The last advice I will
have for you is to use myTable.RegenerateTableSuppressed = True whenever you
do anything to the table. This will speed you up dramatically. If you
still get stuck give me a shout back and I will try to post some of my code.
It is pretty lengthy!!
Nate
"Matt W"
news:5105394@discussion.autodesk.com...
No need for code...
In Excel, copy the cells you want, then in AutoCAD select Past Special, then
select AutoCAD Entities. The result will be table object with the
information you copied from Excel.
Matt W
"I did absolutely nothing, and it was everything I thought it could be."
I have read through many of the threads regarding transfer of information
between Excel and Acad. Can you transfer Excel information right into cells
of Acad's table? I would appreciate any help to code this.
Thanks,
Bill
Message 5 of 8
Not applicable
03-09-2006
07:12 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Thanks, Matt.
This works, sort of, but the resulting table has cell widths and heights
that take a lot of manual adjusting (we were trying to copy a planting list
with about 40 rows). Good trick to know in a pinch, though.
"Matt W" wrote in message
news:5105394@discussion.autodesk.com...
No need for code...
In Excel, copy the cells you want, then in AutoCAD select Past Special, then
select AutoCAD Entities. The result will be table object with the
information you copied from Excel.
Matt W
"I did absolutely nothing, and it was everything I thought it could be."
wrote in message news:5105042@discussion.autodesk.com...
I have read through many of the threads regarding transfer of information
between Excel and Acad. Can you transfer Excel information right into cells
of Acad's table? I would appreciate any help to code this.
Thanks,
Bill
This works, sort of, but the resulting table has cell widths and heights
that take a lot of manual adjusting (we were trying to copy a planting list
with about 40 rows). Good trick to know in a pinch, though.
"Matt W"
news:5105394@discussion.autodesk.com...
No need for code...
In Excel, copy the cells you want, then in AutoCAD select Past Special, then
select AutoCAD Entities. The result will be table object with the
information you copied from Excel.
Matt W
"I did absolutely nothing, and it was everything I thought it could be."
I have read through many of the threads regarding transfer of information
between Excel and Acad. Can you transfer Excel information right into cells
of Acad's table? I would appreciate any help to code this.
Thanks,
Bill
Message 6 of 8
Not applicable
03-09-2006
07:52 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Nate -
Many thanks. The RegenerateTableSuppressed was what I needed. Execution for
a 200-line table went from about 15 minutes to about two seconds.
Note: To use the RegenerateTableSuppressed, you need to dim the table as an
IAcadTable2 rather than AcadTable. Took me a while to figure that one out.
"Nate Hunter" wrote in message
news:5105415@discussion.autodesk.com...
See posts in autocad.customization.dotnet. (Excel to AutoCAD Table). I
have been having dialog about this very subject in this newsgroup. It
pretty much explains what to do. A word of advice from one who has gone
through the pains of the threads is to limit the table rows to 25 at a time
and have a table style set up with the formatting. The last advice I will
have for you is to use myTable.RegenerateTableSuppressed = True whenever you
do anything to the table. This will speed you up dramatically. If you
still get stuck give me a shout back and I will try to post some of my code.
It is pretty lengthy!!
Nate
"Matt W" wrote in message
news:5105394@discussion.autodesk.com...
No need for code...
In Excel, copy the cells you want, then in AutoCAD select Past Special, then
select AutoCAD Entities. The result will be table object with the
information you copied from Excel.
Matt W
"I did absolutely nothing, and it was everything I thought it could be."
wrote in message news:5105042@discussion.autodesk.com...
I have read through many of the threads regarding transfer of information
between Excel and Acad. Can you transfer Excel information right into cells
of Acad's table? I would appreciate any help to code this.
Thanks,
Bill
Many thanks. The RegenerateTableSuppressed was what I needed. Execution for
a 200-line table went from about 15 minutes to about two seconds.
Note: To use the RegenerateTableSuppressed, you need to dim the table as an
IAcadTable2 rather than AcadTable. Took me a while to figure that one out.
"Nate Hunter"
news:5105415@discussion.autodesk.com...
See posts in autocad.customization.dotnet. (Excel to AutoCAD Table). I
have been having dialog about this very subject in this newsgroup. It
pretty much explains what to do. A word of advice from one who has gone
through the pains of the threads is to limit the table rows to 25 at a time
and have a table style set up with the formatting. The last advice I will
have for you is to use myTable.RegenerateTableSuppressed = True whenever you
do anything to the table. This will speed you up dramatically. If you
still get stuck give me a shout back and I will try to post some of my code.
It is pretty lengthy!!
Nate
"Matt W"
news:5105394@discussion.autodesk.com...
No need for code...
In Excel, copy the cells you want, then in AutoCAD select Past Special, then
select AutoCAD Entities. The result will be table object with the
information you copied from Excel.
Matt W
"I did absolutely nothing, and it was everything I thought it could be."
I have read through many of the threads regarding transfer of information
between Excel and Acad. Can you transfer Excel information right into cells
of Acad's table? I would appreciate any help to code this.
Thanks,
Bill
Message 7 of 8
Not applicable
03-09-2006
08:58 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
glad to help.
Nate
"Tony Burba" wrote in message
news:5106226@discussion.autodesk.com...
Nate -
Many thanks. The RegenerateTableSuppressed was what I needed. Execution for
a 200-line table went from about 15 minutes to about two seconds.
Note: To use the RegenerateTableSuppressed, you need to dim the table as an
IAcadTable2 rather than AcadTable. Took me a while to figure that one out.
"Nate Hunter" wrote in message
news:5105415@discussion.autodesk.com...
See posts in autocad.customization.dotnet. (Excel to AutoCAD Table). I
have been having dialog about this very subject in this newsgroup. It
pretty much explains what to do. A word of advice from one who has gone
through the pains of the threads is to limit the table rows to 25 at a time
and have a table style set up with the formatting. The last advice I will
have for you is to use myTable.RegenerateTableSuppressed = True whenever you
do anything to the table. This will speed you up dramatically. If you
still get stuck give me a shout back and I will try to post some of my code.
It is pretty lengthy!!
Nate
"Matt W" wrote in message
news:5105394@discussion.autodesk.com...
No need for code...
In Excel, copy the cells you want, then in AutoCAD select Past Special, then
select AutoCAD Entities. The result will be table object with the
information you copied from Excel.
Matt W
"I did absolutely nothing, and it was everything I thought it could be."
wrote in message news:5105042@discussion.autodesk.com...
I have read through many of the threads regarding transfer of information
between Excel and Acad. Can you transfer Excel information right into cells
of Acad's table? I would appreciate any help to code this.
Thanks,
Bill
Nate
"Tony Burba"
news:5106226@discussion.autodesk.com...
Nate -
Many thanks. The RegenerateTableSuppressed was what I needed. Execution for
a 200-line table went from about 15 minutes to about two seconds.
Note: To use the RegenerateTableSuppressed, you need to dim the table as an
IAcadTable2 rather than AcadTable. Took me a while to figure that one out.
"Nate Hunter"
news:5105415@discussion.autodesk.com...
See posts in autocad.customization.dotnet. (Excel to AutoCAD Table). I
have been having dialog about this very subject in this newsgroup. It
pretty much explains what to do. A word of advice from one who has gone
through the pains of the threads is to limit the table rows to 25 at a time
and have a table style set up with the formatting. The last advice I will
have for you is to use myTable.RegenerateTableSuppressed = True whenever you
do anything to the table. This will speed you up dramatically. If you
still get stuck give me a shout back and I will try to post some of my code.
It is pretty lengthy!!
Nate
"Matt W"
news:5105394@discussion.autodesk.com...
No need for code...
In Excel, copy the cells you want, then in AutoCAD select Past Special, then
select AutoCAD Entities. The result will be table object with the
information you copied from Excel.
Matt W
"I did absolutely nothing, and it was everything I thought it could be."
I have read through many of the threads regarding transfer of information
between Excel and Acad. Can you transfer Excel information right into cells
of Acad's table? I would appreciate any help to code this.
Thanks,
Bill
Message 8 of 8
Not applicable
03-09-2006
10:20 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
Thanks for all of the replies, unfortunately, I have run out of time. I lost my window of time that I could fiddle with this. I will be able to return to this in a few weeks. I have this thread ear marked so that I can return, I'll be asking questions then...
Thanks Again,
Bill
Thanks Again,
Bill