We help IT Professionals succeed at work.

problem converting column value in export agent

varvoura asked
Last Modified: 2013-12-18
Hi all,

I have the following script to export a view from notes to excel.
Now I needed to add some some of formatting so that if the column value in notes is text it will converting to text in excel column,if it is numeric in notes, it will be numeric column in excel, so I added a section which is marked in between %REM - %EndRem in the script, however no matter what I do, I seem to get a value such as 20-90-03 as date value when it is exported from notes into excel.

Here's my script. I know there's a lot of easier and better ones but i need to get this one working fine.

I've also tried to use some sort of NumberFormat in one of the column to see if it makes any difference but it didn't

But basically,I need to make sure the first column exported to worksheet(excel) is text no matter what.

I've also tried using call doc.replaceitemvalue("itemname", cstr(itemname) on all the items when I start the doc.getfirstdocument loop but I had no luck

Could anyone please take a look and see if they can help me achieving the correct results.

Thank you all.

Below is my script

Dim session As New NotesSession
      Dim ws As New notesuiworkspace
'     On Error Goto errorHandler3
'Check to see if the user is on a MacIntosh
'the "Create Object" function does not run on a Mac
      platform = session.Platform
      If Not Instr (platform, "MacIntosh") = 0 Then
            Messagebox ("This function cannot be run on a MacIntosh. Please use a PC to pull this data into a spreadsheet.")
            Exit Sub
      End If
'Set the session variables
      startTime = Timer
      Set db = session.CurrentDatabase
      Set dc = db.unprocesseddocuments
      Set uiview = ws.currentview
      Set v = uiview.view
      If exportall="Everything" Then
            folderName="TmpExportView" + Cstr (Int(Rnd()*100))
            If Not v Is Nothing Then
                  Set doc=db.getdocumentbyunid(v.universalid)
                  If Not doc Is Nothing Then
                        Set newdoc=doc.copytodatabase(db)
                        Call newdoc.replaceitemvalue("$Title",foldername)
                        Call newdoc.replaceitemvalue("$Flags","3FY")
                        Call newdoc.save(True,True)
                  End If
            End If
            Set v = db.getview(folderName)
            Call dc.putallinfolder(folderName)
      End If
' Initialise Progress Bar
      hwnd = NEMProgressBegin( NPB_TWOLINE ) ' use window style progress bar
      NEMProgressSetBarRange hwnd, numDocs ' set range of bar to number of rows
      NEMProgressSetText hwnd, "Exporting view to Excel.", "Starting Export to Excel..."
      Set xl = CreateObject("Excel.application")
      Set xlWbk = xl.Workbooks.Add
      Set xlSheet = xlWbk.Worksheets(1)
      Call xlSheet.Activate
      On Error Goto errorHandler
      xlSheet.Name = "Notes Exported Data"
'Start filling in the header column.  You can get rid of this if you want to, but then get rid of the section lower that highlights it...
      With xlSheet
            Forall vColumn In v.Columns
                  If vColumn.IsHidden = True Then
                        .Cells(1, col)=vColumn.Title
                  End If
            End Forall
      End With
'Row by Row, Column by Column, fill in the values
      Set docX=v.GetFirstDocument
      On Error Goto errorHandler
      With xlSheet
            'vera change
            While Not docX Is Nothing
                  Forall cValue In docX.ColumnValues
                        If Isarray(cValue) Then
                              For i=0 To Ubound(cValue)
                                    If Isdate(cValue)Then
                                    Elseif cValue = " " Then
                                          cValue(i) = Cstr(cValue)
                                    Elseif Isnumeric (cValue) Then
                                          cValue(i) =Cdbl(cValue)
                                          cValue(i) =Cstr(cValue)
                                    End If
                                    If i=Ubound(cValue) Then
                                          text=text & cValue(i)
                                          text=text & cValue(i) & Chr(10)
                                    End If
                              .Cells(row, col)=text
                              .Cells(row, col)=CValue
                        End If
                  End Forall
                  If row Mod 10 = 0 Then
                        processingTime = Timer - startTime
                        NEMProgressSetBarPos hwnd,row
                        NemProgressSetText hwnd, "Exporting view to Excel.", "Exporting: "& Cstr(row) & " of " & Cstr(numDocs) & "  documents exported in "  & Format$(processingTime, "0.00") & " seconds, AVG = " & Format$(row / processingTime , "0.000")
                  End If
                  Set docX=v.GetNextDocument(docX)
      End With
      On Error Goto errorHandler2
