Export to Excel

HCL Domino Domino Forum
1

Help!!

I am a Notes Administrator who has been asked to do some development work since our developer is out of the country for two weeks.

We have a database that tracks tasks. We have a view that shows all over due tasks. My boss would like me to create a button that exports the over due tasks to excel. He would like a pop up box to come up when he clicks the button asking him a date range. He wants to select the data range and export only the documents that fall in that date range to excel. The date range will be based on the due date of that document.

I am not very versed in development. So any help would be greatly appreciated.

My back is against the wall :slight_smile:

Thanks,

Jeremy

2

Subject: Export to Excel…

The boss cannot wait the two week and just go to the view and select the documents they want and choose FIle > Export?

Algorithm:

Prompt for start date and end date (several options from - input box, to dialog boxes)

Find all documents on the view with the date range (get a document collection)

Export the documents to Excel (several options from @command, to using DOM objects to create an excel spreadsheet)

3

Subject: RE: Export to Excel…

Apparently not :slight_smile:

I understand the process, I just have no idea how to code it. Any help there?

Thanks again,

Jeremy

4

Subject: RE: Export to Excel…

Jeremy,

Changes are you will be better off with Lotus Scrip.

Create your action or an action on the view level.

Bring up a dialogbox asking for a Start and End date range.

When the user clicks ok, you have a choice, you can nagigate the view, or build a dbquery such as

dim dc as notesDocumentCollection

set dc = db.search( |Select form = “” & date >= @date(| your date & |) & date <= @date( …

then loop thru the document collection and build the excel sheet.

there are example in here that should show you this.

you can also look in the sandbox and get my export to excel. This would make you life easier, however you would have to teach your user how to do a full test query.

email me and I cna send you the new code that I have for the export.

cheers,

john

5

Subject: RE: Export to Excel…

Here is a snippet of code that I use to run a mail merge. How you get your documents is up to you. A view by date might work OK, or you can use John’s suggestion of getting a NotesDocumentCollection via a date range.

By the way, this is a very advanced programming task. I would do everything in my power to get the boss to wait.

Dim officeApp As Variant

Dim xlApp As Variant

Dim xlsheet As Variant

Dim array As Variant

Dim lname As String

Dim fname As String

Dim k As Integer

Dim IsFirstTry As Integer

Dim WindowsDirectory As String

WindowsDirectory = Environ("Windir")



Set domSes = New NotesSession

Set domDb = domSes.CurrentDatabase

'Unprocessed Documents is the key to finding selected documents

Set domColl = domDb.UnprocessedDocuments

Set domDoc = domColl.GetFirstDocument

If domColl.Count = 0 Then

	Messagebox " At least one document must be selected in the view.",16,"Application Error"

	Exit Sub

End If



Set xlApp = CreateObject("Excel.application")

xlApp.Workbooks.Add

Set xlsheet = xlApp.Workbooks(1).Worksheets(1)

'Write column titles

With xlsheet

	.range("A1") = "RowTitle1"

	.range("B1") = "RowTitle2"

	.range("C1") = "RowTitle3"

	.range("D1") = "RowTitle4"		

	'etc, etc, etc.

End With



k = 1      'k keeps track of the row number

While Not (domDoc Is Nothing)

	k = k + 1			

		'write dataset to Excel worksheet

	With xlsheet

		.range("A" & k) = domDoc.Field1(0)

		.range("B" & k) = domDoc.Field2(0)

		.range("C" & k) = domDoc.Field3(0)

		'etc, etc, etc.

	End With

End With

Set domDoc = domColl.GetNextDocument(domDoc)

Wend

'The next section sorts the worksheet by Column H, then Column I. Delete if you don't need.

xlsheet.cells.select

xlapp.selection.sort xlsheet.range(“H1”) , 1 , xlsheet.range(“I1”) , , 1 , , , 1 , 1, False , 1, 0, 0, 0

'if statement deletes the file if it already exists - I am overwriting the same file each time

If Dir$( WindowsDirectory + “\temp\envsource.xls”) <> “” Then

Kill Environ("Windir") + "\temp\envsource.xls"

End If

On Error Goto 0

xlapp.activeworkbook.saveas WindowsDirectory + “\temp\envsource.xls”

xlapp.activeworkbook.close

xlapp.quit

xlapp = “”