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:
The Ruby interpreter I have tried are:
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!
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
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?
User contributions licensed under CC BY-SA 3.0