logo

Keeping the boss happy

Reporting. That one word is enough to put the fear in me. If I had a pound for every time I'd told somebody that "Notes isn't too hot when it comes to reporting" then I'd probably be out with my mates tonight instead of sitting at home writing this....

Did anybody ever use Lotus Notes Reporter 2. That one piece of software caused me more nightmares than the time I saw V, late at night, alone and young when a lady gave birth to a lizard-tongued baby.

Luckily it's not very often I get asked to create reports. If ever I do then I usually opt for the foolproof method of printing all the data back to the browser in the form of a table. The problem being that your code needs to account for every whim of the manager and all the formats they "need" to see the data in. Can get very messy, very quickly.

The last time I got asked to do some reporting it made me think of something Chris King said. That then led me to do some investigating. What I found was very interesting indeed. Writing the reporting side of an application is almost like having fun now.


What I did:

First thing was to test the theory. To do this I created a simple agent with code similar to the following and ran it from the browser.

Print {Content-Type:application/vnd.ms-excel}
Print {Hello Excel}

Sure enough, this opened the Excel ActiveX object within the browser and displayed the data. At this point I realised this was going to change the way I develop Web Applications forever.

image

Next thing I tried was to send the data in the form of an HTML formatted table.

Print |Content-Type:application/vnd.ms-excel|
Print |<table border="1">
<tr><td width="80">Quarter</td><td>Sales</td></tr>
<tr><td>1</td><td>£10,000</td></tr>
<tr><td>2</td><td>£12,000</td></tr>
<tr><td>3</td><td>£18,000</td></tr>
<tr><td>4</td><td>£30,000</td></tr>
</table>|

Which produced:

image

Excited yet? I was. Just imagine what you can do now. All you need do is send the data to the browser and then the user can do all the work, formatting the data as they like. They can save it as an .XLS file to their hard-drive. They can also use things like AutoFilter to better view the data. Anything they want really.

You can also do this with Word and Powerpoint. The oppurtunities being almost endless.


Producing the spreadsheet:

Assuming the data you are sending to the browser is held in Notes documents that are held in views you can use code similar to the following to produce dynamic tables.

Dim db As NotesDatabase
Dim qs As NotesView
Dim nv As NotesViewNavigator
Dim ne As NotesViewEntry
Set qs = db.GetView("QuarterlySales")
Set nv = qs.CreateViewNav
Set ne = nv.GetFirst

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

Print |<table border="1">
<tr><td width="80">Quarter</td><td>Sales</td></tr>|

While Not(ne Is Nothing)

Print|<tr>
<td>| + ne.ColumnValues(1) + |</td>
<td >| + ne.ColumnValues(2) + |</td>
</tr>|

Set ne = nv.GetNext( ne )
Wend


Now the data in the spreadsheet is truly dynamic. How impressed the boss will be.


Taking it further:

The second of the above two examples is still quite limiting. What we are going to need to do to make the boss really happy is make it look pretty and add extra features.

To find out how one would go about this I first took a basic example and added some formatting and a sum formula.

image

With this done, I saved the spreadsheet using the "Save as Web Page" option in Excel. Here is the result. Look at the source to this page and you start to get some ideas about how to format cells as number/currency and add formulas to cells. To add the "total" row in the above code we need the following line:

Print |<tr><td>Total</td>
<td x:fmla="=SUM(B2:B5)"><b></b></td>
</tr>|

In applications where you don't know that there are 4 rows you will obviously need to add the logic to work out the number of rows and alter the above formula. You also need the lines at the top of the file that define the x namespace.


Something to note:

One problem I came across was IE getting confused about what it was presenting. To get round this probem I found it was best to end all Agent names with ".xls" so a typical URL would end in /db.nsf/Table.xls?OpenAgent. This fools IE in to believing it really is an Excel file.

Have fun....


