Ruby program with win32ole does not work any more under Windows7 64bit

2

I have an old ruby program that extracts values from an excel file and stores the summary in another excel file. For that purpose, the program uses the library win32ole from Ruby. After switching to a new computer with Windows 7 64bit (instead of Windows XP 32bit), Office 2007 instead of Office 2003, the program now throws an error when storing the resulting excel file:

ana.rb:120:in `method_missing': SaveAs (WIN32OLERuntimeError)
  OLE error code:800A03EC in Microsoft Office Excel
    'c:/my/dir' could not be accessed. The file could be corrupt, is on a server that does not react, or the file is write protected.
    (German: Auf 'c:/my/dir' konnte nicht zugegriffen werden. Unter Umstaenden ist die Datei beschaedigt, befindet sich auf einem Server, der nicht mehr reagiert, oder die Datei ist schreibgeschuzetzt.)
HRESULT error code:0x80020009
  Ausnahmefehler aufgetreten.
    from ana.rb:120:in `save'
    from ana.rb:54:in `generateReport'
    from ana.rb:13:in `ana'
    from ana.rb:191

The relevant parts of the program are:

def generateReport
  ...
  report.save(basicdir + reportfile)
  ...
end

with the report:

class EasyExcel
  def initialize(path)
    @path = path
    @excel = excel = WIN32OLE.new("excel.application")
    @workbook = @excel.Application.Workbooks.Open(@path)
    @cache = Array.new
  end
  def save(filename)
    saveCache
    @workbook.SaveAs(filename)
  end

The line 120 is that @workbook.SaveAs(filename). The value of filename at that moment is c:/projekte/itcampus/feedback-analyse/feedback_report.xls. After some debugging, I have noticed that due to my bad ruby exception handling, after the stop of the ruby interpreter, there are 2 instances of excel hanging. So it seems the problem is really due to the changes in handling paths in Excel on Windows 7.

Does any one know the answers to the following questions:

  • What could be the reason for the failure: 64bit instead of 32bit, using Office 2007 instead of 2003, or both?
  • Is there a workaround or fix to use a bridge to Windows 7 64bit and applications like Word or Excel from Ruby?
  • How can I find which API is available from a windows application from Ruby?

The Ruby interpreter I have tried are:

  • ruby 1.8.7 (2011-02-18 patchlevel 334) [i386-mingw32]
  • ruby 1.9.2p180 (2011-02-18) [i386-mingw32]
ruby
excel
win32ole
asked on Stack Overflow Sep 19, 2011 by mliebelt • edited Sep 20, 2011 by mliebelt

3 Answers

3

Thank's to all who added ideas and comments to my question. Finally, I found a workaround.

class EasyExcel
  ....
  def save(filename)
    saveCache
    dos_file = filename.gsub(/\//, "\\\\")
    @workbook.SaveAs(filename)
  end

This replaces in the (ruby) path every forward slash with 2 backward slashes, which then will evaluated to 1 backward slash at the end.

So opening an excel with

@workbook = @excel.Application.Workbooks.Open(@path)

(with @path something like

C:/projekte/itcampus/feedback-analyse/feedback/Bewertungsbogen_XX1404.xls

) works, but

@workbook.SaveAs("c:/projekte/itcampus/feedback-analyse/feedback_report.xls")

does not. Very strange!

answered on Stack Overflow Sep 20, 2011 by mliebelt • edited Sep 20, 2011 by Andrew Grimm
1

I had a similar problem on a Windows Server 2008 (64-bit) and the solution with with filename.gsub(/\//, "\\\\") and expand_path did not help.

When I called it with my user it worked, the same programm in a background process hat the OLE-Error.

The solution (I'm not kidding, it worked): Create a folder C:\Windows\SysWOW64\config\systemprofile\Desktop.

I found the solution at https://blogs.msdn.microsoft.com/dataaccesstechnologies/2012/12/19/error-microsoft-office-excel-cannot-access-the-file-while-accessing-microsoft-office-11-0-object-library-from-ssis/:

For Windows 2008 Server x64: Create the following directory:

C:\Windows\SysWOW64\config\systemprofile\Desktop

For Windows 2008 Server x86: Create the following directory:

C:\Windows\System32\config\systemprofile\Desktop

Thats It!! Voila!! You are all set to go…..

Alternative link with similar details: https://social.msdn.microsoft.com/Forums/en-US/b81a3c4e-62db-488b-af06-44421818ef91/excel-2007-automation-on-top-of-a-windows-server-2008-x64?forum=innovateonoffice

answered on Stack Overflow Aug 19, 2016 by knut
0

Many problems you can encounter when using COM and switching to Windows 7 are related to user rights. Did you try to run your program with administrator rights?

answered on Stack Overflow Sep 20, 2011 by pagra

User contributions licensed under CC BY-SA 3.0