.NET

Reply
New Member
adamschilling8915
Posts: 2
Registered: ‎09-02-2010
Message 1 of 3 (238 Views)

Specifying excel sheetname while creating datalinks

238 Views, 2 Replies
06-10-2013 04:19 AM

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

ADN Support Specialist
Balaji_Ram
Posts: 733
Registered: ‎03-21-2011
Message 2 of 3 (188 Views)

Re: Specifying excel sheetname while creating datalinks

06-17-2013 10:37 PM in reply to: adamschilling8915

Hello Adam,

 

Sorry for the delay.

 

Have you tried setting the connectionstring as <Excel FileName>!<Sheet Name> ?



Balaji
Developer Technical Services
Autodesk Developer Network

New Member
adamschilling8915
Posts: 2
Registered: ‎09-02-2010
Message 3 of 3 (175 Views)

Re: Specifying excel sheetname while creating datalinks

06-18-2013 06:11 AM in reply to: adamschilling8915

I have figured it out.

dl.ConnectionString = fileselect.fn + "!" + dlName

 Thanks for the reply!

Post to the Community

Have questions about Autodesk products? Ask the community.

New Post
Announcements
Are You Going To Be @ AU 2014? Feel free to drop by our AU topic post and share your plans, plug a class that you're teaching, or simply check out who else from the community might be in attendance. Ohh and don't forgot to stop by the Autodesk Help | Learn | Collaborate booths in the Exhibit Hall and meet our community team if you get a chance!