Excel and AutoCAD VBA

stck2mlon

New Member
Joined
Jan 13, 2006
Messages
13
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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?
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top