'Set sizing, fonts, etc to make the spreadsheet readable.
      xl.selection.Font.Name = "Verdana"
      xl.selection.Font.Size = 9
      xl.Selection.Font.Bold = True
      xl.selection.Font.size = 9
      xl.selection.RowHeight = 15
      xl.selection.columnwidth = 100
      xl.selection.VerticalAlignment = xlTop
      'my change
      xl.columns(0).NumberFormat = "###########"
'Stop Progress Bar
      NEMProgressEnd hwnd
'Save and be gone!
      On Error Goto errorHandler3
' LotusScript code...
      processingTime = Timer - startTime
      Print "The script ran in " & Format$(processingTime, "0.00") & " seconds."
      Call v.remove
      Exit Sub
'This is called when there is bad data in a notes view, usually text in a date field, etc.
'Notes will show it, but it will fail to export correctly.  This replaces the Excel cell with a bad data text.
      NEMProgressEnd hwnd
      Messagebox "Bad Options setting Spreadsheet format"
      Call xl.Quit
      If folderName Then
            Call v.remove
      End If
      xl = ""
      Exit Sub
      NEMProgressEnd hwnd
      Messagebox "Bad Filename Specified.  Please make sure that the directory name is correct."
      Call xl.Quit
      xl = ""
      If folderName Then
            Call v.remove
      End If
      Exit Sub
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)


Hi Marilyng,

First, I'd like to thank you for intuitive explanation, its great.

I am not sure where this script was picked up from because I didn't pick it up, I was just asked at work to modify it so that all column in excel match their format in notes because users seem to have problems with values exported from notes to certain column in excel, eg. text values are dislaying as date.

Now to the modifications, I can see what you are doing in the first bit, you are checking for the values of the columns in the notes before the export happens and trying to set them to their equivalent type. Is this correct?

In the last bit, you are trying to apply the formatting to the excel spreadsheet as opposed to the first option. Can you use both of these together to make sure that the right format is obtained after the export.

  .NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"

To me when I see .NumberFormat, Are you trying to format a number to date?
is this what $-F800 mean?

I have over 15 columns that I need to format, some of those needs to be formatted to text, some to date and others to numbers, what is the equivalent to
.NumberFormat...... if the column in spreadsheet needs to be a text then?

Last but not the least, do have a link to a resource where I can learn more about these excel object model, eg. format functions, reference style, this is my first exposure to those and it seems that I'll be doing a lot of those at work.

Thanks again for all your help with this and the points are all yours.




a cell that's suppose to be text in excel displays "Date text with two digit year" when I click on the corner of that cell in excel. I am presuming that this cell have been converted from text to date during the export. Is this correct?


Now to the modifications, I can see what you are doing in the first bit, you are checking for the values of the columns in the notes before the export happens and trying to set them to their equivalent type. Is this correct? << Yes, is one way to do it.  This is what you asked to be corrected.  But, it is not a good way to do this.
In the last bit, you are trying to apply the formatting to the excel spreadsheet as opposed to the first option. Can you use both of these together to make sure that the right format is obtained after the export.  << Not a good idea.  

  .NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"  

To me when I see .NumberFormat, Are you trying to format a number to date? << This is the excel property to format a cell or range.

is this what $-F800 mean? << Open excel, put a date in a field.  Click record macro, format the date to whatever format you want using format.. cells.  Save the Macro.  Switch to Visual Basic Editor and find the formula in the visual basic for excel. Use that format in notes.

"cell have been converted from text to date during the export" << not to my knowledge.  Excel decides and uses the default values if none are specified.  So you can send a date, but excel might decide that it's a number or formula.  A user's preferences are used first, so my spreadsheet might use Arial font size 10, but yours might use Arial font size 8.  My default date format might be: "yy-dd-mm", yours might be "mm-dd-yy."

Excel has it's own methods, properties and constants.  If you want to format excel, you need to know how to program excel.  the language Notes and Excel have in common is visual basic.

I asked where the code was from to find out what it was supposed to look like before it was changed.


All good.

You've answered my question in the first bit which is the conversion to notes and that's great, but you didn't recommend this so I am trying to go by your recommendation.
My questions on how to use the formatting options in excel was really to go by your recommendation.

Still one ambigious issue with all that:
Does NumberFormat work for text. Another word, if I want to force one of the excel spreadsheet to be text even when it appears as date while exported can I still use NumberFormat? I know there is a DateTime Format, but is there any specific format option for text?

You can ask whatever you want to ask about the code, after all you are trying to help me, but as I said again, I am in a new role and the last thing I am going to ask my manager who gave me this code to modify to where he got it from. I assumed that he wrote it himself.

Thanks again for your help with this.
Also your detailed explanation is really helpful.

numberformat refers to the number format that appears in excel when you select Format>>cells.
if I understand you correctly, if you have a date in notes that you want to format specifically to text, then you have to send it to excel as text,
xlapp.range(rows,cols).value = format(doc.thisfield(0),"mm/dd/yyyy")


