We help IT Professionals succeed at work.

Export from Lotus Domino to Excel from Web Application

Sam654
Sam654 asked
on
I'm trying to run an agent from the web to export data to Excel. I've installed Excel on the server from MS Office 2000, I can install excel from office 2k7 if it's recommended.

Users could be using IE, FF or a browser on an MAC.

The server is giving me the following error on the SaveAs line-

Microsoft Excel: The file could not be accessed. Try one of the following:
Make sure the specified folder exists.
Make sure the folder that contains the file is not read-only.
Make sure the filename does not contain any of the following characters: < > ? [ ] : or *.
Make sure the file/path doesn't contain more than 218 characters.
(213)

Code below. I've seen a lot of posts around this subject, either they are for the notes client or not Script based, but none seem to provide a solution for the error that I'm having.

Dim session  As NotesSession
Dim db	As NotesDatabase
Dim doc	As NotesDocument
Set session	= New NotesSession
Set db	= session.currentDatabase

Dim myobject	As Variant
Dim mysheet	As Variant
	
Set myobject = createObject("Excel.application")
	
myobject.workbooks.add
	
Set mysheet = myobject.workbooks(1).Worksheets(1)
	
mysheet.name = "Review"
mysheet.cells(2, 5).value="Transfer to Excel"
myobject.visible=True
mySheet.SaveAs "C:\\test\\test.xls"
myobject.quit

Set myobject = Nothing	' free up the memory

Open in new window

Comment
Watch Question

View Solution Only

mbonaci

Commented:
What kind of agent is that? Is it really server side agent?
What is selected in agent's properties, trigger field? Respond with all properties in that section.

Have you tried to create "test" folder on your workstation?
Sjef BosmanGroupware Consultant

Commented:
There is an easier way if it's applicable in your case: produce output for Excel. No need to save files etc.

http://www.breakingpar.com/bkp/home.nsf/0/87256B280015193F87256E32007999E6
Sam654

Author

Commented:
I started by writing the whole agent out and when it didn't work, I slowly trimmed EVERYTHING back to what I have pasted above, which is what I see as the bare essentials and my only problem. I've tried it on a W2K3 server, W2k8 and at the moment I'm trying it on a Windows 7 machine, just to see if anything makes a difference. Same error comes up each time.

@mbonaci
The agent is triggered by a link that ends ?OpenAgent.

Triggered on Event, Agent List Selection and Target = None.

I specified "Allow restricted operations with full administration rights".

It's NOT "run as Web User" and NOT "run on behalf of" anyone, so it uses the id that signed the agent to run it which is the server administrator at the moment as I'm wanting to get it working before restricting it down. The Domino Server runs as a service and I would have thought it has correct rights, but I'm not 100% sure how to check.

I created a test folder on the server on the root C drive, was thinking that it might need a test folder in the directory where the database is stored, maybe the Domino server has more rights to stuff in the Data directory.

@sjef_bosman
I need to save the excel spreadsheet and allow users to download it later or when they choose in the future. The solution in that link didn't mention saving, will a save work with that. At the end it mentions about using ActiveX, I went down that route first as it works with IE, but I don't think it works with any other browser. I've done that before using Javascript and it would be acceptable if it worked across all browsers... damn browser-wars ;-)
mbonaci

Commented:
That agent isn't trying to create excel file on server, but on your local workstation. Create test folder on your C drive and try to run it...
Server agents are background agents, either scheduled or run on some db event (like receiving mail).

It's not exactly related to your problem, but here's a useful link for understanding and troubleshooting agents:
    http://www.ibm.com/developerworks/lotus/library/ls-Troubleshooting_agents_ND5_6/
Sam654

Author

Commented:
I created a C:\test folder on my local machine, where I am executing the agent from the web browser and I'm getting the same message on the server console.

I would have thought that the browser would prompt for a download location if it wanted to put an excel file somewhere.

I'm happy if it wants to put the Excel file on the client C drive, but think that permissions is going to come into it again, unless like I say, the browser prompts for a download location.

Not sure where I go from here...
Commented:
Try to use single backslash instead of double in the path:
    mySheet.SaveAs "C:\test\test.xls"
Sam654

Author

Commented:
Okay, I've been doing lots of testing as I'm very confused. I added the test folder to my client machine and changed to only use the single backslashes as you suggested. It's worked, but created a the file on the server in the test folder???

It's giving the following error on the server console
Automation object error(207)myobject.quit
when it comes to the line
myobject.quit
Also when I try to open the file on the server it says it's only available in read mode as it's locked by 'system'.

Hard to believe that creating a folder called test on the client allowed the server to create the file in it's test folder. I've since deleted the folder on the client and all still seems to work, which means that it was the slashes. Pretty sure I tried this, but that's the way things go sometimes.

Shame it's not downloading the file directly to the client computer, that would save me attaching it to a document, making it available for download and then deleting it after a period of time once I've worked out that it has actually been downloaded.
Sam654

Author

Commented:
The agent is saving the excel files, so I'll open another question for getting Domino to quit it's handle for excel on the server.