Solved

Excel spreadsheet in IE (did a Domino R5 Agent) - need to know how to have the agent create a second worksheet

Posted on 2006-06-14
9
435 Views
Last Modified: 2013-12-18
Agent name is "ExportCompleted.xls.  In the Initialize section of my agent, I have the following code:

Sub Initialize
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim excel As Variant
      Dim worksheet As Variant
      Dim cell As Variant
      Dim view As NotesView
      Dim nav As NotesViewNavigator
      Dim entry As NotesViewEntry
      Dim rowNum As Integer
      
      Print {Content-Type:application/vnd.ms-excel}
      Print {<HTML><HEAD>}
      Print {</HEAD>}
      Print {<BODY>}
      Print {<TABLE>}
      Print {<TR>}
      Print {<TD WIDTH="65"><B>AI Change No</B></TD>}
      Print {<TD WIDTH="100"><B>Status</B></TD>}
      Print {<TD WIDTH="90"><B>Date Rcvd</B></TD>}
      Print {<TD WIDTH="120"><B>Requestor</B></TD>}            
      Print {<TD WIDTH="150"><B>Platform</B></TD>}
      Print {<TD WIDTH="120"><B>Servers Impacted</B></TD>}
      Print {<TD WIDTH="160"><B>Applications Impacted</B></TD>}
      Print {<TD WIDTH="200"><B>Type of Change</B></TD>}      
      Print {<TD WIDTH="150"><B>Red Watch</B></TD>}
      Print {<TD WIDTH="160"><B>Start Date/Time</B></TD>}      
      Print {<TD WIDTH="160"><B>End Date/Time</B></TD>}                  
      Print {<TD WIDTH="80"><B>Chg Ticket Rcvd</B></TD>}                  
      Print {<TD WIDTH="90"><B>Risk</B></TD>}            
      Print {<TD WIDTH="140"><B>Mop/Backout Plan Received</B></TD>}      
      Print {<TD WIDTH="400"><B>Scheduling Status</B></TD>}            
      Print {<TD WIDTH="140"><B>NFC Created for REDWATCH Apps</B></TD>}            
      Print {<TD WIDTH="120"><B>Duration & Outage</B></TD>}            
      Print {<TD WIDTH="400"><B>Details of Change</B></TD>}            
      Print {<TD WIDTH="120"><B>Scheduling Prime</B></TD>}            
      Print {<TD WIDTH="150"><B>IBM/CSA Contact</B></TD>}            
      
      Print {</TR>}
      
      rowNum = 2
' Current row of data
      
   ' Get a NotesViewNavigator from our view
      Set db = session.CurrentDatabase
      Set view = db.GetView("AI_CRCompleted")
      Set nav = view.CreateViewNav
      Set entry = nav.GetFirst
      
       ' Go through all the entries in the view
      While Not entry Is Nothing
            Print {<TR>}
            Print {<TD>} & entry.ColumnValues(2) & {</TD>}
            Print {<TD>} & entry.ColumnValues(3) & {</TD>}
            Print {<TD>} & entry.ColumnValues(4) & {</TD>}
            Print {<TD>} & Cstr(entry.ColumnValues(5)) & {</TD>}
            Print {<TD>} & Cstr(entry.ColumnValues(6)) & {</TD>}
            Print {<TD>} & Cstr(entry.ColumnValues(7)) & {</TD>}
            Print {<TD>} & Cstr(entry.ColumnValues(8)) & {</TD>}
            Print {<TD>} & Cstr(entry.ColumnValues(9)) & {</TD>}
            Print {<TD>} & Cstr(entry.ColumnValues(10)) & {</TD>}
            Print {<TD>} & Cstr(entry.ColumnValues(11)) & {</TD>}
            
            Print {<TD>} & Cstr(entry.ColumnValues(12)) & {</TD>}
            Print {<TD>} & Cstr(entry.ColumnValues(13)) & {</TD>}
            Print {<TD>} & Cstr(entry.ColumnValues(14)) & {</TD>}
            Print {<TD>} & Cstr(entry.ColumnValues(15)) & {</TD>}
            Print {<TD>} & Cstr(entry.ColumnValues(16)) & {</TD>}
            Print {<TD>} & Cstr(entry.ColumnValues(17)) & {</TD>}
            Print {<TD>} & Cstr(entry.ColumnValues(18)) & {</TD>}
            Print {<TD>} & Cstr(entry.ColumnValues(19)) & {</TD>}
            Print {<TD>} & Cstr(entry.ColumnValues(20)) & {</TD>}
            Print {<TD>} & Cstr(entry.ColumnValues(21)) & {</TD>}
            
            Print {</TR>}
            
            rowNum = rowNum + 1
            Set entry = nav.GetNext(entry)
      Wend
      
      
      Print "</TABLE></BODY></HTML>"
      
