sketchucation logo sketchucation
    • Login
    🤑 SketchPlus 1.3 | 44 Tools for $15 until June 20th Buy Now

    Get data from already opened workbook

    Scheduled Pinned Locked Moved Developers' Forum
    4 Posts 2 Posters 616 Views 2 Watching
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • D Offline
      davesexcel
      last edited by

      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

      1 Reply Last reply Reply Quote 0
      • Dan RathbunD Offline
        Dan Rathbun
        last edited by

        @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.)

        I'm not here much anymore.

        1 Reply Last reply Reply Quote 0
        • D Offline
          davesexcel
          last edited by

          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
          
          
          1 Reply Last reply Reply Quote 0
          • D Offline
            davesexcel
            last edited by

            Wow, I got it to work!!

            
            require 'win32ole'
            xl = WIN32OLE;;connect('Excel.Application')
            xl.visible=1
            worksheet = xl.Worksheets(1)
            
            
            
            
            1 Reply Last reply Reply Quote 0
            • 1 / 1
            • First post
              Last post
            Buy SketchPlus
            Buy SUbD
            Buy WrapR
            Buy eBook
            Buy Modelur
            Buy Vertex Tools
            Buy SketchCuisine
            Buy FormFonts

            Advertisement