4

Using http://ruby-doc.org/stdlib/libdoc/win32ole/rdoc/classes/WIN32OLE.html as a guide I have written the following:

require 'win32ole'
excel = WIN32OLE.new('Excel.Application')
excel.visible = false #Setting this is 'true' doesn't reveal anything
workbook = excel.workbooks.open('C:\myspreadsheet.xlsx')
worksheet = workbook.worksheets('sheet1')
worksheet.Activate

data = worksheet.UsedRange.Value
p data.size #This works! - My spreadsheet has 3987 rows.
p data[3932] #This works, too! - I can "see" row 3932.

worksheet.Rows(3932).Insert #Insert a row above row 3932

data = worksheet.UsedRange.Value
p data.size #Returns 3988! This would seem to indicate that I've successfully added a row since it was just 3987.

workbook.saved = true #Save the workbook and quit.
excel.ActiveWorkbook.Close(0)
excel.Quit()

When I open the Excel spreadsheet after all of this it is unchanged. Any ideas?

Mark Cramer
  • 2,614
  • 5
  • 33
  • 57

1 Answers1

3

Setting the Saved property of a Workbook object to True does not cause the workbook to be saved. That property is used as a flag to show whether a workbook has unsaved changes. Setting it to True is a simple way of preventing the "Do you wish to save..." dialog appearing when Excel is closed.

To actually save the workbook, you need the Save method of the Workbook object. This method doesn't return anything so I would assume that workbook.Save would do the trick (I have no experience of Ruby, unfortunately, so can't be 100% sure on that)

barrowc
  • 10,444
  • 1
  • 40
  • 53
  • I added "workbook.Save" immediately after "workbook.saved = true" and everything went great. – Mark Cramer Jul 20 '11 at 19:18
  • If you wouldn't mind, out of curiosity, where could I have gone to find this information myself? It's great that I can get an answer on stackoverflow, but isn't there documentation or a reference someplace for something like this? I've looked, but the only thing I could find was the link at the top. Again, thanks for the help! – Mark Cramer Jul 20 '11 at 19:20
  • All of the details of the Excel object model are on MSDN. `Workbook.Saved` is at http://msdn.microsoft.com/en-us/library/ff196613.aspx The example code for most items will be in VBA but you should get enough information to use it in Ruby – barrowc Jul 21 '11 at 05:21
  • Thank you for the link! That's great stuff. I'm not sure that I would have ever found that. – Mark Cramer Jul 21 '11 at 18:39