Excel Report on web: workbooks.open stmt failing

HCL Domino Domino Forum
1

I am trying to leverage client-side code to run from the web rather that completely re-write it in XML. The code generates an excel spreadsheet from an xlsx file embedded in a Notes document.

The db has a Report document listing the fields to be captured, an embedded Excel file (.xlsx) and the name of the file. On the client side, the click event of a button calls a sub from an LS library and passes the necessary arguments.

To convert it to the web, the code from the button click event was moved to an Agent called by the @Command([ToolsRunMacro] function. A new subroutine was added to the script library eliminating all UI objects and calls.

Excel 2010 is on the server where the database resides.

From the web, the File is successfully extracted to the server hard drive, the ExcelApp object is successfully instantiated, but the ExcelApp object is not able to get a handle to the newly created file on the server via the: ExcelApp.workbooks.open strfilename statement (note the variable includes the server drive, directory path and complete filename).

Following is the main code excerpt:

strReportName = "e:\TempReports" & strNewFile & strTemplateExtension

Call embeditem.ExtractFile(strReportName)

’ Successful, file is created from embedded template on server hard drive

Set ExcelApp = CreateObject(“Excel.Application”)

ExcelApp.Workbooks.Open(strReportName)

’ This line fails, also tried the syntax:

ExcelApp.Workbooks.Open strReportName

	ExcelApp.visible = "False"

	'

’ (I have been debugging this code using print statements, and the following statement never runs)

Print(“opened workbook”) ’ does not display when run from web

All of the code runs on the server back end and theoretically should be able to be called from the web.

I have tried putting the “ExcelApp.visible = False” before and after instantiating the workbook.

Server OS is: Windows/Longhorn/64 6.0

LN Release 8.5.1

Does anyone have any suggestions on how I can get this to run successfully when called from the web? I have searched Lotus Notes forums for 8.0, 8.5, 6/7, OpenNTF.org and a number of other LN sites and have not found any solutions. I will be happy to provided extended code if that will help.

I look forward to any suggestions/assistance that anyone can provide.

Best regards,

Gail Spurlock

2

Subject: May i suggest

put a print statement right after

Set ExcelApp = CreateObject(“Excel.Application”)

or better yet proper error trapping in the code.

I am wondering if the service id has rights to excel. Perhaps you might need to create a service account and run domino using that service account.

3

Subject: Failure of workbooks.open

Hi Barry,

Thank you for the suggestion and taking the time to reply!

I think you may be on to something. As I have been playing around with this, I tested the excel object and captured its application path in a print statement. The path for the excel object is on the c:\ drive and the path for the excel file I extracted from an embedded item is on the e:\drive.

I was able to create a new blank workbook and successfully create the worksheets and populate them, but I still cannot open the workbook that was extracted from an embedded item to the e:\ drive. I’m also not sure that I can save the new workbook to the hard drive. . . it’s the end of the day and I’ll dig in again tomorrow :wink:

I may see if I can get a server admin to create the tempReports directory on the c:\drive and see if that works.

If you can think of anything else with this extra information, your further suggestions will be very welcome!

Best regards,

Gail Spurlock

4

Subject: Great

So I think you got it down to a rights issue. Been there and had those issues. Best will be to have a dedicated directory for the application to use. Don’t forget to do cleaning up of the directory as well.