xlapp.range(rows,cols).value = ucase(format(doc.thisfield(0))


xlapp.range(rows,cols).FormulaR1C1 = "=RC[-2]&RC[-1]"
However, sending a date as format(doc.thisfield(0), "mm/dd/yyyy") will send it to excel but excel will still translate it as a date, and display it according to the spreadsheet defaults.

So there are two properties that you take into consideration when exporting to excel: the value of the cell, and how you want the cell formatted.    Both are properties but may be contained by different excel application objects.

Whether or not you can use properties and methods that are outlined in Excel's visual basic help are dependent on those that appear in the Notes OLE reference guide.  Like everyone suggests, record the macro in excel to achieve the value and format that you want and then open visual basic editor in excel to see what it says. :)


Point taken and fantastic help thanks.
All this info will be of a great help to me now and in future.

Just a small clarification on my part though.

I don't have a date field in notes that I am trying to format to text.
What I do have is a text field in notes which happens to have dashes in it, but some reason or another and in many cases but not all cases, this field value displays in excel as date(because of the dashes in that field). What i am trying to do is to make sure that this field even it had dashes and it looks like a date, but it is in fact a text to display as text in excel. For example,
field value in notes, 23-09-04, is a code for a product with a text field type in notes and I want it to stay the same when it moves to excel. Although i try to apply the cstr(formatting in notes) before populating the values in the exported spreadsheet, I always seem to get the value in the excel spreadsheet as date(really annoying).
That the reason why i am trying to get the right info on conversions between notes and excel.

Thanks a million for all the time and explanation you put into this.

Again, open excel. Enter the value "01-02-04" - it wil convert to date.
now record a macro, and select format>>cells>> select "text"
then enter your value: "01-02-04" and save the maco.
Open the excel visual basic editor to the macro you just recorded and see how excel wants you to format it for notes.
If you select a different cell and run the macro you just created, it should enter "01-02-04" and not convert it to a date.

Vera, this is all available in Excel help, :)

Have you tried this?  it will answer all your questions.



Yes, I have and I can see the results fine in excel but when using the same formula in notes, it still seem to give me problems. I try exporting again using the new change and the cell which has 01-02-04 still says that it is a textdate field with 2 digit year.
Any reason why?

I have been looking at all different help options

Not sure if you can further clarify any of this.



I've already gone through all your above steps many times.


I am probably doing this right but I am realising because I am not so familiar with the process. Let me test something and then I can explain what i am doing and you can clarify if wrong.


Actually only one thing I want to clarify:

When I run the Macro, after changing the first column value to text, I get the following formula

Selection.NumberFormat = "@"
This applies to Cell A1 only in the worksheet and it will format it to text.
If I was to format that entire column to text, would I then just use the following in lotusscript

Selection.NumberFormat = "@"

Then for the second column which I want to format into number could I then do this
Selection.NumberFormat = "##.####" since it currency with 2 decimal

Then the third column, for date

Is this a crazy assumption. I know it is all in help and I am looking to find a way to this right now.

Varvoura, can you please fill out your profile for us? It may help experts when they know if they're trying to help an expirienced programmer (in java, vb, notes or whatever), someone with a different background in IT, or someone who has only just joined the crew.
In car terms: we're wondering if you're a mechanic, a driver or e.g. a navigator....


Quite a strange question, not so sure how I'll take this one, but as they say, beggars can't be choosers and I'll add to this(otherwise they'll be losers)

Surely enough, experts by now understand that i am in the Notes background, every question of mine is a Notes related one. I do know a lot about the Notes Administration environment as it was my area of expertise for the last 4 years and I never had to ask more than one question ever. I did develop over 30 databases in the last 4 years also, but they were simple ones, but now with my new role I am involved into more complex development processes, hence my long list of questions.

I'd like to be a main driver rather than the mechanic, but it is taken me a bit of time, but I guarrantee you I'll be there shortly + I am too nice looking to be a mechanic!!!

Have a lovely day/evening CRAK and although I wasn't going to answer this question but I do hope my answer was satisfying.



I think that I fixed the problem with a bit of fiddling so thank you so much for all your help today, it means a lot to me.


Selection.NumberFormat = "@"  << excellent! See!

xlApp.ReferenceStyle = 1


Yup that's exactly what I used and I managed to use excel to get all the other types for my 20 other columns and they work magic.

Thanks heaps for today, seriously, now I also have excel skills.

PS. My script works OK without using the refrencestyle = 1 that you have up there, do you think that it is best to include it the same way you have it coded?

Getting there!!



