Get data from already opened workbook

davesexcel · 31 Jan 2017, 15:44

Hi,
I have a workbook, that I use to input the values for the sketchup to draw. I seems to be working fine but would like it to be able to get the info from the workbook that is already opened, not having to open the workbook to get the data. Is there a way to get the info from an active workbook?

This way when I run the code, it will get the info and insert the picture onto the sheet.


  UI.menu("PlugIns").add_item("Get From Excel") { get_from_excel }
require('win32ole')
	 def get_from_excel
ents=Sketchup.active_model.active_entities
require 'win32ole'


xl = WIN32OLE.new('Excel.Application')
xl.visible=1

wb = xl.Workbooks.Open('C;\TestFolder\Stair_Info_to_Sketchup.xlsm')

worksheet = wb.Worksheets('Sheet1')
worksheet2 = wb.Worksheets('Sheet2')

ent = Sketchup.active_model.entities
Sketchup.active_model.entities.clear!		
rise=worksheet.range('a2').value
run=worksheet.range('b2').value
b=worksheet.range('i2').value
d=worksheet.range('h2').value
c=worksheet.range('c2').value
dado=worksheet.range('d2').value
ns=worksheet.range('e2').value
e=worksheet.range('f2').value
wdth=worksheet.range('g2').value
tr=7
g=1.25
f=worksheet.range('j2').value
h=0.5

ent.add_line [0,(run+1)+0*run-1.5,rise-c], [0,(1.5+0*run)-1.5,rise-c]  
ent.add_line [0,1.5+0*run-1.5,rise-c+dado], [0,1.5+0*run-1.5,rise-c]
ent.add_line [0,1.5+0*run-1.5,rise-c+dado], [0,1+0*run-1.5,rise-c+dado]
ent.add_line [0,1+0*run-1.5,rise-c+dado], [0,1+0*run-1.5,rise-c]
edg = ent.add_line [0,1+0*run-(1+0.5),rise-c],[0,e+0*run-(ns+1),rise-c]
edg = ent.add_line [0,e+0*run-(ns+1),rise-c],[0,e+0*run-(ns+1),rise]
edg = ent.add_line [0,e+0*run-(ns+1),rise],[0,(run+1)+0*run-1.5,rise]
 edg=ent.add_line [0,1+run-1.5,rise],[0,1+run-1.5,rise-c]
        edg.find_faces; face=edg.faces[0]
          face.pushpull wdth
   group1 = ent.add_group face.all_connected ;group1.name="Tread"
		  group1.description="Tread"         
#loop through treads--------------------
for i in 1..tr-2
  group2 = group1.copy
  tran = Geom;;Transformation.translation [0, run*i, rise*i]
group2.transform! tran ;group2.name="Treads"
  end
  #---Good
   group = ent.add_group(); group.name="Bottom riser"
 
ent2 = group.entities
 pts = []
 pts[0] = [0, 0, 0]
 pts[1] = [0,-e,0]
 pts[2] = [0, -e,rise-c+dado]
 pts[3] = [0, 0, rise-c+dado]
 # Add the face to the entities in the model
face = ent2.add_face pts
 face.pushpull wdth
 group.description = "Bottom riser"
 #---good
 group = ent.add_group(); group.name="Riser"
ent3 = group.entities
 pts = []
pts[0] = [0, run, rise-c]
pts[1] = [0,run-e,rise-c]
 pts[2] = [0, run-e,2*rise-c+dado]
 pts[3] = [0, run, 2*rise-c+dado]
 # Add the face to the entities in the model
face = ent3.add_face pts
 face.pushpull wdth
group.description = "Riser"

