Can’t export to wk4, due to limitation of rows. Export to structured text, notes will export field by rows, not what I want. Export to tabular text, looks okay, but than I will like to have it in excel, to make use of excel features, it will not cut nicely. I tried all the delimited options, etc, but some words the cross few columns will be cut off to several columns. So, any better suggestion? I even tried to open in 123 from txt, but still there is problem of cutting off of data to several columns.
Will, R7 now has the Comma Separated Value option for view export. Give it a .CSV extension and that would open in Excel as a spreadsheet. Would that do what you need?
No, it does not, it seems that the column being distorted, I have some columns which is used as sorting indicators, so, when it comes to total by categories, it is not being displayed as per the current view. i.e the total for Year1 is shifted to my Region sort column, since there is no total value, since this is text column.
any other suggestion?
I hoped notes team can really consider this: why can’t notes just have export to 123 v9 or Excel? When are they going to start developing this? I know about Hannover, but this is such a basic long outstanding requirement, don’t you think?
Try this (2 Subs & 1 Function - drop it all into an agent or script libarary) and call it from a button in a view (you’ll need to create a view that’s sorted and contains all the columns you want to export)
Global var :
Dim ws As notesUIWorkSpace
Sub ExportCSV(fileName As String)
'This routine exports data in CSV format including titles for the view columns for the currently displayed view.
On Error Goto ErrHandler
Dim doc As notesdocument
Dim itm As notesitem
Dim txt As String
Dim idx As Long
Dim fil As Long
Dim quote As String
Dim vw As notesview
Dim vwRows As notesviewentrycollection
Set vw = ws.currentview.view
quote = Chr(34) 'this is the double-quote character (")
fil = Freefile()
If fileName = "" Then
fileName = "C:\REPORTS"
End If
Open fileName For Output As fil
idx = 1
'first export the titles
txt = ""
If idx = 1 Then
Forall Col In vw.columns
If Strcomp(txt,"") Then txt = txt & ","
txt = txt & quote & Col.title & quote
End Forall
Print #fil, txt
End If
'now loop through the various rows of the view
Set vwRows = vw.AllEntries
Set vwRow = vwRows.GetFirstEntry
While Not vwRow Is Nothing
Print "Exporting record # " & Trim$(Str(idx))
txt = ""
Forall Col In vwRow.columnValues
If Strcomp(Txt, "" ) Then txt = txt & ","
If col = "" Then
txt = txt & quote & " " & quote
Else
If Isdate(col) Then
'must do the check for date first because numeric check also returns true for date
txt = txt & quote & col & quote 'this is date
Else
If Isnumeric(col) Then
txt = txt & col 'this is numeric
Else
txt = txt & quote & col & quote 'this is text
End If
End If
End If
End Forall
Print #fil, txt
Set vwRow = vwRows.getnextentry(vwRow)
idx = idx + 1
Wend
Print "End of Data Export"
If fil > 0 Then Close #fil
Exit Sub
ErrHandler:
Msgbox("An error has occurred during export of data")
Exit Sub
End Sub
Function GetFileName() As String
'This routine will prompt for a filename. A future enhancement would be to provide a directory navigator
Dim files As Variant
'only allow one file name to be selected
files = ws.OpenFileDialog(False,"Filename for Export File","*.csv", "C:\","REPORT.CSV")
'If user presses 'Cancel' on the dialog box
If Isempty (files) Then
Exit Function
End If
GetFileName = Cstr(files(0))
End Function
Sub GetFileAndExport
'This calls the routines to obtain the filename and then export the data to it
Dim fileName As String
Set ws = New notesUIWorkSpace
fileName = ""
fileName = GetFileName
If fileName <> "" Then
ExportCSV(fileName)
End If
Thank you very much, if I want to export straight to excel, can this formula still be used? Is it just changed the last 3rd line from ExportCSV to ExportExcel?