Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy sheet and make new sheet active | Excel Discussion (Misc queries) | |||
Active Cell Copy And Paste Sheet to Sheet | New Users to Excel | |||
Using the Active cell in one sheet for another sheet | Excel Discussion (Misc queries) | |||
Active Sheet | Excel Programming | |||
How to make a sheet the active sheet? | Excel Programming |