active sheet

[John Coon]

Ron,

I added the Set wkbObj = ExcelApp.ActiveWorkbook but it return a 0 for calc
in textbox1
The excel file only has two colnums A & B see below. When I subsitute a
hard path = Set wkbObj =
workbooks.Open(Filename:="c:\vba\sample\costing.xls")
the routines works fine. ? Why or how do I get this to read costing.xls
which will be the currently open and have it read the values in colnum A &
B.
I thought about adding a commonDialog to select the file but that seems like
overkill. if I could just connect to the currently open excel sheet i'd be
fine.

A B
duct 2.00
as-built 5.00

Thank you for your comments.
John coon

Private Sub CommandButton1_Click()

On Error Resume Next
Set ExcelApp = CreateObject("excel.Application")
If Err <> 0 Then
Err.Clear
MsgBox "Could not start Excel", vbExclamation
End
End If
ExcelApp.Visible = True
''''''Set wkbObj = workbooks.Open(Filename:="c:\vba\sample\costing.xls")
Set wkbObj = ExcelApp.ActiveWorkbook
Set shtObj = wkbObj.Worksheets("sheet1")
Dim i, j As Integer
Dim pnum, anum, atot As Double
Dim lnam, enam As String
Dim ent As Object
i = 1: anum1 = 0#: anum2 = 0#: atot = 0#:
lnam = shtObj.Cells(i, 1).Value
pnum = shtObj.Cells(i, 2).Value
Do While Not (lnam = "")
For j = 0 To ThisDrawing.ModelSpace.Count - 1
Set ent = ThisDrawing.ModelSpace.Item(j)
If ent.EntityType = 24 And ent.Layer = lnam Then
anum1 = ent.Area
anum2 = anum2 + anum1
End If
Next j
atot = atot + (anum2 * pnum)
anum1 = 0#: anum2 = 0#
i = i + 1
lnam = shtObj.Cells(i, 1).Value
pnum = shtObj.Cells(i, 2).Value
Loop
ExcelApp.Quit
TextBox1.Text = atot
End Sub

 

[Bob Phillips]

Why not replace

''''''Set wkbObj = workbooks.Open(Filename:="c:\vba\sample\costing.xls")
Set wkbObj = ExcelApp.ActiveWorkbook

with

Set wkbObj = ExcelApp.Workbooks.Open(Filename:="c:\vba\sample\costing.xls")

 

[John Coon]

Bob,

Doesn't that do the same as the Set wkbObj =
workbooks.Open(Filename:="c:\vba\sample\costing.xls")
It links by the filename. Maybe I wasn't clear in my description. I want to
link to "Whatever excel is current" without the filename.
I'm not familiar with excel but in Autocad you can link to the current
document. I tried Set wbkObj = ExcelApp.ActiveWorkbook
that Ron from the group suggested. But that return 0 in the clac too.

My goal was to use the same routine for different excel sheets so I wanted
to connect to whatever was the current sheet without having to hard code the
path.
the routine looks at all the elements in a autocad drawing and looks for
closed polygons on certain layers that are declared in the excel file
(column A).
each layer has a corresponding cost (column B) in the excel. my switching
drawings and excel files I could use the same VBA routine for all types of
bill of materials

Thanks you for all your help. I just got back from the book store were . I
purchased excel 2002 power programming with VBA. This might help for other
routines.

John Coon

 

[Bob Phillips]

John,

It is not clear to me what you are trying to do, or why you get a 0 on a Set
command (it doesn't return a value)

You can get the activesheet just as simply with

Set shtObj = wkbObj.Activesheet

 

[John Coon]

Bob,
Sorry, No I get the 0 in the TextBox1.Text = atot when I change any of the
calls to select the ActiveSheet. It works fine with the hard coded path.
The routine gets the area of a polygon and multiplies by the value in column
B that's it. When I change to ActiveSheet it returns 0 in the textbox, hard
coded
let say I have a area on 50 sq ft * column B (2.00) it should return 100.00.
it does, soon as I change to active I get 0

Thanks for all your comments, I'll look at this new book and see if it has
anything in the way of linking to active workbooks and then sheets
John Coon