Insert Picture into excel

davesexcel

This code works fine by itself:


 model = Sketchup.active_model
 entities = model.active_entities
 view = model.active_view

 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

and this works fine by itself:


require 'win32ole'

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

wb = xl.Workbooks.Open('C;\TestFolder\Test2.xlsx')

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

worksheet.Range('a1').Value=12
worksheet.Range('a1;b4').Value

#-----Pictures Insert Code---------------
pic = worksheet2.Pictures.Insert('S;\Stairs\Stair Pics\Fellner-Landing.JPG')
range = worksheet2.Range('A1;F15')
pic.ShapeRange.LockAspectRatio = false
pic.Top = range.Top
pic.Left = range.Left
pic.Width = range.Width
pic.Height = range.Height

I want to save the image then insert it into Excel.
-My idea was to save the image, then insert that saved image into excel.
The code does not like the jpeg file S:/Stairs/Stair Pics/write_image.jpeg, it will insert any other pic except the one that was created with the code above.



 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
 #------------------------------------


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

wb = xl.Workbooks.Open('C;\TestFolder\Test2.xlsx')

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

worksheet.Range('a1').Value=12
worksheet.Range('a1;b4').Value

#-----Pictures Insert Code---------------
pic = worksheet2.Pictures.Insert('S;/Stairs/Stair Pics/write_image.jpeg')
range = worksheet2.Range('A1;F15')
pic.ShapeRange.LockAspectRatio = false
pic.Top = range.Top
pic.Left = range.Left
pic.Width = range.Width
pic.Height = range.Height

TIG

Does the Excel app want \ separators.
Try swapping / for \ so:
pic = worksheet2.Pictures.Insert('S:/Stairs/Stair Pics/write_image.jpeg')
becomes
pic = worksheet2.Pictures.Insert('S:\Stairs\Stair Pics\write_image.jpeg')

Dan Rathbun

~

(1) Only write to paths that the user has write permissions on.

@davesexcel said:

# Puts in SketchUp install directory by default

This path is a binary program path and the normal user does not have write permissions there.

This is not the default path for Ruby when SketchUp v13+ is running. After SketchUp loads Ruby and it's API, it sets the working directory to the User's "Documents" directory.

SketchUp 2013 and higher:
Dir.pwd %(green)[#=> C:/Users/Dan/Documents]

SketchUp 8 and earlier just left the current working directory in SketchUp's program path (which was a bad thing.)

(2) In code when you are doing a write then read, you need to wait until the file is finished being written, and the OS file system advertises that it is "ready" and available. (ie, the old file handle is closed.)

The Ruby standard File class has some class methods to help you determine if paths are readable or writable by the current user, and whether the OS reports if the file is finished being written (ie, does it exist yet?)


def image_from_model(
  write_path    = 'S;/Stairs/Stair Pics/',
  keys = {
    ;filename    => 'write_image.jpeg',
    ;width       => 500,
    ;height      => 400,
    ;antialias   => false,
    ;compression => 0.5,
    ;transparent => false
  }
)
  return false unless File.writable?(write_path)
  image_path = File.join( write_path, keys[;filename] )
  keys[;filename]= image_path
  model = Sketchup.active_model
  view = model.active_view
  view.zoom_extents
  Kernel.sleep(2.0) # wait for view to redraw
  view.write_image(keys)
  @tid = UI.start_timer(0.5,true) {
    if File.exist?(image_path)
      UI.stop_timer(@tid)
      insert_to_excel(image_path)
    end
  }
end

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

  wb = xl.Workbooks.Open('C;\TestFolder\Test2.xlsx')

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

  worksheet.Range('a1').Value=12
  worksheet.Range('a1;b4').Value

  #-----Pictures Insert Code---------------
  pic = worksheet2.Pictures.Insert('S;/Stairs/Stair Pics/write_image.jpeg')
  range = worksheet2.Range('A1;F15')
  pic.ShapeRange.LockAspectRatio = false
  pic.Top = range.Top
  pic.Left = range.Left
  pic.Width = range.Width
  pic.Height = range.Height
  # write done message to SketchUp console;
  put "Finished inserting image in Excel."
end