varvoura
asked on
problem converting column value in export agent
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("item
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
Else
Randomize
folderName="TmpExportView"
If Not v Is Nothing Then
Set doc=db.getdocumentbyunid(v
If Not doc Is Nothing Then
Set newdoc=doc.copytodatabase(
Call newdoc.replaceitemvalue("$
Call newdoc.replaceitemvalue("$
Call newdoc.save(True,True)
End If
End If
Set v = db.getview(folderName)
Call dc.putallinfolder(folderNa
End If
numDocs=v.allentries.count
' 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.applic
Set xlWbk = xl.Workbooks.Add
Set xlSheet = xlWbk.Worksheets(1)
Call xlSheet.Activate
On Error Goto errorHandler
xlSheet.Name = "Notes Exported Data"
xl.Cells.select
xl.Selection.ClearContents
'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...
col=1
With xlSheet
Forall vColumn In v.Columns
If vColumn.IsHidden = True Then
Else
.Cells(1, col)=vColumn.Title
col=col+1
End If
End Forall
End With
'Row by Row, Column by Column, fill in the values
'
Set docX=v.GetFirstDocument
row=2
On Error Goto errorHandler
With xlSheet
'vera change
While Not docX Is Nothing
col=1
Forall cValue In docX.ColumnValues
If Isarray(cValue) Then
For i=0 To Ubound(cValue)
%REM
If Isdate(cValue)Then
cValue(i)=Cdat(cValue)
Elseif cValue = " " Then
cValue(i) = Cstr(cValue)
Elseif Isnumeric (cValue) Then
cValue(i) =Cdbl(cValue)
Else
cValue(i) =Cstr(cValue)
End If
%ENDREM
If i=Ubound(cValue) Then
text=text & cValue(i)
Else
text=text & cValue(i) & Chr(10)
End If
Next
.Cells(row, col)=text
Else
.Cells(row, col)=CValue
End If
continue:
col=col+1
End Forall
row=row+1
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(doc
Wend
End With
On Error Goto errorHandler2
'Set sizing, fonts, etc to make the spreadsheet readable.
xl.Cells.select
xl.selection.Font.Name = "Verdana"
xl.selection.Font.Size = 9
xl.Rows("1:1").Select
xl.Selection.Font.Bold = True
xl.selection.Font.size = 9
xl.selection.RowHeight = 15
xl.Cells.select
xl.selection.columnwidth = 100
xl.selection.columns.Autof
xl.selection.rows.Autofit
xl.selection.VerticalAlign
xl.ActiveSheet.Range("A1")
'my change
xl.columns(0).NumberFormat
'Stop Progress Bar
NEMProgressEnd hwnd
'Save and be gone!
On Error Goto errorHandler3
xl.Visible=True
' LotusScript code...
processingTime = Timer - startTime
Print "The script ran in " & Format$(processingTime, "0.00") & " seconds."
Call v.remove
Exit Sub
errorHandler:
'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.
Resume
errorHandler2:
NEMProgressEnd hwnd
Messagebox "Bad Options setting Spreadsheet format"
Call xl.Quit
If folderName Then
Call v.remove
End If
xl = ""
Exit Sub
errorHandler3:
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
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("item
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
Else
Randomize
folderName="TmpExportView"
If Not v Is Nothing Then
Set doc=db.getdocumentbyunid(v
If Not doc Is Nothing Then
Set newdoc=doc.copytodatabase(
Call newdoc.replaceitemvalue("$
Call newdoc.replaceitemvalue("$
Call newdoc.save(True,True)
End If
End If
Set v = db.getview(folderName)
Call dc.putallinfolder(folderNa
End If
numDocs=v.allentries.count
' 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.applic
Set xlWbk = xl.Workbooks.Add
Set xlSheet = xlWbk.Worksheets(1)
Call xlSheet.Activate
On Error Goto errorHandler
xlSheet.Name = "Notes Exported Data"
xl.Cells.select
xl.Selection.ClearContents
'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...
col=1
With xlSheet
Forall vColumn In v.Columns
If vColumn.IsHidden = True Then
Else
.Cells(1, col)=vColumn.Title
col=col+1
End If
End Forall
End With
'Row by Row, Column by Column, fill in the values
'
Set docX=v.GetFirstDocument
row=2
On Error Goto errorHandler
With xlSheet
'vera change
While Not docX Is Nothing
col=1
Forall cValue In docX.ColumnValues
If Isarray(cValue) Then
For i=0 To Ubound(cValue)
%REM
If Isdate(cValue)Then
cValue(i)=Cdat(cValue)
Elseif cValue = " " Then
cValue(i) = Cstr(cValue)
Elseif Isnumeric (cValue) Then
cValue(i) =Cdbl(cValue)
Else
cValue(i) =Cstr(cValue)
End If
%ENDREM
If i=Ubound(cValue) Then
text=text & cValue(i)
Else
text=text & cValue(i) & Chr(10)
End If
Next
.Cells(row, col)=text
Else
.Cells(row, col)=CValue
End If
continue:
col=col+1
End Forall
row=row+1
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(doc
Wend
End With
On Error Goto errorHandler2
'Set sizing, fonts, etc to make the spreadsheet readable.
xl.Cells.select
xl.selection.Font.Name = "Verdana"
xl.selection.Font.Size = 9
xl.Rows("1:1").Select
xl.Selection.Font.Bold = True
xl.selection.Font.size = 9
xl.selection.RowHeight = 15
xl.Cells.select
xl.selection.columnwidth = 100
xl.selection.columns.Autof
xl.selection.rows.Autofit
xl.selection.VerticalAlign
xl.ActiveSheet.Range("A1")
'my change
xl.columns(0).NumberFormat
'Stop Progress Bar
NEMProgressEnd hwnd
'Save and be gone!
On Error Goto errorHandler3
xl.Visible=True
' LotusScript code...
processingTime = Timer - startTime
Print "The script ran in " & Format$(processingTime, "0.00") & " seconds."
Call v.remove
Exit Sub
errorHandler:
'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.
Resume
errorHandler2:
NEMProgressEnd hwnd
Messagebox "Bad Options setting Spreadsheet format"
Call xl.Quit
If folderName Then
Call v.remove
End If
xl = ""
Exit Sub
errorHandler3:
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Marilyng,
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?
Thanks
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?
Thanks
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.
--------------------------
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.
ASKER
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.
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).val
or
xlapp.range(rows,cols).val
or
xlapp.range(rows,cols).For
--------------------------
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. :)
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).val
or
xlapp.range(rows,cols).val
or
xlapp.range(rows,cols).For
--------------------------
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. :)
ASKER
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.
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.
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.
ASKER
Marilyn,
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.
Thanks
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.
Thanks
ASKER
I've already gone through all your above steps many times.
ASKER
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.
ASKER
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
Range("A1").Select
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
Range("A").Select
Selection.NumberFormat = "@"
Then for the second column which I want to format into number could I then do this
Range("B").Select
Selection.NumberFormat = "##.####" since it currency with 2 decimal
Then the third column, for date
Range("C").Select
Selection.NumberFormat="dd
Is this a crazy assumption. I know it is all in help and I am looking to find a way to this right now.
When I run the Macro, after changing the first column value to text, I get the following formula
Range("A1").Select
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
Range("A").Select
Selection.NumberFormat = "@"
Then for the second column which I want to format into number could I then do this
Range("B").Select
Selection.NumberFormat = "##.####" since it currency with 2 decimal
Then the third column, for date
Range("C").Select
Selection.NumberFormat="dd
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....
In car terms: we're wondering if you're a mechanic, a driver or e.g. a navigator....
ASKER
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.
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.
ASKER
Marilyn,
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.
Regards,
Vavoura
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.
Regards,
Vavoura
Range("A").Select
Selection.NumberFormat = "@" << excellent! See!
xlApp.ReferenceStyle = 1
Xlapp.Range("A:A").Select
Selection.NumberFormat = "@" << excellent! See!
xlApp.ReferenceStyle = 1
Xlapp.Range("A:A").Select
ASKER
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!!
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!!
ASKER
Marilyn,
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.
Thanks
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.
Thanks
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!
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!
ASKER
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.
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
xl.Columns(x).NumberFormat
next
--------------------------
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").Colum
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(
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
tmpStr =format(doc.thisDate(0), "'mm-dd-yy") ' add a (') single quote before the date.
else
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. :(
--------------------------
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
xl.Columns(x).NumberFormat
next
--------------------------
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").Colum
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(
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
tmpStr =format(doc.thisDate(0), "'mm-dd-yy") ' add a (') single quote before the date.
else
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....
Mind if I ask where you're from? And what kind of company you work for (no need to name it specifically)?
Just curious....
ASKER
Australia.
International Business Franchise Network. High dependency on Notes/Domino environment for business apps.
International Business Franchise Network. High dependency on Notes/Domino environment for business apps.
ASKER
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
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....?
PS:
Those few minutes of spare time won't come available easily as I've found another silly project to waste my evenings on.
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....?
PS:
Those few minutes of spare time won't come available easily as I've found another silly project to waste my evenings on.
ASKER
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.
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.
;-))
ASKER
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.
Varvoura