for i in 1..tr-3
  group2 = group.copy
  tran = Geom;;Transformation.translation [0, run*i, rise*i]
  group2.transform! tran
  end
  #----good
  #<<<<<<Top Hanger



  group = ent.add_group(); group.name="Top riser"
 
   ent5 = group.entities
 ent5.add_line [0,b-2,d-c], [0,b-2,d-(rise+1)]
  ent5.add_line [0,b-2,d-(rise+1)], [0,b-2+h,d-(rise+1)]
  ent5.add_line [0,b-2+h,d-(rise+1)], [0,b-2+h,d]
    edg=ent5.add_line [0,b-2,d-c], [0,b-2-1,d-c]#<<<< edg
  ent5.add_line [0,b-2-1,d-c], [0,b-3,d]
  ent5.add_line [0,b-3,d] , [0,b-2+h,d]
  edg.find_faces; face=edg.faces[0]
  face.pushpull wdth
  
 #<<<<<<<<<<<<<<<Stringer
  
        # offset vectors for rise and run
		group = ents.add_group(); group.name="Stringer"
		ent1 = group.entities
		
        risevect = Geom;;Vector3d.new([0,0,0], [0,0,rise])
        runvect = Geom;;Vector3d.new([0,0,0], [0,run,0])
	#<<<<<<<<<<<<<<#last tread
        lstrun = Geom;;Vector3d.new([0,0,0], [0,(run-e),0])
	#<<<<<<<<<<<<<<<<<<<<Move Stringer
	ms= Geom;;Vector3d.new [(-wdth-g),0,0]
	ms2= Geom;;Vector3d.new [-wdth/2+g/2,0,0]
		#<<<routed stringer
  group = ent.add_group(); group.name="Routed Stringer"
 
   ent6 = group.entities
   pts = []

   pts[0] = [0,b-(1+e),d]
   pts[1] = [0,b-(4+e),d]
   pts[2] = [0,-3, rise]
   pts[3] = [0, -3, 0]
    #calculate pts 4 & 5
  vec = pts[2].vector_to(pts[1])
  v = vec.axes[1].reverse
  tpt = pts[2].offset(v,f)
  pts[4]=Geom.intersect_line_line [pts[3],[0,1,0]],[tpt,vec]
  #pts[4] = [0,(run+2.5)*0.4256, 0]
  pts[5] = Geom.intersect_line_line [pts[0],[0,0,1]],[tpt,vec]
  #pts[5] = [0, b-(1+e), (d-12 )]
  
       #add the face to the entities in the model
    face = ent6.add_face pts
    face.pushpull g
    group.description = "Routed Stringer"
	#copy routed stringer
 group1 = group.copy
  tran = Geom;;Transformation.translation ms
group1.transform! tran

#-----Pictures Insert Code---------------

 #require 'win32ole'
 model = Sketchup.active_model
 entities = model.active_entities
 view = model.active_view
 # Puts in SketchUp install directory by default
 status = view.write_image "test.jpeg"
 keys = {
   ;filename => "S;/Stairs/Stair Pics/write_image.jpeg",
   ;width => 500,
   ;height => 400,
   ;antialias => false,
   ;compression => 0.5,
   ;transparent => false
 }
 model = Sketchup.active_model
 view = model.active_view
 new_view = view.zoom_extents
 view.write_image keys
 #------------------------------------
#-----Pictures Insert Code---------------
pic = worksheet.Pictures.Insert('S;\Stairs\Stair Pics\write_image.jpeg')
range = worksheet.Range('A5;F20')
pic.ShapeRange.LockAspectRatio = false
pic.Top = range.Top
pic.Left = range.Left
pic.Width = range.Width
pic.Height = range.Height


end

Thanks

Dan Rathbun · 1 Feb 2017, 14:21

@davesexcel said:

... but would like it to be able to get the info from the workbook that is already opened, not having to open the workbook to get the data. Is there a way to get the info from an active workbook?

Have you seen my example here in this forum ?

[Code] Geometry Creation from Excel worksheet data

It has a method that first checks to see if Excel is already open. And it is properly module wrapped (but you should change the name of the outer module at least.)

davesexcel · 1 Feb 2017, 17:46

Thanks Dan,
I did find that, just can't get it to work< using all sorts of options, It seems to want to open up a new excel, and not use the existing excel. So it errors when finding the range.


  require 'win32ole'
#WIN32OLE.connect('Excel.Application')
xl = WIN32OLE;;connect('Excel.Application')
xl.visible=1

#wb = xl.Workbooks('TestAddImage.xlsm')
puts "a"
worksheet = xl.activesheet
puts "b"
#worksheet2 = xl.Worksheets('Sheet2')
puts "c"
worksheet.Range('a1').Value=1111
worksheet.Range('a2').Value

davesexcel · 1 Feb 2017, 19:04

Wow, I got it to work!!


require 'win32ole'
xl = WIN32OLE;;connect('Excel.Application')
xl.visible=1
worksheet = xl.Worksheets(1)