Notes/Domino 4 and 5 Forum

I have done a number of exports in the past, but I have a new challenge and am hoping someone has done this before. I want to be able to have a button toexport to excel, which when clicked will prompt the user to input a date range, e.g (3/1/06 - /3/7/06) and then have it export just the records entered during that time.

Here is code I have used for other imports. I would want to propmpt for the install date range.

Sub Click(Source As Button)

Dim session As New NotesSession
Dim workspace As New NotesUIWorkspace
Dim uidoc As NotesUIDocument
Dim db As NotesDatabase
Dim view As NotesView

Dim Mgrdc As NotesDocumentcollection
Dim MgrDoc As notesDocument

Dim Repdc As NotesDocumentCollection
Dim RepDoc As NotesDocument
Dim mgrview As NotesView
Dim RepView As NotesView
Dim txt_Agency As String
Dim tmp_NotesName As NotesName
Dim MgrKey, RepKey, ADKey As String

Dim xlApp As Variant
Dim xlSheet As Variant
Dim i As Integer
Dim x As Integer
Set db = session.CurrentDatabase
Set Mgrdc = db.UnprocessedDocuments
Set Mgrdoc = Mgrdc.GetFirstDocument
On Error Resume Next

RepKey = "sots_refresh_order"
Set Repview = db.GetView( "(export_data)")
Set RepDoc = Repview.GetDocumentByKey( RepKey ) ' form name must be first colum and sorted

Print "Creating Excel Workbook..."
Set xlApp = CreateObject("Excel.application")
Print "Creating Excel Worksheet for Survey Results."
xlApp.Workbooks.Add
Set xlSheet = xlApp.Workbooks(1).Worksheets(1)

XlApp.Columns("A").Select
With XlApp.Selection
.ColumnWidth=5
.WrapText=1
.HorizontalAlignment = xlCenter
End With

XlApp.Columns("F:J").Select
With XlApp.Selection
.ColumnWidth=5
.WrapText=1
.HorizontalAlignment = xlCenter
End With

xlSheet.Cells(1,1).Value = "Install Date"
xlSheet.Cells(1,2).Value = "Office"
xlSheet.Cells(1,3).Value = "MD"
xlSheet.Cells(1,4).Value = "OM"


x = 3

While Not ( RepDoc Is Nothing ) ' as long as it has a handle on a document do the following
If RepDoc.Form(0) = "sots_refresh_order" Then

xlSheet.Rows("1:1").Select
xlSheet.Rows(1).WrapText =True
xlsheet.Columns(1).Columnwidth = 8
xlsheet.Columns(2).Columnwidth = 8
xlsheet.Columns(3).Columnwidth = 25
xlsheet.Columns(4).Columnwidth = 25

xlSheet.Cells(x,1).Value = RepDoc.install_date
xlSheet.Cells(x,2).Value = RepDoc.office_num_adjusted
xlSheet.Cells(x,3).Value = RepDoc.md_hidden
xlSheet.Cells(x,4).Value = RepDoc.om_hidden

RecNum = x - 3 ' optional
Print "Gathering Data. Record Number: " + RecNum ' optional
x = x + 1 ' increments x by one
Else ' these tell it to get the next document in the view which loops back to
Set RepDoc = Repview.GetLastDocument ' While not (RepDoc) is nothing
End If
Set RepDoc = Repview.GetNextDocument(RepDoc)
Wend

Print "Data Collection Complete."

Set xlSheet = xlApp.Workbooks(1).Worksheets("Sheet1")
Set range1 = xlsheet.Range("A2: IV300")
Call range1.Sort(xlsheet.Columns("A"), , , , , , , 1)

Msgbox "Your Survey Export is ready in Excel. Click OK and then open the Excel file flashing near the bottom of your screen. ", 64, "Excel Export"

xlApp.Visible = True
xlApp.UserControl = True

End Sub