Results 1 to 3 of 3

Excel and AutoCAD VBA

This is a discussion on Excel and AutoCAD VBA within the Excel Questions forums, part of the Question Forums category; Hi everyone...I hope this is a good place to look! I am relatively new to Excel and have been an ...

  1. #1
    New Member
    Join Date
    Jan 2006
    Posts
    13

    Default Excel and AutoCAD VBA

    Hi everyone...I hope this is a good place to look!

    I am relatively new to Excel and have been an AutoCAD for a long time. I am creating a utility that allows the user to select a piece of text and store that in a variable. The open a session of Excel behind the scenes and search for that text string, when it finds the string return a value in the row. Then return back to AutoCAD dialog box with the value from the spreadsheet populating a text box... I have included my code...Please Help!!!

    Option Explicit

    Private Sub cmdCancel_Click()
    End
    End Sub

    Private Sub cmdInsert_Click()
    'Insert the block
    Dim blockRefObj As AcadBlockReference
    Dim returnPnt As Variant

    Me.Hide
    returnPnt = ThisDrawing.Utility.GetPoint(, "Select Insertion Point: ")
    'Me.Show

    Set blockRefObj = ThisDrawing.ModelSpace.InsertBlock(returnPnt, "Excel_Import", 1, 1, 1, 0)

    'Get the attributes for the block reference
    Dim varAttributes As Variant
    varAttributes = blockRefObj.GetAttributes

    'Inserting Attributes
    'varAttributes(0).TextString = txtSAClassCode.Value
    'varAttributes(1).TextString = txtSpbldinfCode.Value
    varAttributes(2).TextString = txtSpaceNo.Value
    'varAttributes(3).TextString = txtSpbldlocCode.Value
    'varAttributes(4).TextString = txtSaclstypCode.Value
    'varAttributes(5).TextString = txtDesc.Value
    'varAttributes(6).TextString = txtSporgCode.Value
    'varAttributes(7).TextString = txtArea.Value

    'Get the attributes again
    Dim newvarAttributes As Variant
    newvarAttributes = blockRefObj.GetAttributes


    'Clear the form
    txtSAClassCode.Value = ""
    txtSpbldinfCode.Value = ""
    txtSpaceNo.Value = ""
    txtSpbldlocCode.Value = ""
    txtSaclstypCode.Value = ""
    txtDesc.Value = ""
    txtSporgCode.Value = ""
    txtArea.Value = ""

    Me.Show
    End Sub

    Private Sub cmdSelect_Click()
    Me.Hide

    '--------Get The AutoCAD Space Number--------
    'Declare Variables
    Dim returnobj As Object
    Dim entbasepnt As Variant
    Dim oSpaceNo As Variant

    'For Handle Storage
    'Dim entHandle As String
    'Dim entry As AcadEntity
    'For Handle Storage

    On Error Resume Next
    ThisDrawing.Utility.GetEntity returnobj, entbasepnt, "Select Text: "


    oSpaceNo = Trim(returnobj.TextString)
    txtSpaceNo.Text = oSpaceNo

    '--------Start the Excel Portion--------
    Dim oExcel As Excel.Application
    Dim oWRKBK As Excel.Workbook
    Dim oSheet As Excel.Worksheet

    Dim lngRows As Long
    Dim indx As Long

    On Error Resume Next
    Err.Clear ' clear any errors
    Set oExcel = GetObject(, "Excel.application") ' See if Excel is running
    If Err <> 0 Then ' ' If Excel not running start a new session
    Err.Clear
    Set oExcel = CreateObject("Excel.application") ' Start Excel if excel is not running
    If Err <> 0 Then
    MsgBox " Could not start Excel ! , Is Excel Installed ? ", vbCritical, " Excel Error ! "
    Err.Clear
    End If
    End If
    Err.Clear

    oExcel.visible = False ' Make excel application invisible
    AcadApplication.WindowState = acMax

    Set oWRKBK = oExcel.Workbooks.Open(FileName:="C:\Data\Consulting\Wilmont Sanz\Partial-Ground.xls")

    Set oSheet = oWRKBK.Worksheets(1)
    oSheet.Activate ' Make sheet 1 the active sheet

    lngRows = oWRKBK.ActiveSheet.UsedRange.Rows.Count

    Dim MyCollection As Collection
    Dim myValue As String
    For Each oSpaceNo In MyCollection
    If oSpaceNo.Text = oSheet.Cells(indx, 3) Then
    myValue = oExcel.WorksheetFunction.VLookup(oSpaceNo, "C2:E1000", 3, False)
    txtSAClassCode.Text = (myValue)
    Exit For
    End If
    Next


    oWRKBK.Close True, "C:\Data\Consulting\Wilmont Sanz\Partial-Ground.xls"
    oExcel.Quit
    Me.Show
    End Sub

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,648

    Default

    What's the actual question?
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Jan 2006
    Posts
    13

    Default

    Somewhere in the red block of text...I am not getting any values...Does that look correct for pulling a value from the same row as the piece of text I am searching on?

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com