Export from Notes on web to Excel; data transfer problem

HCL Domino Domino Forum
1

I am trying to adapt a rather complicated application, which I’ve inherited, to work on the web. I’ve got useful tips here and in other forums, and I’ve come very far, but not to completion. Now I’m asking: am I thinking “right”? I’ve been struggling with this so long that I may not see if I’m barking up the wrong tree.

The task is to generate a report in Excel, based on information in selected documents. The fields that should be used for the export are defined in a Notes document. An LS agent collects the data and writes it to a number of text files that are saved on the user’s C: drive. Finally it opens the Excel template, which is stored in a Notes form. An auto_open Excel macro imports the data from the text files, and, pronto, the user can start analyze the data in pivot tables and charts.

What was complicated in Notes seemed impossible on the web, but I gave it a try. With JavaScript I can send the selected documents to a modified LS agent. This can write the text files to the Domino server’s C: drive, to a directory that is accessible for regular users.

How to launch the Excel template is one of the issues. If I use this script, it’s launched in the browser, and then some macros won’t work:

Print |window.open(‘| & dbServer + dbPath + “(myForm)/$FILE/” + “(myAttachment)” +“?OpenElement” & |’)|

This works to start Excel in its own window, but only an empty file:

Print |Content-Type: application/vnd.ms-excel|

Print |Content-disposition: attachment; filename=“(myExcelFile)”|

Since I’ve picked this code in the forum, I don’t know how to modify it. Can the two expressions be combined?

My workaround is unfortunately not programmable, and that is to uncheck the “Browse in same window” property for the XLS file type in my Windows settings.

So far, so good. In the Excel template the text file paths and names are hardcoded. The macro uses these to do an “Open (file) for Input” to get the data. Here comes the obstacle I haven’t been able to overcome: I can’t access the files on the server with a “\Server\Folder\File” expression. I can only use an “http://Server/Folder/File” expression. There is a macro command to follow this hyperlink, but none to get to the content. First I was fooled by the method “CreateNewDocument”, since its explanation is: “Creates a new document linked to the specified hyperlink”. But I soon discovered that the new document was empty. Is there any other way?

This last is not a Notes question, but I’ve seen a lot of forum entries with similar issues, so I hope someone will have some input for me. It would save my Christmas holidays - and my sanity…

With hope and gratitude

Ingegerd

2

Subject: Export from Notes on web to Excel; data transfer problem

Try this thread to open your data in an excel sheet embedded in your browser.

http://www-10.lotus.com/ldd/nd6forum.nsf/55c38d716d632d9b8525689b005ba1c0/1fff07c24ce2a73685257024002e9415?OpenDocument

Shawn

3

Subject: RE: Export from Notes on web to Excel; data transfer problem

Sorry, but that is what I DON’T want to do. I need Excel to open in its own window with its full functionality, where it can also access the user’s environment.

4

Subject: RE: Export from Notes on web to Excel; data transfer problem

My Bad…

So you want an Excel instance to fire, as if you went to Start > All Programs Microsoft Excel or could it just open the sheet in the workspace?

Shawn

5

Subject: RE: Export from Notes on web to Excel; data transfer problem

The Excel template contains 40 worksheets with tables, pivot tables and graphs. There are about 50 VBA functions and subroutines. Yes, I want Excel to start in an own instance. I’ve tried to launch Excel in the browser, but then the macros will not work.

As I said, the Excel launch can be controlled with a Windows setting, but then it must be communicated to the users elsewise.

6

Subject: RE: Export from Notes on web to Excel; data transfer problem

Ingegerd,

Have you tried somehting similar to:

Dim excelApp As Variant

Set excelApp = CreateObject("Excel.Application")

excelApp.workbooks.open("C:\publicfolder\filename.xls")

excelApp.workbooks.save("C:\publicfolder\filename.xls")

excelApp.workbooks.close()

This way you can go ahead and import the data, save it and then use your print functions to open the ‘Good Workbook’ on the web and see the charts/tables.

Here is an MSDN link to the Excel COM API mehtods.

HTH,

Shawn