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

"Bob Phillips" wrote in message
...
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


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.xl s")
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