Feedback

    • avatar
    • Kurt
    • Mon 10 Dec 2001

    Not working

    I have a problem : the browser doesn't start the activeX object. He returns (correct) simple HTML code in de browser.

    Any idea

    1. Re: Not working

      I have to admint that I only tested this on my PC with IE6 and Office 2000. Which version of Excel have you got installed?

      Did you name the agent "somthing.XLS"?

      Have you tried opening a real Excel file in the browser to see if that opens in ActiveX or prompts for a download?

      This is probably going to be a problem with the config of your machine. So far I've had no complaint from people whose projects I've used this on...

    2. Web Components

      Isn't it Web Components that you use? In that case, 2000 is required.

      The View wrote about the components in the [<a href="http://www.eview.com/eview/viewr5.nsf/fe1e41a7625f757e8525653600105ec5/c99 07bb04e3c854c85256a8600503e6f?OpenDocument">July/August issue</a>].

      Show the rest of this thread

      • avatar
      • Richard Sampson
      • Wed 12 Dec 2001

      Re: Not working

      Are you sure that the VERY FIRST print statement contains the mime-type?

      If it isn't then the browser will assume normal html text & display accordingly.

    3. Re: Not working

      Little late to the party here.. not sure if this is in the above thread; but Ive been using this for a while - just wanted to say; this won't work if your agent name has underscores in it! Just FYI ;-) Hope this helps someone who was as frustrated as I !!!

    4. Moreover, the seeds stay dormant under unfavorable environmental conditions, avoiding direct competition with rivals who are favored by the same environment, and thus diminish the impact of competition in bad years. , <a href="http://ewzhff.mebywem.co.cc/www.vbfunkeys.com.html">www.vbfunkeys.com</a>, [url="http://ewzhff.mebywem.co.cc/www.vbfunkeys.com.html"]www.vbfunkeys.com[/url], http://ewzhff.mebywem.co.cc/www.vbfunkeys.com.html www.vbfunkeys.com, 646,

  1. open a view in excel

    I have read this some time ago (http://www.dominopower.com/issues/issue199901/domexcel001.html), and I've found more interesting.

    Briefly, you can read an HTML table in excel, then a web view, with the web query tool. You can deploy this with a excel file, template, or a .iqy file, wich contains the query parameter.

    If you embed the view in a form, and write a table row with formulas like "=sum(a1:a100)", then you have totals, without write a LotusScript agent.

    I love this tool, is a perfect example of power and semplicity.

    bye Francesco

    PS: Lotus 1-2-3 also has a similar facility. PPS: great site, Jack, I love you.

  2. HTML Reports

    Jake,

    As an alternative to a Dynamically generated Excel report, why not just create an HTML report. The advantage would be that for users without Excel they could still generate a "report" to read online, take to meetings, etc.

    Jack

    1. Re: HTML Reports

      Yes, you could just write the data out to HTML. I think that your missing that fact that you could program in Excel formulas. You can then calculate a ton of information that is kinda of a pain to do in Notes. Don't get me wrong it can be done but you have to loop and keep the data in variables while calculating it. I like this approach.

    2. Re: HTML Reports

      This technique also allows the user to control and modify the output, such as filtering, sorting, adding/removing columns, etc. This certainly could be done in HTML, but it puts the user at the developer's mercy. (I hate writing 8 different reports when all that's changing is the sort order!)

      Thanks Jake!!!

      Show the rest of this thread

  3. Launching Excel in-place

    Print {Content-Type:application/vnd.ms-excel} Print {Hello Excel}

    The simple code (above) acts differently depending on how the operating system is set-up to handle XLS files. It appears the a machine with IE6 installed always launches XLS files in-place within the browser unless told otherwise, however IE5.x does not always use in-place launching and instead launches the full blown excel app.

    To fix this in NT (I assume this is similar in 9x). Open explorer (not internet explorer) and goto Tools\Folder Options\File Types. Scroll down the file type list until you find "Microsoft Excel Worksheet" with an extension of "XLB XLS" and click "Edit". The Edit dialog has a check box called "Browse in same window" make sure this is checked!

    When checked XLS file URLs will be loaded into the browser window and not launched separately.

    1. Client side launch?

      Hi Jake,

      Tremendous article! Very inspiring!

      I wonder if you see any way to accomplish this effect without the involvement of the web server (Domino) and without having to call a web-based agent.. that is: is there a way to generate the excel/html file by a "user agent" in the Notes client, store the file temporarily and then open it in the browser with all the great features mentionned above in you article in effect? I was thinking maybe OLE embedding and activating, but it seems an inelegant (if even possible) way of doing..

      My goal is to only perform one lookup on the server and then locally produce the result to save the servers power.

      Possible - you think?

      Kind regards Folke

  4. Doing this Using Web Components

    N.B. To use web components you need IE5+, Office 2000/XP (to develop) or XP Components for viewing. XP Components version 10 are available for download at Microsoft.com if you have clients without office 2000+.

    http://office.microsoft.com/downloads/2002/owc10.aspx?HelpLCID=%HelpLang%1033

    As I pointed out the original example in this thread launches excel in-place and doesn't use Web Components as some have suggested. So I have dug out an example of how to do the same thing (or similar) using components. Using components has several advantages and disadvantages but there is one very nice feature... read on.

    Firstly: i'll reproduce the "Hello Excel" example using the excel web component. Use the following print statements in an agent and call the agent from the web. The agent name can be anything and does not require the .xls name as you are not trying to get a full blow Excel session running.

    Print {<object id=component1 classid=CLSID:0002E510-0000-0000-c000-000000000046 style="width:49%;height:350"></object>}

    Print {<script language=vbscript>} Print {dim osheet} Print {set osheet = component1.ActiveSheet} Print {osheet.range("A1:A1").Value = "Hello Excel Web Component"} Print {</script>}

    This launches the default component and sets the first cell value. The first line embeds the component (an activeX control) using its class ID. If the machine loading the page does not have the component registered (i.e. installed) the activeX control will not display. There are many many ways to configure the component once it has loaded e.g.

    Print {component1.TitleBar.Caption = "This is a CodeStore test"}

    check out the MSOffice VBA component documentation for a full method reference.

    So whats the advantage? Well you can format the page around the component for one and the component is light-weight but a major advantage is the DataType property of the sheet component. This property has a type of HTMLURL, sounds interesting, and in operation it is amazingly simple!!!

    Print {component1.HTMLURL = "url to data"}

    Heres the cruncher... Just set the URL to a Notes View and your view will appear in the component!!! The code is below.

    Print {osheet.cells.clear} Print {component1.DataType = "HTMLURL"} Print {component1.HTMLURL = "/my database/myview?OpenView"}

    However there are some caveats. The URL must be to the same sever that the component is loaded from, i.e. the same server as the database that is running your agent. And the view must have no formatting. The best way to do this is to create a view template for the view and have nothing in it but a $$ViewBody field.

    Doing this is similar to doing a "New Web Query" from within Excel.

    Well... there you go... simple... and the automation possibilities are huge. Mix this with the pivot table and chart components and the boss will be really happy.

    Make good use of it...!!

    Mike http://www.notestips.com

      • avatar
      • ajay
      • Tue 26 Nov 2002

      Re: Doing this Using Web Components

      Hi I tried the script that Mike Golding mentioned, It worked fine, was able to get the view in an excel sheet, The problem was that only upto 31 entries were captured Any ideas?

      Show the rest of this thread

    • avatar
    • Jetze van der Wal
    • Fri 28 Dec 2001

    An alternative

    The File Open command allows users to open url's instead of files only. If i enter "http://myserver/mydb.nsf/myview?Openview&Start=1&Count=999" i download the data without any problems in Excel 2000/97.

    Using VBA script i've developed an Excel sheet that downloads all the data (entries 1 to 10000 or more). You could then add code to automatically create diagrams, do analysis etc. The Excel sheet can, of course, be downloaded once and re-used until a new version is required.

    • avatar
    • jay
    • Sun 30 Dec 2001

    export to excel from notes using browser Domino R5

    This is fine.

    I can create views in notes and xreate an excel file, but i need to store the final xls file onto local disk of user ?

    Using browser it always assumes the local disk as that of user.

    can you help me to solve this problem

  5. Combined this with an agent I use to export CSV

    Hope this formats correctly. If not, its going to be on my web page at http://www.bluestream.org.

    I had an agent that I used to create a .csv file directly off of the web browser. I've converted it here. This is completely generic, and will dump any view listed without requiring conversion. I've added code here to properly format numbers and dates, which you can modify to your tastes. Just make sure you change the view name as necessary.

    What I normally do is create a hidden data dump view with all the raw data. That way when people ask for reports (like most people know what it means anyways,) I give them the benefits of the data dump, so they can create their own custom reports on the fly without waiting for a developer! This usually either pacifies them or confuses them enough to let me get back to my game...

    'Generate Report|report.xls:

    Option Public Option Explicit %INCLUDE "lsconst.lss" %INCLUDE "lsxbeerr.lss"

    Sub Initialize

    'This agent creates a excel file and dumps it directly to the web browser. 'The browser interprets it as a file. 'The script runs line by line, first taking the view column headers, then 'all the data itself. Please make sure you update the view name. 'It is currently ExportView

    Dim session As New NotesSession Dim db As NotesDatabase Dim v As NotesView Dim docX As NotesDocument Dim col As Integer Dim lineitem As String Dim View As String Set db = session.CurrentDatabase

    'Sets the download to use Excel Print |Content-Type:application/vnd.ms-excel| 'Triggers the save/open prompt instead of embedding the spreadsheet in the browser Print |Content-Disposition:Attachment; filename="Report.xls"| On Error Goto errorHandler

    View="ExportView" Set v = db.GetView(View$) Call v.refresh col=1 Print |<Table border>| lineitem="" Forall vColumn In v.Columns If col < 1 Then Elseif col=1 Then lineitem=|<th align="center"><FONT SIZE=3 COLOR="000000">|+vColumn.Title Else lineitem=lineitem+|<th align="center"><FONT SIZE=3 COLOR="0000FF">|+vColumn.Title End If col=col+1 End Forall lineitem=lineitem

    Print lineitem Set docX=v.GetFirstDocument lineitem="" While Not docX Is Nothing col=1 Forall cValue In docX.ColumnValues If col=1 Then lineitem=|<tr>| End If If cValue="" Then 'blank value still formats the cell lineitem=lineitem+|<td>&nbsp;</td>| Elseif Isdate(cValue) Then 'date format lineitem=lineitem+|<TD ALIGN="right" STYLE="vnd.ms-excel.numberformat:dd-mmm-yyyy">|+cValue+|</td>| Elseif Isnumeric(cValue) Then If (v.columns(col-1).numberformat=3) Then 'currency format lineitem=lineitem+|<td ALIGN="right" STYLE="vnd.ms-excel.numberformat:$#,##0.00">|+cValue+|</td>| Else 'other number format lineitem=lineitem+|<td ALIGN="right">|+cValue+|</td>| End If Else 'Plain text format lineitem=lineitem+|<td>|+cValue+|</td>| End If

    col=col+1 End Forall Print lineitem+|</tr>| Set docX=v.GetNextDocument(docX) Wend Print |</table>| Exit Sub

    errorHandler: Print "There has been an error " & Err() & " : " & Error() & " - On Line "+Cstr (Erl) & Chr$(13) Exit Sub

    End Sub

      • avatar
      • Aodhaidin O'Shea
      • Wed 12 Jun 2002

      Re: Combined this with an agent I use to export CSV

      This is absolutely exactly what I needed!!!! Thank you so much!!!

      • avatar
      • leon
      • Sun 10 Nov 2002

      Re: Combined this with an agent I use to export CSV

      Chad,

      We ran into a wall recently with exporting views to excel. Then we noticed your code:

      'Generate Report|report.xls:

      Option Public Option Explicit %INCLUDE "lsconst.lss" %INCLUDE "lsxbeerr.lss"

      Sub Initialize

      'This agent creates a excel file and dumps it directly to the web browser. 'The browser interprets it as a file. 'The script runs line by line, first taking the view column headers, then 'all the data itself. Please make sure you update the view name. 'It is currently ExportView

      Dim session As New NotesSession Dim db As NotesDatabase Dim v As NotesView Dim docX As NotesDocument Dim col As Integer Dim lineitem As String Dim View As String Set db = session.CurrentDatabase

      'Sets the download to use Excel Print |Content-Type:application/vnd.ms-excel| 'Triggers the save/open prompt instead of embedding the spreadsheet in the browser Print |Content-Disposition:Attachment; filename="Report.xls"| On Error Goto errorHandler

      View="ExportView" Set v = db.GetView(View$) Call v.refresh col=1 Print |<Table border>| lineitem="" Forall vColumn In v.Columns If col < 1 Then Elseif col=1 Then lineitem=|<th align="center"><FONT SIZE=3 COLOR="000000">|+vColumn.Title Else lineitem=lineitem+|<th align="center"><FONT SIZE=3 COLOR="0000FF">|+vColumn.Title End If col=col+1 End Forall lineitem=lineitem

      Print lineitem Set docX=v.GetFirstDocument lineitem="" While Not docX Is Nothing col=1 Forall cValue In docX.ColumnValues If col=1 Then lineitem=|<tr>| End If If cValue="" Then 'blank value still formats the cell lineitem=lineitem+|<td>&nbsp;</td>| Elseif Isdate(cValue) Then 'date format lineitem=lineitem+|<TD ALIGN="right" STYLE="vnd.ms-excel.numberformat:dd-mmm-yyyy">|+cValue+|</td>| Elseif Isnumeric(cValue) Then If (v.columns(col-1).numberformat=3) Then 'currency format lineitem=lineitem+|<td ALIGN="right" STYLE="vnd.ms-excel.numberformat:$#,##0.00">|+cValue+|</td>| Else 'other number format lineitem=lineitem+|<td ALIGN="right">|+cValue+|</td>| End If Else 'Plain text format lineitem=lineitem+|<td>|+cValue+|</td>| End If

      col=col+1 End Forall Print lineitem+|</tr>| Set docX=v.GetNextDocument(docX) Wend Print |</table>| Exit Sub

      errorHandler: Print "There has been an error " & Err() & " : " & Error() & " - On Line "+Cstr (Erl) & Chr$(13) Exit Sub

      End Sub

      We tried this but it didn't work. The problem we have is that two of our columns have multi values in them. We are both new to notes development and cannot seem to find any step by step documentation/code for dealing with this. Your code works fine apart from the two columns. How would you incorporate a multivalue handler in your routine? Can you please help?

      Can you please reply to BOTH cc addresses?

      Many Thanx,

      Leon

      Show the rest of this thread

    1. Re: Content-type:application/vnd.ms-excel

      Hi i would like to add one more worksheet in the above created EXCEL workbook. is it possible? if so, how to create ? thanks guys

      Show the rest of this thread

    2. Re: Combined this with an agent I use to export CSV

      Chad Amberg;

      I tried your method but did not work.. 1. I copied it to an agent and name it "(sample)" - properties are: A. it's Triggered on Event B. Agent List Selection C. All documents in view

      2. have a web enabled view and created an action button with formula @command([toolsRunMacro];"(sample)");

      3. then i launched the view in the WEB using Internet Explorer...(view in Web)

      4. I click the button to run the agent..

      Nothing happend why? can you help with this? PS : i change this part off course with the view i want it to search

      View="ExportView" -- "MyView" Set v = db.GetView(View$)

  6. Report

    Hi I think we need not specify application in the following syntax. Print |Content-Type:application/vnd.ms-excel|

    Just we can specify Print {Content-Type: text/vnd.Ms-Excel} is enough

    if u want to print in Msword just u add Print {Content-Type: text/vnd.Ms-Word} Agnet name should be Agentname.doc bye jana

    1. Saving the excel doc

      Bloody Brilliant. I love this site and the help it's given me.

      The excel thing works perfectly. However, I now need to be able to save this as an excel workbook, but can't get it to work. Any ideas? Also, If I want to bring in the excel toolbars? How?

      Thanks.

      Show the rest of this thread

  7. Boggles the Mind

    The possibilities inherent in the above article boggle the mind. Once you've read it, it's a case of - of course that's possible! But I'd never have thought of it :)

    Brilliant as usual - I can use this to generate automatice spreadsheets via html/xml just using views and viewtemplates.

    Ingenius.

  8. Domino Security

    Has anyone tested how Domino Security effects this - say someone points their Excel at a URL, and the database needs a username and password - does this cause it to fail, or ask for the password and continue.

    If it still works, then external people could point there Excel spreadsheets at a view/view template that created an invoice?

      • avatar
      • Jonathon Llewellyn thomas
      • Fri 17 May 2002

      Re: Domino Security

      Hi All

      From my painful experience I would suggest that Domino Security does apply to this. I was working on producing an Excel spreadsheet from a log view so Tutors (it's an educational application) could see who has been contributing etc...

      However, to save time, I was working on a local copy and wondering why nothing was appearing in Excel when the debugger showed it working fine.

      After many hours, it suddenly dawned that the log form is controlled by Reader fields (to allow student to see a list of documents they have read, whilst Tutors see the whole picture). Of course, when testing offline (ie not logged in) it was returning no records in the browser (of course the debugger was fine in the client).

      Anyway, despite my own, self-inflicted problem, this is a great tool. As Jake said, we can now give end users the data in a format they are familar with and let them analyse it at will (and stop bugging us!).

      Regards All. Jonathon

    • avatar
    • Sam
    • Fri 18 Jan 2002

    Column formatting

    Does anyone out there know of a way to programmatically format a column as currency in Excel? I'm able to create an Excel spreadsheet, but apparently the data must be in string format to be exported, and it is printed as such in the Excel spreadsheet. Formatting the spreadsheet's column as a currency column after data export seems to be the answer, but I don't know the proper command to do this. Any suggestions?

      • avatar
      • Jake Howlett
      • Tue 22 Jan 2002

      Re: Column formatting

      As I said in the article you can do this buy going in to Excel and creating a spreadsheet with a curreny column and then saving it as HTML. Open the HTML in a text editor and look how Excel denotes this column. Use the same notation in your code..

      Jake -codestore

      Show the rest of this thread

      • avatar
      • PAL
      • Fri 18 Oct 2002

      Re: Column formatting

      Try this

      Print {<tr><td>}&Format(Round(Field,0),"Currency")&{</td></tr>}

      Field is the variable or you could use the doc.field(0) if you have not set it to a variable.

      Hope this helps

      PAL

  9. Works on one server but not another

    Coded an agent using this info (which is incredibly helpful-thank you), but now I have a problem. I have two servers - one running the Domino HTTP task, the other that is using the IIS HTTP stack. When I run the agent on the server running HTTP the agent runs perfectly. Agent also works on my local machine. However, when I try running it on the Domino/IIS server (OS is Win NT) it hangs. I have administrator rights to the server and ability to run all agents. Anyone have any idea why it would hang? Nothing is being logged in my agent error logging or Notes log.

    1. Re: Works on one server but not another

      Fixed - just needed to add a MIME setting for the application in the IIS Administrator.

  10. Saving the excel doc

    Bloody Brilliant. I love this site and the help it's given me.

    The excel thing works perfectly. However, I now need to be able to save this as an excel workbook, but can't get it to work. Any ideas? Also, If I want to bring in the excel toolbars? How?

    Thanks.

      • avatar
      • Paul Graham
      • Thu 7 Feb 2002

      Re: Saving the excel doc

      Add the following line immediately before the table ... Content-Disposition:Attachment; filename="Report.xls"

      This brings up the Download File Dialog, with the option to save as or open in a new window with the full set of toolbars and menus.

      Show the rest of this thread

    • avatar
    • Ray
    • Thu 14 Feb 2002

    how is the agent called ?

    Hi Jake,

    The code is pretty slick ..

    It works great but i do have a problem ..When i call this agent from a link or a button on a page the browser prompts you to save the page that you are currently on ..or if you try to open this file form its current location it tries to open the page you are on and then prompts the second time but now with the agent that you are trying to run

    The agent is named test.xls and runs fine if i key in the url in the address bar of the browser

    The agent is set to manually run from agent list

    Is there something that i am missing here ?

    thanks

    ray

    1. Re: how is the agent called ?

      Thanks Sooooooo Much ! This made my day!

    • avatar
    • Tim
    • Thu 14 Feb 2002

    Problem getting the column total to work

    I have everything working the way you described in your article. But I can't get the total to work. Here is the code I have to accomplish this, Print |<tr><td>Total</td><td></td><td></td> <td x:fmla="=SUM(D2:D10)"><b></b></td> </tr>|

    and here is what it generates if you look at the reports source code: <td class=xl25 width=49 style='border-top:none;border-left:none;width:37pt' u1:fmla="=SUM(D2:D10)">&nbsp;</td>

    I'm not sure what is going on, I would appreciate any tips you may have.

    Thanks for your article!

    1. Re: How to Hide Column Grand Total

      Is there a way to hide the grand total? Thanks in advance.

      Show the rest of this thread

    2. Login screen appears when exporting to excel

      Hi All, This discussion thread is of great value to me. well i am facing a tricky problem. I am calling an agent (xxx.xls?openagent) and i have some parameters passed as query_string along with the agent call. Now when my parameter(which is a field value) has no space in it, then the data is getting exported to excel like charm. but when the parameter has an space (%20) then it opens up excel sheet but the sceen is filled with the initial login screen. any idea..why i am breaking my head.. any clue will solve my problem.

    3. Jack Please Respond ..

      Is there any way to create excel file based on predefined template?

      I have used this successfully in the past, now there is an immediate need to create an excel file based on the template.

      Thanks

      Show the rest of this thread

    4. Here's my take on the code to do this

      Summarized a couple items from this thread also added a "content-disposition" tag which will launch the file in Excel (not excel within the browser) - so you don't need to name your agent ".xls".

      This will work with any view - replace GetCGIParm("VIEW") with the name of the view you are using.

      Sub Initialize On Error Goto Handler Dim s as New NotesSession Dim db_Current as NotesDatabase Dim vw_Export As NotesView Dim nv As NotesViewNavigator Dim ne As NotesViewEntry Dim i_CurColumn As Integer Set db_Current = s.CurrentDatabase Set vw_Export = db_Current.GetView(GetCGIParm("VIEW")) Set nv = vw_Export.CreateViewNav Set ne = nv.GetFirst Print |Content-Type: application/vnd.ms-excel| Print |Content-disposition: attachment; filename=| & GetCGIParm("VIEW") & |.xls| Print |<table border="1">| Print |<tr>| Forall c In vw_Export.Columns If Not c.IsHidden Then Print |<td width="80"><b>| & c.Title & |</td>| End If End Forall Print |</tr> | While Not(ne Is Nothing) Print|<tr>| i_CurColumn = 0 Forall c In vw_Export.Columns If Not c.IsHidden Then If Not ne.IsCategory And c.IsHideDetail Then ' TOTAL COLUMN AND HIDE DETAIL - PRINT BLANK CELL Print |<td></td>| Elseif c.TimeDateFmt = 0 And Isdate(ne.ColumnValues(i_CurColumn)) Then ' FORMAT DATE ONLY COLUMNS Print |<td>| & Format$(ne.ColumnValues(i_CurColumn),"mm/dd/yy") & |</td>| Elseif Isarray(ne.ColumnValues(i_CurColumn)) Then ' FORMAT MULTI-VALUE COLUMNS Print |<td>| & Join(ne.ColumnValues(i_CurColumn),"<br>") |</td>| Else Print |<td>| & ne.ColumnValues(i_CurColumn) & |</td>| End If End If i_CurColumn = i_CurColumn + 1 End Forall Print |</tr> | Set ne = nv.GetNext( ne ) Wend Exit Sub Handler: Error Err, Error & { //} & Getthreadinfo(1) & {:} & Erl End Sub

  11. Problems with the while loop...

    Hi Jake. Very cool and dynamic this code:)

    I have a problem thou: The while loop is stopping before it reaches the end of the view.

    While Not(ne Is Nothing) Print|<tr><td align ="left">| + ne.ColumnValues(0) + |</td></tr> Set ne = nv.GetNext( ne ) Wend

    Do you have any idea of what the problems can be?

    Thnx for your help

    1. Re: Problems with the while loop...

      It's a great reporter:) I've propably figured it out why it stopped, could it be that the data format in one column is a number field in the notes dokument??

      In that case how could i convert the column value to a string value in the loop?

      Thnx

      Show the rest of this thread

  12. Set column propertie values in Excel

    Very nice app this is, it works like a baby:)

    I wonder if there is a way to set the properties to handle text variables, like "0002", if u export this straigth into a excel sheet the three 000 is missing.

    Can u set the properties with lotus scripting?

    Thnx in beforhand:)

    1. Re: Set column propertie values in Excel

      Off the top of my head, you could add a number format to the cell's value:

      cell_object_ref.NumberFormat = "0000"

      which should display the number 12 as 0012.

      Alternatively, you could add an apostrophe at the beginning of the cell's value:

      cell_object_ref.value = " '0012 "

      I haven't tried the latter in vba/script but should be ok.

      Pat Read

      Show the rest of this thread

  13. Works also in Netscape Navigator

    This approach is really nice. Besides IE, it also works in Netscape Navigator 4. I couldn't get it running in Opera 5.12 though.

  14. Works for RTF also

    I've found that this same technique works for RTF files, and gives a lot of the benefit of PDFs (cross-platform, mostly standardized formatting) without the overhead or giving $$ to Adobe. Since RTF files are made up of ASCII, we can generate them from LotusScript.

    The MIME Type entry for RTF is Application/rtf. Here's the text from a simple RTF file:

    {\rtf1\ansi\ansicpg1252\deff0\deftab720{\fonttbl{\f0\fswiss MS Sans Serif;}{\f1\froman\fcharset2 Symbol;}{\f2\froman\fprq2 Book Antiqua;}{\f3\froman Times New Roman;}{\f4\fswiss\fprq2 Arial;}} {\colortbl\red0\green0\blue0;} \deflang1033\horzdoc{\*\fchars }{\*\lchars }\pard\fi360\plain\f4\fs20 This is an RTF file using the Arial and \plain\f2\fs20 Book Antiqua fonts, with one indented paragraph. The following lines of the paragraph are not indented.\plain\f4\fs20\b \par }

    To see what this looks like, copy and paste the text to your favorite text editor and save the document with a .rtf extension. Then open it in an RTF reader such as Notepad or Word.

    A lot of this text is standardized information, such as the RTF header, the font table, et cetera. I'm betting that a lot of this could be placed in subroutines. Then you could call the sub(s) to start your document and use RTF to format it.

  15. A link to the RTF specification

    I found a copy of the [<a href="http://www.biblioscape.com/rtf15_spec.htm">RTF 1.5 Specification</a>] at [<a href="http://www.biblioscape.com ">Biblioscape.com</a>].

    1. Re: A link to the RTF specification

      Cheers Keith.

      Very interesting. I may well have to investigate this further ;-)

      Thanks for doing what I wish a lot more of my visitors would do and returning valuable content...

      Jake -webmaster

  16. Agent Done? - Again

    I am still looking for a solution to a previously reported problem (Agent Done? - Jason, 12/13/01):

    Static text will open in excel but when I add the following lines:

    Dim db As NotesDatabase Dim qs As NotesView Dim nv As NotesViewNavigator Dim ne As NotesViewEntry Set qs = db.GetView("ByStore") Set nv = qs.CreateViewNav Set ne = nv.GetFirst

    I get "Agent Done" after running the agent on the Web.

    The view "ByStore" exists.

    Please help I would really like to use this method for writing reports to Excel.

    Thanks!

      • avatar
      • Eric
      • Fri 29 Mar 2002

      Re: Agent Done? - Again

      Hello,

      The db object is not set in the example... This one is ok :

      Dim sess as new NotesSession ' added this line Dim db As NotesDatabase Dim qs As NotesView Dim nv As NotesViewNavigator Dim ne As NotesViewEntry Set db = sess.CurrentDatabase ' added this line Set qs = db.GetView("ByStore") Set nv = qs.CreateViewNav Set ne = nv.GetFirst

      Show the rest of this thread

  17. open an excel template and report

    Hi Jake

    this is a great site and has helped me heaps. well done.

    Print {Content-Type:application/vnd.ms-excel} Print {Hello Excel}

    is there any way that we can force it to use an excel template.

    Open a template and then fill in all the things that we want from a notes document.

    thanks mate

      • avatar
      • jey
      • Tue 11 Jun 2002

      Re: open an excel template and report

      Have a look at POI from jakarta.apache.org - you don't need to know much java (or have any MS code installed) to generate and read spreadsheets

      jey

      Show the rest of this thread

  18. THANK YOU!

    I just want to say a big "Thank You" for posting this thread. It has helped me tremendously. I got it to work, and added the ability to pass a parameter using QUERY_STRING.

    Thanks!!!!

    • avatar
    • Jonathon Llewellyn Thomas
    • Fri 17 May 2002

    Use with MS-Word?

    Hi All

    I've been using this excellent application with Excel and was wondering how to get it going with Word.

    I've tried:

    {Content-Type: text/vnd.Ms-Word}

    but this always prompts me with the 'open with' dialogue box. If I select MS-Word it works fine.

    Any suggestions on how to get it not to show the dialogue box?

    Cheers Jonathon

    1. Re: Use with MS-Word?

      How your browser handles Office attachments depends mainly on your system settings. Open Windows Explorer and from the Tools menu select Folder Options. On the File Types tab, locate Microsoft Word Document and choose Edit, as in the image below.

      Notice the four checkboxes at the bottom. Un-check the two on the right and try your link again.

      These settings are more than likely somewhere in the Registry. Where, I do not know.

      If that still doesn't work, try changing the MIME type to be the same as the one that shows up in the Content Type field of the dialog below:

      [<br clear="all" /><img src="rsrc/incgifs18/$file/imginc169.gif" alt="image" border="1" />]

      Jake -codestore.net

      Show the rest of this thread

    • avatar
    • Jonathon Llewellyn Thomas
    • Mon 20 May 2002

    Exporting selected documents only... Example

    Hi All

    I've been using this export method quite a lot recently and found it and excellent tool. One of the things I decided to do was allows users to export selected document only (from a Discussion View) to MSWord.

    The first thing I did was to search various Domino sites until I found an existing method of processing selected documents on the web (much easier than writing my own!). Anyway, much of this code I have taken from Notestips.com and the original code can be found here:

    http://www.notestips.com/80256B3A007F2692/0/F6B360E79988B37780256B7800809555?Ope nDocument

    I've just added a few feature to allow the export to Word (including how to handle RichText etc.. and a simple search and replace feature (again, 'borrowed' from Notes.net).

    Anway, I figure some people may find this useful as an example.

    i. follow the instructions from the notetip page (or download the sample database).

    ii. my code for the script library is as follows (agent remains the same).

    Sub ProcessMultiple(opWebDocument As NotesDocument) On Error Goto SubError ' ******** OBJECTS ************************************* Dim oSession As NotesSession Dim oDatabase As NotesDatabase Dim oSelected As NotesDocument Dim oRTItem As Variant Dim PlainText As String Dim CreateDate As Variant Dim todaysDate As String Set oSession = New NotesSession Set oDatabase = oSession.CurrentDatabase ' ******** VARIABLES *********************************** Dim vLoop As Long Dim vHTML As String Let vLoop = 0 todaysDate=SearchReplace(Date$,{/},{_}) ' ******** PROCESS SELECTED DOCUMENTS ****************** vHTML = vHTML + {<FONT Face="Arial" SIZE="4"><B>General Discussion Area</B></FONT><BR><BR>} vHTML = vHTML + {<TABLE STYLE="font : 8pt Verdana">} Print{Content-Type:application/msword} Print {Content-Disposition:Attachment; filename="DiscussionDocuments_} + todaysDate + {.doc"} Forall DocID In opWebDocument.CheckBox Set oSelected = oDatabase.GetDocumentByUNID(DocID) CreateDate = oSelected.Created vHTML = vHTML + "<TR><TD><B>" + oSelected.subject(0) + {</B></TD><TR><TD>} vHTML = vHTML + oSelected.From(0) + { - } + Cstr(CreateDate) + {</td></tr>} Set ortitem = oSelected.GetFirstItem( "Body" ) If ( ortitem.Type = RICHTEXT ) Then plainText = ortitem.GetFormattedText( False, 200 ) End If If oSelected.hasItem("ParentForm") Then vHTML = vHTML + "<TR><TD><B>Response Document</B></TD></TR><TR><TD>" + plainText + {</TD></TR>} Else vHTML = vHTML +"<TR><TD>" + plainText + {</td></tr>} End If vHTML = vHTML + "<TR></TR>" ' ** PROCESS DOCUMENT ** oSelected.Save True, True ' ** PROCESS DOCUMENT ** End Forall ' ******** PRINT RESULTS **************************** Print vHTML ' ******** EXIT ************************************* SubExit: Exit Sub ' ******** ERROR TRAP ******************************* SubError: Print "Error:" + Cstr(Err) + " " + Error Resume SubExit End Sub

    iii. the code for the Searchand Replace function as follows (included in the library). NOTE: turn off option declare/explicit or declare all variables in following script:

    Function SearchReplace(FullString$ , OldText$ , NewText$) As String REM This function will replace OldText$ with NewText$ in FullString$ REM Example Call: URL$ = SearchReplace(URL$ , {.com/} , {.net/}) AnyText$ = FullString$ pos& = Instr(AnyText$ , OldText$) While pos& > 0 AnyText$ = Left$(AnyText$,pos& -1) + NewText$ + Right$(AnyText$,Len(AnyText$) - pos& - Len(OldText$) + 1) pos& = pos& + Len(NewText$) pos& = Instr(pos&, AnyText$ , OldText$) Wend SearchReplace = AnyText$ End Function

    And there you have it, this works brilliantly for me.

    Hope this is of use to other people.

    Regards Jonathon

    1. Re: Exporting selected documents only... Example

      Sorry I am just a novice.

      I put in the code and when I try to export I get an empty word document.

      any hints would be great

      • avatar
      • neel
      • Tue 22 Jan 2008

      Problem Faced

      Hey friends,

      Actually I have an exporting problem in excel.When I am using code in different page as abc_excel.asp (original page is abc.asp) and writing there "Content-Type: application/vnd.ms-excel" it is working properly.I want to export data in same page but only the report segment ie. without the filters as combobox,submit button etc.

      *abc.asp contains both filter part(with combo box,submit button with reporting section etc)

      *abc_excel.asp contains only reporting page where parameters are passed through querystring.

      Req:-export only reporting part to excel without the filter section in abc.asp (WITHOUT GOING TO ABC_EXCEL.ASP)

      Please revert for any quaries.

  19. EXCEL as Pop-up won't close/refocus - error

    This is a great technique as is the "Managing JavaScrip Pop-up windows" but I get the following error when I try to close or refresh the pop-up through javascript: This one is from Microsoft Excel A document with the name '05' is already open. You cannot open two documenst with the same name, even if the documents are in different folders...." This one is a javascript error on the .focus statement. "Member not found." Any ideas? I have tried the WindowOpener function and many varations of thewindow.close, but to no avail. Any suggestions?

    Thanks

    Paula Wright

    • avatar
    • Pat Hanc
    • Mon 2 Dec 2002

    Embedded Excel in VB app

    I use Spreadsheet component from Microsoft Office XP Web Components.

    I have put the Spreadsheet in my VB form. It's a regular VB project.

    Now my question is : HOwTo print the spreadsheet ? OR how to print the XML file related to the spreadsheet ?

    Now I can Open Excel File saved in XML format and view it in spreadsheet. I can save Excel Spreadsheet to XML format file.

    But now I need to know how to print ? (8 1/2 x 14 Landscape) I have not been abble to print from IE considering this format.

    The application will be deploy to multiple Windows OS with multiple configuration. Multiple clients got different version of Office.

    I'm not sure if the office XP Web components is the gool tool for that but in my application I need to: show content of Excel file(open files) in my VB app. Allow modification of content by user an by VB app. Save files Print files

    Any suggestions ? Thansk all for your time! PAt

  20. I' Going Bald!

    Okay, this thing is driving me mad!

    My Excel loads in IE, I've tested for that. I have a simple agent with the simple 'table' code pasted from codestore.net. I can run the agent from an action menu in the Notes client (it obviously just prints the html to the results bar).

    When I look at the same view in IE, it gives me the action bar. When I click it, it then tries to run the agent via the correct URL. I then get a page cannot be displayed error?

    Is it the code its having problems with or it literally not finding the agent?

    1. Re: I' Going Bald!

      Ack...some idiot changed the security settings of our dev server without filling in the correct forms.

      I'll now go away and research under what 'sign' the agents run when being used on the web and whether this is restricted or unrestricted.

      Thanks.

  21. Cell formula not working

    I'm having the same problem that another poster reported with the totals not appearing. In the source code, where you would expect x: I get ul: even thought the agent code is the same. I'm seriously banging my head on this one. Anyone have a suggestion?

    BTW, thanks for all the great articles, Jake!

    further explanation (as I've posted on many web sites and got no answers):

    I have a webquerysave agent that exports some values to Excel, then runs a formula against them. The documentation (mainly codestore.net)I've read says to use a line like: <tr><td>Total</td><td x:fmla="=SUM (B2:B5)"><b></b></td></tr>

    This should produce the total, and in the source something like:

    <td class=xl27 align=right x:num="70000" x:fmla="=SUM (B2:B5)">70,000.00</td>

    However, nothing shows in my total cell, and my source looks like:

    <td class=xl25 width=53 style='border-top:none;border- left:none;width:40pt' u1:fmla="=SUM(B2:B5)">&nbsp;</td>

    notice that the desired result has x:fmla=.. while mine has u1:fmla=..

    1. Re: Cell formula not working

      If anyone knows the procedure to compute the value of a cell based on a formula for eg =Sum(R2C3+R2C4), plz do share with us.

  22. Notes release 6

    Hi,

    i tried to run the agent from a release 6 server and got the error messgae on the server:

    unsupported trigger and search in the background or embedded agent.

    I have signed the agent with a proper ID.

    Agent properties:

    options : shared runtime: trigger = on event, action menu selection target = all documents in view

    what did i do wrong for getting the error message above?

    kind regards, Patrick

      • avatar
      • Jake Howlett
      • Mon 31 Mar 2003

      Re: Notes release 6

      You can find out the meaning of this and other error messages in this article that lists a whole load of them.

      http://www.codestore.net/store.nsf/cmnts/54159BBCA099B4A0862569990055A9DA?OpenDo cument

      Jake

    1. Re: Notes release 6

      Hi Patrick

      How you are triggering the agent? If it not triggered thru action menu then you should change the option to "Agent List Selection". And if your agent is not reading any documents then change the other option to "None" instead of all documents.

      Kumar

  23. Closing excel in the agent

    How do you close the excel functionality in your notes agent? I want to send the user to another page after the export by using:

    Print "[/dbname/viewname]" at the end of my agent. But that address ends up on my excel spreadsheet because i'm exporting to Excel. How can i close that functionality so that my print statement will send me to the new page??

      • avatar
      • Jake Howlett
      • Mon 9 Jun 2003

      Re: Closing excel in the agent

      Why would you want to send some Excel to a browser and then overwrite with a redirect to another site?

      You can't do what you're trying to do.

      Jake

      • avatar
      • Balaji Vaddepalli
      • Wed 15 Aug 2007

      Re: Closing excel in the agent

      Hi

      For me I have exporting data to excel from the embeded view from the same window, its exporting fine after that if i ma clicking on any other link it giving error in the browser. I want to refresh the page once I exported to excel. Can any one help me how can I come out from excel once its exports to excel. Ex We have Exapp.close method in Lotus notes while exporting to excel.

  24. Lotus Notes Reporter 2

    Your nightmares are over. Your link to Lotus Notes Reporter 2 now generates a 404, pity they (IBM) haven't been reading your blog, they'd have known how to make a 404 page behave!

    • avatar
    • lomags
    • Fri 18 Jul 2003

    problem with set

    The example works only so long as i do not use a set statement ! Has someone an idea why it is so ? Has someone the same problems ?

    Thanks a lot

    1. Creating multiple worksheets

      This works great, but it seems that it will only create 1 worksheet at a time. How would I creae multiple worksheets?

      I'm using XP.

      Thanks,

      Caroline

      Show the rest of this thread

  25. It works!! kind of.....

    Brilliant article. I just have one area thats not working too well. I'm using the export feature throughout an application of mine...mostly in views with a small number of documents and its working perfectly. When I tried doing this on a view with close to 14K records, I get a message saying "HTTP Web Server: Lotus Notes Exception - Agent did not complete within the time limit." ....this only after 30 seconds (I timed it). I modified my server document to allow LotusScript/Java to run for 90 minutes but that did not help. Any thoughts? Thanks again for the wonderful code!!

    1. Re: It works!! kind of.....CORRECTED

      I found that I had modified my server document in the wrong spot. If you look at the server document, select the "Internet Protocols" tab and then select "Domino Web Engine" tab. At the bottom of this tab is a field called Web Agent Timeout (in seconds). This was initially set to 30 secs. I'm assuming this is the default. Anyhow, you can change the number to be higher or you can set it to 0 which allow the agent to run indefinitely (at least until you crash you server Remember to restart your server once you've made the change.

  26. Cell data too large

    While exporting selected docs to excel, I get an error "Cell data too large". How do I solve this? What is the limit of characters in Excel? I am using Excel 2000. Is there anything I need to change for Excel 2000. Please help.

    1. Re: Cell data too large

      It's possible the reason is not text overflow, but a <table> tag in the text. Stupid excel tries to interpret the <table> tag. I suppose you need to escape (mangle) such tags somehow, eg turn them into "[table]".

      I got this error, but it says "HTML ERROR in Cell data too large".

      Show the rest of this thread

    • avatar
    • Kurt
    • Thu 27 Jan 2005

    Create in new window

    A major change to my customer's networking environment included reverting to MSIE v5.50. I successfully implemented the Excel reporting tip under MSIE v6.xx, but now there seems to be a problem with the javascript menu bar that I am using in the application. I can create the spreadsheet report, but when I attempt to save it, I only have the option to save the report as a web page and my javascript menu bar no longer works until I refresh the browser.

    In any case, I thought I would try to create the Excel spreadsheet in a new window, but have not been able to figure out how to do it. Can someone provide some help?

      • avatar
      • Jake Howlett
      • Thu 27 Jan 2005

      Re: Create in new window

      Easiest way to create in a new window is to point the link that opened the agent to target="_new".

      Jake

      Show the rest of this thread

      • avatar
      • Per M
      • Tue 18 Sep 2007

      Re: Create in new window

      Hi! If you want to ensure that the speadsheet alwys opens in a new Excel-window, you can insert this line after the content-type line: Print {Content-disposition:attachment;filename=lista6.xls}

      (assuming the agents name is lista6.xls)

      Regards, Per M

  27. whether doese it work in Domino R6.5??

    About the code print {Content-Type:application/vnd.ms-excel} print{Content-Disposition:Attachment; filename="Report.xls"}

    Is it working well in Domino R6.5? I have a agent with the code for export to excel.It runs well in R5,when we upgrade to R6.5,It can not open in new window and download? If I delete code in line two.It can open excel in origin window. a headache,how can I resolve it? Thanks for advance

    • avatar
    • Per M
    • Wed 8 Jun 2005

    Great

    Hi! Thanks for posting this GREAT article. You really saved my day!

    • avatar
    • L
    • Mon 20 Jun 2005

    Based on a file template?

    This is really nice! Thanks. However, is there any way to use a predefined excel file instead of creating a brand new spreadsheet?

    Thanks, L

  28. Not working for Spanish Characters

    Hi, It works fine with English characters but behaving differently when spanish characters is handled.

  29. API for Excel to use from Notes...

    HI,,,, i found ur method very interesting.. I nedd some info about how can we format the text within Excel from Notes... Thanks in advance and regards

  30. Problem with display

    Hi,

    The code for displaying the view in excel works fine. The problem arises when I enforce Access control, i.e, I have a view with documents that use Readers & Authors fields. When I try to export the view to Excel with Anonymous given Manager access, it works fine but when Anonymous is given No Access, the code doesnt work. IE opens Excel within it but instead of displaying the data in the view, it displays a login screen for authentication.

    Any help would be appreciated.

    Thanks

      • avatar
      • Jake Howlett
      • Fri 15 Feb 2008

      Re: Problem with display

      I wouldn't, but you could pass the username and password as parameters to the URL followed by an &login

  31. Another way to create Excel reports from Notes

    I developed a Lotusscript class that makes writing to an Excel file very easy. It uses an Excel file as model. All the formating, presentation and calculations are defined in the Excel model. In the model file, target areas for Notes data are defined using cell naming. Giving to a target cell the same name as a Notes document item is enough to ensure the right filling. You can find more details, and a (free) demo database at the following address: http://seusoft.com/en/ExcelReport01.html

      • avatar
      • AA
      • Wed 15 Oct 2008

      Re: Another way to create Excel reports from Notes

      This is a good tool. you meen we can use it in our company gratis? thank you.

    • avatar
    • Ravi
    • Tue 29 Apr 2008

    problem with giving the filename

    Print |Content-Disposition:Attachment; filename="Change Requests By Control Number All Data.xls"|

    when I add the above line in the code, I have a problem with the javascript menu bar that I am using in the application. I can create the spreadsheet report, but when I attempt to save it, I only have the option to save the report as a web page and my javascript menu bar no longer works until I refresh the browser.

    In any case, I thought I would try to create the Excel spreadsheet in a new window, but have not been able to figure out how to do it. Can someone provide some help?

      • avatar
      • Ravi
      • Wed 30 Apr 2008

      How to lock the header

      I am using the below code to export notes data to excel through browser.

      Can some one please help me in locking the header of the excel sheet. Thanks.

    • avatar
    • ND
    • Thu 4 Sep 2008

    how to sort columns

    hi

    Can someone tell me how to sort data in the excel spreadsheet using the following code?

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

    Print |<table border="1"> <tr><td width="80">Quarter</td><td>Sales</td></tr>|

    While Not(ne Is Nothing)

    Print|<tr> <td>| + ne.ColumnValues(1) + |</td> <td >| + ne.ColumnValues(2) + |</td> </tr>|

    Set ne = nv.GetNext( ne ) Wend

    I WANT TO ADD CODE TO SORT BASED ON USER INPUT. Can someone help.

    Thanks in advance.

  32. Returning to default state

    Jake - thanks for the great article - i was wondering though - once i've written all I want to excel, how do I return the Print statement so the i can redirect the user to another page? Using simply "print" continues to write to the XLS. Any thoughts??

      • avatar
      • Jake Howlett
      • Thu 4 Jun 2009

      Re: Returning to default state

      Hi. If you want to redirect the browser using a print statement then it needs to be the first line printed (so it's part of hte headers rather than the body). You can't do what you're trying to do.

      Jake

    • avatar
    • Crispy
    • Mon 26 Nov 2012

    Note to anyone using the above technique that "extension hardening" from Office 2007 onwards now throws up a warning every time you try to open the file:

    "The file you are trying to open, '...xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"

    Selecting yes opens the file as normal...but does anyone know a way around this?? From what I can tell the only fix is a registry hack which works for Office 2007 but not Office 2010.

Your Comments

Name:
E-mail:
(optional)
Website:
(optional)
Comment:



Navigate other articles in the category "Miscellaneous"

« Previous Article Next Article »
Predicting the end   Yet another new eXPerience

About This Article

Author: Jake Howlett
Category: Miscellaneous
Keywords: report; excel;

Attachments

ExcelFormat.htm (6 Kbytes)

Options

Feedback
Print Friendly

Let's Get Social


About This Website

CodeStore is all about web development. Concentrating on Lotus Domino, ASP.NET, Flex, SharePoint and all things internet.

Your host is Jake Howlett who runs his own web development company called Rockall Design and is always on the lookout for new and interesting work to do.

You can find me on Twitter and on Linked In.

Read more about this site »