Export any view dynamically to a comma quote delimted text file

The code below, when used in a view action button, will dynamically export the entire contents of the view to a text file in the C:\Temp directory. The user will be prompted for the desired filename and if they wish to include the column titles as headers. The code will retrieve the actual values displayed in the view, not the back-end fields, which is helpful when columns are combined field and text values. Also is helpful with date formats, ensure that date fields export in the format consistent with the user's local settings.
Lotus Notes View • LotusScript

Sub Click(Source As Button)
	On Error Resume Next
	Dim workspace As New NotesUIworkspace
	Dim session As New notessession
	Dim db As notesdatabase
	Set db = session.currentdatabase
	Dim uiview As NotesUIView
	Set uiview = workspace.currentview
	Dim view As notesview
	Set view = uiview.view
	Dim vecollect As notesViewEntryCollection
	Set vecollect = view.AllEntries
	Dim entry As NotesViewEntry
	Set entry = vecollect.GetFirstEntry()
	If entry Is Nothing Then Exit Sub
	'************************************* Set Variables **************************************
	'Used for both header and data
	Dim LastColumn As Integer
	LastColumn = Ubound(view.columns)
	'Target folder on local machine to create/use
	Dim TargetPath As String
	TargetPath = "C:\Temp"
	'Prompt user for Filename to create
	Dim OutputFilename As String
	OutputFilename = Inputbox$("Do NOT include the 3 digit extension (.txt)", "Enter the filename to use.")
	If OutputFilename="" Then Exit Sub
	'*********************************Target Path Checks**************************************
	Chdir TargetPath				'Check that the TargetPath exists
	If Err = 76 Then				'If it did not exist, peform these tasks
		Mkdir TargetPath 			'Create the TargetPath
		Err = 0						'Reset Error Number for next check
		Chdir TargetPath 			'Verify that the TargetPath was successfully created
		If Err = 76 Then 			'If it wasn't created successfully
			Msgbox "TargetPath " & TargetPath & " does not exist or could not be created..", 0 + 16, "Invalid Path"
			Chdir "C:\"
			Exit Sub
		End If
	End If
	'********************************* Create the output file **************************************
	Dim TargetFilename As String
	TargetFilename = TargetPath & "\" & OutputFileName & ".txt"
	' Delete old output file.
	If Not Dir$(TargetFilename, 0) = "" Then
		If Msgbox( "A file already exists with this name, do you wish to overwrite?" , 4 + 32 + 256 , "Duplicate" )  = 6 Then	
			Kill TargetFilename
			Goto TryAgain
		End If
	End If		
	'Create the file
	Dim filenum1 As Integer
	filenum1 = Freefile
	Open TargetFilename For Append As filenum1
	'********************************* Write the header record **************************************
	If Msgbox( "Do you want to include the Header Row?" , 4 + 32 + 0 , "Header" )  = 6 Then	
		Dim HeaderString As String
		For c = 0 To LastColumn
			If c = 0 Then 		
				'first column
				HeaderString = view.Columns(c).Title
				'every other
				HeaderString = HeaderString & Chr(34) & "," & Chr(34) & view.Columns(c).Title
			End If
		Next c
	'write to file
		Write #filenum1 , HeaderString
	End If
	'********************************* Write the Content records **************************************
	Dim ValueString As String
	Dim counter As Long
		For v = 0 To LastColumn
			If v = 0 Then
				'first column
				ValueString = entry.ColumnValues(v)
				'every other
				ValueString = ValueString & Chr(34) & "," & Chr(34) & entry.ColumnValues(v)
			End If
		Next v
		'write to file
		Write #filenum1 , ValueString
		counter = counter + 1
		Print counter & " of " & vecollect.count & " docs processed so far."
		Set entry = vecollect.getnextentry(entry)
	Loop Until entry Is Nothing
	'********************************* Close the output file and release the handle Notes has on the TargetPath***********************
	Close filenum1	
	Chdir "C:\"
	Msgbox "The following file has been created:" & Chr(10) & Chr(10) & TargetFilename, 0 + 64, "Export complete"
End Sub

Posted by fbrefere001 on Wednesday March 23, 2005