Is there a way to group those columns of similar type together so I don't repeat myself in script.
For example,

               xl.Columns(1).NumberFormat = "@"
      xl.Columns(2).NumberFormat = "@"
      xl.Columns(3).NumberFormat = "@"

Then I have a few other columns with text type.
I also have a range of dates and a range of number.
At the moment, they work great but they separated on each line.
I was thinking maybe it is best to group each time to reduce script size.
not sure if possible though.
Can you please clarify.


I didn't mean to offend you, I (and probably a few others too) was only wondering because of the amount and kind of questions (and no, EE has no rule against that!).
4 Years of expirience and currently taking on the thougher databases..... such background info is good for "us" to know.
I'm not an expert in programming Excel myself, but lotusscript is no issue to me. If I had a question answered by somebody expecting me to know Excel inside out and guessing that LS would be the tough part for me, we wouldn't easily be getting anywhere! The more of such details an expert knows about me, the better he/she would be able to be of good help with only a few hints!


No problems & I am not offended, just curious to why you would ask such a question.
I guess I never looked into this because the experts have always done such a fantastic job in answering my questions no matter how confusing they were.

.Range("A:D").Numberformat = "@"
But you decided to use columns?  Ok, you can do that, but columns only reference one column, so you would have to loop through the columns and  add the formatting:
 for x = 1 to maxcols
Range/Reference style requires more explanation.. but, I was avoiding this since it is complicated and hard to understand...

xlApp.ReferenceStyle = 1                                    << makes excel use the A1, B1 reference styles
xlapp.columns("B:B").ColumnWidth = 35               << then this will work.

The agent should have a start line:
xlApp.ReferenceStyle = 2                                  <<this is so you can use (rows, cols)

But, when declaring a RANGE you have to use the top left reference, to the bottom right reference  i.e. A1 to D255
in the R1C1 notation that would be something like:
             xlApp.Range(xlSheet.Cells(1,1), xlSheet.Cells(1, maxcols)).Select      
Which is cumbersome, but not really since you know how many columns and how many rows you're exporting. But, if you change to the standard notation, then you can use "A:B" or "A1:D255"

Most people use the R1C1 reference since we have a handle on rows and columns while we're exporting.  When I get to the end of the excel export, I switch to the standard notation, and step through my format array, so that I have a print selection, bold and regular fonts, color titles and color summaries, conditional formatting, pivot tables, etc.  This is a complicated step, because I generally read the column value types at the top of the code and create an array of how I want it formatted, text, date, currency, integer, double, long, etc.

Another way:  
 if len(doc.thisDate(0))>0 and year(doc.thisDate(0))>1959 then
             tmpStr =format(doc.thisDate(0), "'mm-dd-yy")           ' add a (') single quote before the date.
             tmpstr = ""
  end if
    .range(rows,cols).value = tmpstr

  Downside is that excel will create a note in each cell asking if you want to convert the date or leave as is. :(

All clear then!  ;-))
Mind if I ask where you're from? And what kind of company you work for (no need to name it specifically)?
Just curious....


International Business Franchise Network. High dependency on Notes/Domino environment for business apps.


CRAK, This question yours and hope you can give some ideas

I am about to develop a front end database catalog which will be used globally. This catalog needs to have a nice Front page, with navigators and links to all the dbs that you have on the server. I know that notes already have catalog.ntf template which I can use that as I start and I will. Do you have any ideas? Samples dbs to review?

Don't worry, this is not a compulsory question and I already know how to develop this thing but if you have any design hints, ideas to make it more than a plain boring db catalog, please let me know.

Have a lovely day

This question can't be mine: I haven't responded to your original question.
This is a new question and should in fact be treated that way.
But to save you the trouble (and please do NOT award points for this comment): I'm currently working on something like that myself. I'm using several PC's in several locations, so I need to store my bookmarks in a central place somewhere. And, stubborn as I am, I'll try to reinvent the wheel: I do not want to use any of the hundreds (thousands) solutions currently available on the web.
Currently I've come down to url's and descriptions (both language specific!) and, in an attempt to disclose the rights in an easy way,a multi value category (again: multi lingual).
Right now it's nothing but a categorised view, but it shouldn't take much more than a couple of minutes of spare time to turn it into a combobox-driven single category embedded view.
That's where I ran out of inspiration, so if you have a good idea....?

Those few minutes of spare time won't come available easily as I've found another silly project to waste my evenings on.


I don't mean that I am going to reward the points for this question. Those already belong to Marilyn, she has done too good of a job to reward the points to anyone else but her. Thanks to you Marilyn, I now know how to work/cheat with excel recording, etc.. lots of good info in this response.

CRAK, All what I meant that I am directing this new question, bla,bla....at you.

Anyway, enough said for now, and yes when I start this project,I'll send you some hints/ideas.


Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.