active sheet
 

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

active sheet
 

Why not replace

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

with

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

--

HTH

RP

"John Coon" wrote in message
...
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.xl s")
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.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

active sheet
 

Bob,

Doesn't that do the same as the Set wkbObj =
workbooks.Open(Filename:="c:\vba\sample\costing.xl s")
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" wrote in message
...
Why not replace

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

with

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

--

HTH

RP

"John Coon" wrote in message
...
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.xl s")
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.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

active sheet
 

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

--

HTH

RP

"John Coon" wrote in message
...
Bob,

Doesn't that do the same as the Set wkbObj =
workbooks.Open(Filename:="c:\vba\sample\costing.xl s")
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" wrote in message
...
Why not replace

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

with

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

--

HTH

RP

"John Coon" wrote in message
...
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.xl s")
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.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

active sheet
 

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