End Sub

My questions is that I have another agent that outputs "Cancelled requests" using the same columns.  How can I have that report outputting to the same spreadsheet but in another worksheet when I run my agent?  

The code for the cancelled requests is the same except the view name is different.


0
Comment
Question by:mermaidink
  • 3
  • 2
  • 2
9 Comments
 
LVL 63

Accepted Solution

by:
SysExpert earned 250 total points
ID: 16908110
From WHat I can see, you are using HTML to create an excel compatible table.

I do not see the use of the variables defined

  Dim worksheet As Variant
     Dim cell As Variant

Anywhere in your code.

I would say that via HTML it will NOT be possible,  you will need to do it using either Excel VBA or COM objects in Excel or Notes.


I hope this helps !
0
 
LVL 18

Expert Comment

by:marilyng
ID: 16908168
Near as I can tell, you're creating your excel spreadsheet using html?  And by using the content type, you're using the excel viewer in a browser to display an excel spreadsheet.

Since you're not activating the excel object directly, you can't tell it to create a workbook and then another worksheet, only to create another html page.

Is there a particular reason you did it this way?  It is clever, I must admit :)

If you, instead, used the com objects, then adding a worksheet becomes really easy:

Something like this:

        Set xlApp = CreateObject("Excel.Application")
      xlApp.Visible = False 'hide Excel while processing
      xlApp.Workbooks.Add      
      xlApp.ReferenceStyle = 2
      Set xlSheet = xlApp.Workbooks(1).Worksheets(1)
      xlSheet.Name = "Lotus Export"
        Set xlSheet2 = xlApp.Workbooks(1).Worksheets(2)
        if xlSheet2 is nothing then
             Set xlSheet2 = xlApp.Workbooks(1).Worksheets.add
        end if
             xlSheet2.Name = "Another Name"
--------

But I can be wrong, so wait for someone else to decide if you can add another worksheet using html.. hmm.. you could if you printed out to xml, and defined the second worksheet in the xml style sheet..

That's all I have!

0
 
LVL 63

Expert Comment

by:SysExpert
ID: 16908295
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 18

Expert Comment

by:marilyng
ID: 16908518
Sorry, Mike, I didn't refresh when I posted my comments, so yours weren't there.  

I like Qwaletee's comment about the excel export posted at Q_20819577 :  "That's some messed up code..."  ;)

A Nice. clean example: http://www.bluestream.org/Domino/ExportToExcelViaNotes.htm

Here are others:
http://www.experts-exchange.com/Applications/Email/Lotus_Notes_Domino/Q_21826566.html
http://www.experts-exchange.com/Applications/Email/Lotus_Notes_Domino/Q_21828050.html

Multiple Worksheets:
http://www.experts-exchange.com/Applications/Email/Lotus_Notes_Domino/Q_20255907.html

Regards!
0
 

Author Comment

by:mermaidink
ID: 16911317
The code I attached does work. It creates an excel sheet in the browser.  I will have to look at those links in your comments and see  how I can alter the code.
0
 

Author Comment

by:mermaidink
ID: 16911766
From a link given by marilyng, I got to this page.  My users are on the web and don't have Notes.  This is why I wrote the initial one in HTML.  
Would this type of solution (link below) have a coding ability to do multiple worksheet when exporting to excel via a web browser?
http://www.bluestream.org/Domino/ExportToExcelViaWeb.htm

I also looked closely at Q_20255907 and didn't know if that was for exporting to excel to the WEB...

Can someone clarify?


0
 
LVL 18

Assisted Solution

by:marilyng
marilyng earned 250 total points
ID: 16913112
No, none of the links I posted, nor sysExpert's links will export to web.  You would have to use the link examples to create your Excel objects and export Notes to excel, then use the Excel methods and properties to export  EACH worksheet to  an html file.  You can probably do this all in Lotus Script.

Question - Why not just flash the Notes form or view on the web?  users can be assigned an html name and password in a secondary address book, and can hit the web page via a browser using the internal server name:  http://notesserver/database.nsf/Viewname?OpenView

The server has to be running the http task, and port 80 needs to be available to the internal users.  (So, you don't have to enable port 80 externally if you don't want to)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now