Save workbook with input box entry

davesexcel

Is it possible to saveas a workbook from input box?
Something like this is what I'm trying to do.


	prompts = ["Top Hanger Thickness","Customer Address"]
    defaults = [3.0, "Howdy"]
    input = UI.inputbox prompts, defaults, "Stair Info"
        a,b=input
   
      require('win32ole')
   excel = WIN32OLE.new('Excel.Application')
   excel.Visible = true
    number_of_sheets = excel.SheetsInNewWorkbook
    excel.SheetsInNewWorkbook = 1
    workbook = excel.Workbooks.Add
    excel.SheetsInNewWorkbook = number_of_sheets
	ws = workbook.Worksheets(1)

ws.Cells(1,1).Value = b 
  # workbook.SaveAs({C;\TestFolder\}  b & ".xlsx")#trying to get 'Customer Address' as the saved workbook
    ## workbook.SaveAs("#{pwd}whatever.xlsx",

Thanks

Dan Rathbun

@unknownuser said:

(http://sketchucation.com/forums/viewtopic.php?f)":2b5xebzl]
(3) require statements are always best located at the top of a file, since they should be dependencies. If they cannot be loaded, the rest of the script should not be run, because a LoadError exception will be raised.

Dan Rathbun

FYI

[Workbook.SaveAs Method (Excel)](http://msdn.microsoft.com/en-us/library/ff841185(v)
[Workbook.Close Method (Excel)](http://msdn.microsoft.com/en-us/library/ff838613(v)
[XlFileFormat Enumeration (Excel)](http://msdn.microsoft.com/en-us/library/ff198017(v)

(1) Always pass SketchUp API class Length instances to input boxes. And then convert back to Float, if needed. (That way users can use whatever the model units are, in the input box entry controls.)

(2) I think you want XL file format number 60

Try the following.
(I cannot test it because I haven't run the Office Home "Click-to-Run" installer yet on my machine.)

require('win32ole')

def make_wookbook()

  prompts = [
    "Top Hanger Thickness",
    "Customer Address"
  ]
  
  defaults = [
    3.0.to_l,
    "Howdy"
  ]
  
  input = UI.inputbox( prompts, defaults, "Stair Info" )
  
  if input # check validity

    a,b = input
    
    if defined?(WIN32OLE)
    
      excel = WIN32OLE.new('Excel.Application')
      excel.Visible = true
      number_of_sheets = excel.SheetsInNewWorkbook
      excel.SheetsInNewWorkbook = 1
      workbook = excel.Workbooks.Add
      excel.SheetsInNewWorkbook = number_of_sheets
      
      workbook.Activate
      sheet = workbook.Worksheets(1)

      sheet.Activate
      sheet.Range("B2").Value = b.to_f
      
      home = ENV["HOME"] || ENV["USERPROFILE"]
      sdir = "TestFolder"
      name = "CustAddy.xlsx"
      
      path = "#{home}\\Documents\\#{sdir}"
      
      if Kernel.test(?d,path)
        filepath = "#{path}\\#{name}"
      elsif Kernel.test(?d, File.dirname(path))
        filepath = "#{File.dirname(path)}\\#{name}"
      else
        if UI.respond_to?(;select_directory)
          path = UI.select_directory(
            title; "Select SpreadSheet Directory",
            directory; "#{home}\\Documents"
          )
          return false unless path
          if Kernel.test(?d,path)
            filepath = "#{path}\\#{name}"
          else
            return false
          end
        else # pre SU2015
          path = UI.savepanel(
            "Save SpreadSheet As",
            "#{home}\\Documents",
            "#{name}"
          )
          return false unless path
          if Kernel.test(?d,path)
            filepath = path
          else
            return false
          end
        end
      end

      workbook.SaveAs(
        filepath,
        60 # xlOpenDocumentSpreadsheet
      )

      excel.ActiveWorkbook.Close()
      excel.Quit()

    else
      puts("The WIN32OLE class could not be defined!")
    end
    
  end # if input valid

end # method()

Dan Rathbun

Oh and ..

(3) Always save files within the User's path to avoid permission issues.

davesexcel

Right on, thanks!