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
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