I have written some some to automate a task involving adding datalinks into autocad. I use workbooks that contain multiple tabs to create each individual datalink. THe issue that I am having is specifying which excel sheet tab to be used while adding that data link. I have automated finding the datalink name based on the sheetnames, but each time I run the program it only adds the first sheet in the workbook.
Thanks in advance.
Imports System
Imports System.IO
Imports System.Drawing
Imports System.Windows.Forms
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Imports System.Windows.Forms.ListBox
Imports Autodesk.AutoCAD.Interop
Imports Autodesk.AutoCAD.Interop.Common
Imports Autodesk.AutoCAD.ApplicationServices
Imports Autodesk.AutoCAD.DatabaseServices
Imports Autodesk.AutoCAD.EditorInput
Imports Autodesk.AutoCAD.Runtime
Imports Autodesk.AutoCAD.Windows
Public Class fileselect
Public Shared lb As Object
Public Shared filename As Stream
Public Shared fn As Object
Public Shared xlsheet As Excel.Worksheet
Public Shared xlwbook As Excel.Workbook
Public Shared chkname As String
Public Sub FileSelectButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FileSelectButton.Click
OFD.Title = "Select File Containing Databoxes"
OFD.InitialDirectory = "G:/"
OFD.Filter = "Excel File (*.xlsx)|*.xlsx| Excel File(*.xls)|*.xls|All Files (*.*)|*.*"
OFD.ShowDialog()
End Sub
Private Sub OFD_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OFD.FileOk
Dim strm As System.IO.Stream
strm = OFD.OpenFile()
TextBox1.Text = OFD.SafeFileName.ToString()
If Not (strm Is Nothing) Then
'insert code to read the file data
strm.Close()
End If
filename = OFD.OpenFile
End Sub
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
OFD.FileName.ToString()
End Sub
Public Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim xl As New Excel.Application
fn = OFD.FileName
xlwbook = xl.Workbooks.Open(fn)
xlsheet = xlwbook.Sheets.Item(1)
For Each sht In xlwbook.Worksheets
CheckedListBox1.Items.Add(sht.Name)
Next
xlwbook = Nothing
End Sub
Private Sub MoveRight_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MoveRight.Click
Dim selectedIndex As Integer = CheckedListBox1.SelectedIndex
Dim checked As CheckedListBox.CheckedItemCollection = CheckedListBox1.CheckedItems
If (selectedIndex <> -1) Then
For i As Integer = 0 To CheckedListBox1.Items.Count - 1
' Get the selected item's check state.
Dim chkstate As CheckState
chkstate = CheckedListBox1.GetItemCheckState(i)
' Toggle the item state.
If (chkstate = CheckState.Checked) Then
chkstate = CheckState.Checked
Else
chkstate = CheckState.Checked
End If
' Set the new state.
CheckedListBox1.SetItemCheckState(i, chkstate)
Next
End If
End Sub
Private Sub MoveLeft_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MoveLeft.Click
Dim selectedIndex As Integer = CheckedListBox1.SelectedIndex
If (selectedIndex <> -1) Then
For i As Integer = 0 To CheckedListBox1.Items.Count - 1
' Get the selected item's check state.
Dim chkstate As CheckState
chkstate = CheckedListBox1.GetItemCheckState(i)
' Toggle the item state.
If (chkstate = CheckState.Unchecked) Then
chkstate = CheckState.Unchecked
Else
chkstate = CheckState.Unchecked
End If
' Set the new state.
CheckedListBox1.SetItemCheckState(i, chkstate)
Next
End If
End Sub
Private Sub Cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel.Click
Me.Close()
End Sub
Public Sub nextbutton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles nextbutton.Click
' Using checkedlistbox1 As New CheckedListBox
If CheckedListBox1.Items.Count = 0 Then
MessageBox.Show("Please select at least one")
Else
Dim selected As Integer = CheckedListBox1.SelectedIndex
Dim checked As CheckedListBox.CheckedItemCollection = CheckedListBox1.CheckedItems
'******* THIS IS WHERE I NEED TO LOOK THE SHEET NAMES AND REFERENCES DATALINK OPTIONS STARTING AROUND LINE 165 *******
'if (selected <> -1) Then
' For i As Integer = 0 To CheckedListBox1.Items.Count - 1
' 'get itemes checked state
' Dim chkstatecreation As CheckState
' chkstatecreation = CheckedListBox1.GetItemCheckState(i)
' If (chkstatecreation = CheckState.Checked) Then
For Each item In CheckedListBox1.CheckedItems
chkname = item
Next
End If
Call links.Datalinks()
End Sub
End Class
Public Class links
<CommandMethod("TFS")> _
Public Shared Sub Datalinks()
' Hardcoding the string
' Could also select for it
Dim selectedfile As String = fileselect.fn
Dim dlName As String = fileselect.chkname
Dim doc As Document = Autodesk.AutoCAD.ApplicationServices.Application.DocumentManager.MdiActiveDocument
Dim db As Database = doc.Database
Dim ed As Editor = doc.Editor
Dim doclock As DocumentLock
ed.WriteMessage(vbLf & "File selected was ""{0}"".", fileselect.fn)
Dim ppr As PromptPointResult = ed.GetPoint(vbLf & "Enter table insertion point: ")
If ppr.Status <> PromptStatus.OK Then
Return
End If
'Remove the Data Link, if it exists already
Dim dlm As DataLinkManager = db.DataLinkManager
Dim dlId As ObjectId = dlm.GetDataLink(dlName)
'If dlId <> ObjectId.Null Then
' dlm.RemoveDataLink(dlId)
'End If
' Create and add the Data Link
Dim dl As New DataLink()
dl.DataAdapterId = "AcExcel"
dl.Name = dlName
dl.Description = "Excel fun with Through the Interface"
dl.ConnectionString = fileselect.fn
dl.DataLinkOption = DataLinkOption.None
dl.DataLinkOption = DataLinkOption.PersistCache
dl.DataLinkOption = CellOption.InheritCellFormat
dl.UpdateOption = dl.UpdateOption Or CInt(UpdateOption.AllowSourceUpdate)
doclock = Autodesk.AutoCAD.ApplicationServices.Application.DocumentManager.MdiActiveDocument.LockDocument
dlId = dlm.AddDataLink(dl)
'MessageBox.Show("nothing")
Dim tr As Transaction = doc.TransactionManager.StartTransaction()
Using tr
tr.AddNewlyCreatedDBObject(dl, True)
Dim bt As BlockTable = DirectCast(tr.GetObject(db.BlockTableId, OpenMode.ForRead), BlockTable)
Dim tb As New Table()
tb.TableStyle = db.Tablestyle
tb.Position = ppr.Value
tb.SetDataLink(0, 0, dlId, True)
tb.GenerateLayout()
Dim btr As BlockTableRecord = DirectCast(tr.GetObject(db.CurrentSpaceId, OpenMode.ForWrite), BlockTableRecord)
btr.AppendEntity(tb)
tr.AddNewlyCreatedDBObject(tb, True)
tr.Commit()
End Using
' Force a regen to display the table
ed.Regen()
End Sub
End Class
