Solved

Exporting dynamic date range.

Posted on 2008-06-09
13
410 Views
Last Modified: 2013-12-18
I currently have a number of excel exports which are used in a BlackBerry ordering database. I am wondering the viability of doing a similar export but have the ability for the user to select a date range. prefreably by selecting from the calendar interface, if not by entering a start and end date for the report. Can I hand off the selections by the user and get it dynamically to the code?
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 ParticipantsItem As NotesItem

	

	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 = "blackberry_order_form"

	

	Set Repview = db.GetView( "(bb_export_all)")  

	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 BlackBerry Data.."

	xlApp.Workbooks.Add

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

	

	xlSheet.Cells(1,1).Value = "Name (Last, First)"     ' change your column headings

	xlSheet.Cells(1,2).Value = "Cost Center"

	xlSheet.Cells(1,3).Value = "Employee#"

	xlSheet.Cells(1,4).Value = "Hardware"

	xlSheet.Cells(1,5).Value = "Optional Add-Ons"

	xlSheet.Cells(1,6).Value = "Port Number"

	xlSheet.Cells(1,7).Value = "Ship to Name"

	xlSheet.Cells(1,8).Value = "Ship to Street"

	xlSheet.Cells(1,9).Value = "Ship to City"

	xlSheet.Cells(1,10).Value = "Ship to St"

	xlSheet.Cells(1,11).Value = "Ship to Zip"

	xlSheet.Cells(1,12).Value = "Ship to Phone No"

	xlSheet.Cells(1,13).Value = "Customer email address "

	xlSheet.Cells(1,14).Value = "Customer Telephone"

	xlSheet.Cells(1,15).Value = "Comments"

	

	x = 3

	While Not ( RepDoc Is Nothing )                 ' as long as it has a handle on a document do the following

		If  RepDoc.Form(0) = "blackberry_order_form" Then   		

			

			xlSheet.Rows("1:1").Select			

			xlSheet.Rows(1).WrapText =True

			xlsheet.Columns(1).Columnwidth = 22

			xlsheet.Columns(2).Columnwidth = 10

			xlsheet.Columns(3).Columnwidth = 10

			xlsheet.Columns(4).Columnwidth = 51

			xlsheet.Columns(5).Columnwidth = 38

			xlsheet.Columns(6).Columnwidth = 11

			xlsheet.Columns(7).Columnwidth = 17

			xlsheet.Columns(8).Columnwidth = 22

			xlsheet.Columns(9).Columnwidth = 10

			xlsheet.Columns(10).Columnwidth = 10

			xlsheet.Columns(11).Columnwidth = 10

			xlsheet.Columns(12).Columnwidth = 15

			xlsheet.Columns(13).Columnwidth = 22

			xlsheet.Columns(14).Columnwidth = 18

			xlsheet.Columns(15).Columnwidth = 50

			

			

			

			xlSheet.Cells(x,1).Value = RepDoc.name_imported

			xlSheet.Cells(x,2).Value = RepDoc.ccc

			xlSheet.Cells(x,3).Value = RepDoc.cert_adjusted

			xlSheet.Cells(x,4).Value = RepDoc.bundle

			xlSheet.Cells(x,5).Value = Join(RepDoc.addons, ";")

			xlSheet.Cells(x,6).Value = RepDoc.porting	

			xlSheet.Cells(x,7).Value = RepDoc.EE_Name

			xlSheet.Cells(x,8).Value = RepDoc.address_combined

			xlSheet.Cells(x,9).Value = RepDoc.city

			xlSheet.Cells(x,10).Value = RepDoc.state

			xlSheet.Cells(x,11).Value = RepDoc.zip

			xlSheet.Cells(x,12).Value = RepDoc.phone

			xlSheet.Cells(x,13).Value = RepDoc.email

			xlSheet.Cells(x,14).Value = RepDoc.contact_phone

			xlSheet.Cells(x,15).Value = RepDoc.comments

			

			

			RecNum = x - 3 ' optional

			Print "Gathering Data.  Record Number: " + RecNum  ' optional

			x = x + 1                    

		Else						

			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("A1: Z2000") 

	Call range1.Sort(xlsheet.Columns("A"), , , , , , , 1)

	

	

	Msgbox "Your BlackBerry Report 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

Open in new window

0
Comment
Question by:jforget1
  • 7
  • 6
13 Comments
 
LVL 7

Expert Comment

by:olaraak
ID: 21746145
It is possible, if you create a form with two date fields - start and end date. Also at form you can have field with computed-when-composed user name, to allow each user to have saved date range and avoid save conflicts.

Then, you can have a (hidden) view where those date picker documents are sorted by user name, to get one when needed.

This form can be called from LotusScript and user-picked field  values used later for date comparison.

Below is a little example how it can be done.
Of course there are other, maybe better ways to accomplish this, but it's one of them what's working.

' Example: get date document for current user

      dim vwDt as NotesView

      dim dlgDoc as NotesDocument

' Get the view with date picker documents

	Set vwDt = db.GetView("(new-view-name)")  
 

' userName is extracted earlier from session object

' user name must be in the same format as 

' saved in date picker document, otherwise we get a new document 

' at each run
 

	Set dlgDoc = vwDt.GetDocumentByKey(userName)

	

	If dlgDoc Is Nothing Then

' if there is no dialog document for this user, create one	
 

	Set dlgDoc = db.CreateDocument

	dlgDoc.Form = "fmReportDateDialog" ' a date picker form name

		

' Fill the fields, if needed, for example default dates
 

	  Call dlgDoc.Save(True,False) ' Save doc object

	End If

	

	' Open document in dialog window
 

         Dim GetUserInput as Boolean ' check, what button was pressed

	

	GetUserInput = ws.DialogBox("fmReportDateDialog",_

	True, _ ' autoHorzFit

	True, _ ' autoVertFit

	False, _ ' noCancel

	False, _ ' noNewFields

	False, _ ' noFieldUpdate

	False, _ ' readOnly

	"Report preferences", _ ' title$

	dlgDoc, _ ' notesDocument

	True, _ ' sizeToTable

	False, _ 'noOkCancel

	False) ' okCancelAtBottom

	

        If GetUserInput=False Then

		Print "Report generation cancelled by user"

		Exit Sub

	End If
 

     ' Save dialog document to get selected dates

     Call dlgDoc.Save(True,False)
 

 Dim fromDate As Variant

 Dim toDate As Variant

 Dim tmpDate As Variant

	

 fromDate = Datevalue(dlgDoc.dtReportFrom(0)) 'field in date picker doc

 toDate = Datevalue(dlgDoc.dtReportTo(0))'field in date picker doc
 

' Next lines go inside the loop

' get the date of current document
 

tmpDate =  Datevalue(RepDoc.DateFieldName(0)) ' ?or creation date?
 

 If  tmpDate >= fromDate And tmpDate <= toDate Then
 

   ' --- Fill Excel row with current document data. ---
 

 End If

Open in new window

0
 

Author Comment

by:jforget1
ID: 21909712
Sorry that this one has been open so long, I am now at the point where I really need to tackle this one. So tell me if I have this straight
Make a form with the two fields for the date and the user to allow to save these records.
Where would I put the code above, would it be in the view which has the users date ranges?
Does the user have a date range form that they have saved selected and click an action button to run the report?

I have taken a stab at inserting the code for the excel export into your code, am I setting this up correctly? I took the DIms and added them to the top, then added the other code to the area below.
' Example: get date document for current user

      dim vwDt as NotesView

      dim dlgDoc as NotesDocument

	

	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 ParticipantsItem As NotesItem

	

	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

' Get the view with date picker documents

        Set vwDt = db.GetView("(new-view-name)")  

 

' userName is extracted earlier from session object

' user name must be in the same format as 

' saved in date picker document, otherwise we get a new document 

' at each run

 

        Set dlgDoc = vwDt.GetDocumentByKey(userName)

        

        If dlgDoc Is Nothing Then

' if there is no dialog document for this user, create one      

 

        Set dlgDoc = db.CreateDocument

        dlgDoc.Form = "fmReportDateDialog" ' a date picker form name

                

' Fill the fields, if needed, for example default dates

 

          Call dlgDoc.Save(True,False) ' Save doc object

        End If

        

        ' Open document in dialog window

 

         Dim GetUserInput as Boolean ' check, what button was pressed

        

        GetUserInput = ws.DialogBox("fmReportDateDialog",_

        True, _ ' autoHorzFit

        True, _ ' autoVertFit

        False, _ ' noCancel

        False, _ ' noNewFields

        False, _ ' noFieldUpdate

        False, _ ' readOnly

        "Report preferences", _ ' title$

        dlgDoc, _ ' notesDocument

        True, _ ' sizeToTable

        False, _ 'noOkCancel

        False) ' okCancelAtBottom

        

        If GetUserInput=False Then

                Print "Report generation cancelled by user"

                Exit Sub

        End If

 

     ' Save dialog document to get selected dates

     Call dlgDoc.Save(True,False)

 

 Dim fromDate As Variant

 Dim toDate As Variant

 Dim tmpDate As Variant

        

 fromDate = Datevalue(dlgDoc.dtReportFrom(0)) 'field in date picker doc

 toDate = Datevalue(dlgDoc.dtReportTo(0))'field in date picker doc

 

' Next lines go inside the loop

' get the date of current document

 

tmpDate =  Datevalue(RepDoc.DateFieldName(0)) ' ?or creation date?

 

 If  tmpDate >= fromDate And tmpDate <= toDate Then

 

   ' --- Fill Excel row with current document data. ---

 	RepKey = "blackberry_order_form"

	

	Set Repview = db.GetView( "(bb_export)")  

	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 BlackBerry Data.."

	xlApp.Workbooks.Add

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

	

	xlSheet.Cells(1,1).Value = "Name (Last, First)"     ' change your column headings

	xlSheet.Cells(1,2).Value = "Cost Center"

	xlSheet.Cells(1,3).Value = "Employee#"

	xlSheet.Cells(1,4).Value = "Product(Bundle)"

	xlSheet.Cells(1,5).Value = "Optional Add-Ons"

	xlSheet.Cells(1,6).Value = "Port Number"

	xlSheet.Cells(1,7).Value = "Ship to Name"

	xlSheet.Cells(1,8).Value = "Ship to Street"

	xlSheet.Cells(1,9).Value = "Ship to City"

	xlSheet.Cells(1,10).Value = "Ship to St"

	xlSheet.Cells(1,11).Value = "Ship to Zip"

	xlSheet.Cells(1,12).Value = "Ship to Phone No"

	xlSheet.Cells(1,13).Value = "Customer email address "

	xlSheet.Cells(1,14).Value = "Customer Telephone"

	xlSheet.Cells(1,15).Value = "Comments"

	

	x = 3

	While Not ( RepDoc Is Nothing )                 ' as long as it has a handle on a document do the following

		If  RepDoc.Form(0) = "blackberry_order_form" Then   		

			

			xlSheet.Rows("1:1").Select			

			xlSheet.Rows(1).WrapText =True

			xlsheet.Columns(1).Columnwidth = 22

			xlsheet.Columns(2).Columnwidth = 10

			xlsheet.Columns(3).Columnwidth = 10

			xlsheet.Columns(4).Columnwidth = 51

			xlsheet.Columns(5).Columnwidth = 38

			xlsheet.Columns(6).Columnwidth = 11

			xlsheet.Columns(7).Columnwidth = 17

			xlsheet.Columns(8).Columnwidth = 22

			xlsheet.Columns(9).Columnwidth = 10

			xlsheet.Columns(10).Columnwidth = 10

			xlsheet.Columns(11).Columnwidth = 10

			xlsheet.Columns(12).Columnwidth = 15

			xlsheet.Columns(13).Columnwidth = 22

			xlsheet.Columns(14).Columnwidth = 18

			xlsheet.Columns(15).Columnwidth = 50

			

			

			

			xlSheet.Cells(x,1).Value = RepDoc.name_imported

			xlSheet.Cells(x,2).Value = RepDoc.ccc

			xlSheet.Cells(x,3).Value = RepDoc.cert_adjusted

			xlSheet.Cells(x,4).Value = RepDoc.bundle_combined

			xlSheet.Cells(x,5).Value = Join(RepDoc.addons, ";")

			xlSheet.Cells(x,6).Value = RepDoc.porting	

			xlSheet.Cells(x,7).Value = RepDoc.EE_Name

			xlSheet.Cells(x,8).Value = RepDoc.address_combined

			xlSheet.Cells(x,9).Value = RepDoc.city

			xlSheet.Cells(x,10).Value = RepDoc.state

			xlSheet.Cells(x,11).Value = RepDoc.zip

			xlSheet.Cells(x,12).Value = RepDoc.phone

			xlSheet.Cells(x,13).Value = RepDoc.email

			xlSheet.Cells(x,14).Value = RepDoc.contact_phone

			xlSheet.Cells(x,15).Value = RepDoc.comments

			

			RecNum = x - 3 ' optional

			Print "Gathering Data.  Record Number: " + RecNum  ' optional

			x = x + 1                    

		Else						

			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("A1: Z2000") 

	Call range1.Sort(xlsheet.Columns("A"), , , , , , , 1)

	

	

	Msgbox "Your BlackBerry Report 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 If

Open in new window

0
 
LVL 7

Expert Comment

by:olaraak
ID: 21913987
Hi
The code will be started from action button, placed in the user view with documents to get collection based on date range. It can be placed in action code or in agent, started by action.

Date range selection documents are created by small form "fmReportDateDialog".
Those docs store previous date range selected by user.

Date range selection documents are listed in hidden view, here named "(new-view-name)", you select better name for it. This view's first column is sorted by user name.

When testing code, check that in LotusScript options, you have the "Option Declare" mentioned. It ensures all variables are declared before using them.

Test code step-by-step, placing "Exit Sub" statements in code.

For example, at first stop code on row 75, to see if date selection part works OK. Then remove it and see if code in loop works.

Also use error trapping code, by "on error goto errorhandler" and Error functions/properties.
Use LotusSript Debugger to see what is going on.
0
 

Author Comment

by:jforget1
ID: 21916358
Trying to get all this code together, I am getting red errors at 3 points and I cannot figure out why.

Fist 2 are coming up red
      Set dlgDoc = vwDt.GetDocumentByKey(username)
      
                        RecNum = x - 3

This has Variable not declared, have used this many time and never seen this error.
      Set range1 = xlsheet.Range("A1: Z2000")
Sub Click(Source As Button)

	' Example: get date document for current user

	Dim vwDt As NotesView

	Dim dlgDoc As NotesDocument

	

	Dim session As New NotesSession

	Dim ws 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 ParticipantsItem As NotesItem

	

	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

' Get the view with date picker documents

	Set vwDt = db.GetView("(date_selections)")  

	

' userName is extracted earlier from session object

' user name must be in the same format as 

' saved in date picker document, otherwise we get a new document 

' at each run

	

	Set dlgDoc = vwDt.GetDocumentByKey(username)

	

	If dlgDoc Is Nothing Then

' if there is no dialog document for this user, create one      

		

		Set dlgDoc = db.CreateDocument

		dlgDoc.Form = "fmReportDateDialog" ' a date picker form name

		

' Fill the fields, if needed, for example default dates

		

		Call dlgDoc.Save(True,False) ' Save doc object

	End If

	

        ' Open document in dialog window

	

	Dim GetUserInput As Boolean ' check, what button was pressed

	

	GetUserInput = ws.DialogBox("fmReportDateDialog",_

	True, _ ' autoHorzFit

	True, _ ' autoVertFit

	False, _ ' noCancel

	False, _ ' noNewFields

	False, _ ' noFieldUpdate

	False, _ ' readOnly

	"Report preferences", _ ' title$

	dlgDoc, _ ' notesDocument

	True, _ ' sizeToTable

	False, _ 'noOkCancel

	False) ' okCancelAtBottom

	

	If GetUserInput=False Then

		Print "Report generation cancelled by user"

		Exit Sub

	End If

	

     ' Save dialog document to get selected dates

	Call dlgDoc.Save(True,False)

	

	Dim fromDate As Variant

	Dim toDate As Variant

	Dim tmpDate As Variant

	

	fromDate = Datevalue(dlgDoc.start_date(0)) 'field in date picker doc

	toDate = Datevalue(dlgDoc.end_date(0))'field in date picker doc

	

' Next lines go inside the loop

' get the date of current document

	

	tmpDate =  Datevalue(RepDoc.Created(0)) ' ?or creation date?

	

	If  tmpDate >= fromDate And tmpDate <= toDate Then

		

   ' --- Fill Excel row with current document data. ---

		RepKey = "ibpc_user)record"

		

		Set Repview = db.GetView( "(ibpc_export)")  

		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 BlackBerry Data.."

		xlApp.Workbooks.Add

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

		

		xlSheet.Cells(1,1).Value = "Name"     ' change your column headings

		xlSheet.Cells(1,2).Value = "Office ID"

		xlSheet.Cells(1,3).Value = "Order Type"

		

		

		x = 3

		While Not ( RepDoc Is Nothing )                 ' as long as it has a handle on a document do the following

			If  RepDoc.Form(0) = "ibpc_user_record" Then   		

				

				xlSheet.Rows("1:1").Select			

				xlSheet.Rows(1).WrapText =True

				xlsheet.Columns(1).Columnwidth = 22

				xlsheet.Columns(2).Columnwidth = 10

				xlsheet.Columns(3).Columnwidth = 10

				

				xlSheet.Cells(x,1).Value = RepDoc.username

				xlSheet.Cells(x,2).Value = RepDoc.office_number

				xlSheet.Cells(x,3).Value = RepDoc.dlm_vap

				RecNum = x - 3 

				Print "Gathering Data.  Record Number: " + RecNum  ' optional

				x = x + 1                    

			Else						

				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("A1: Z2000") 

		Call range1.Sort(xlsheet.Columns("A"), , , , , , , 1)

		

		

		Msgbox "Your IBPC Refresh Report 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 If

Open in new window

0
 
LVL 7

Expert Comment

by:olaraak
ID: 21923071
1. "username" was not declared, it was a placeholder in example.
Use something like "session.UserName" instead of it.

Or declare userName variable and fill it like:

dim useName As String
userName = session.Username

Then when creating dlgDoc, save the same name in User name field. Field can be named differently, to avoid a confusion about everything named username.

dlgDoc.fieldUserName = userName

In dlgDoc, you can pre-fill date fields, using NotesDate object and NotesDate.Adjust methtod.
---
If you have Option Declare active, then you are forced to declare all variables, even Variants.
Without it, every undeclared variable becomes Variant.

Option declare option issues errors on undeclared variables.

It is a little bit annoying at first, but forces a good habit of declaring everything, it helps to avoid type mismatch errors or too many Variants in future. Variants are the slowest and most complex variables, it is always good to use Integers for numbers in loops, under 32K range and Long for bigger loops/numbers.
0
 

Author Comment

by:jforget1
ID: 21976739
OK making progress, got all the Dims done. Now I am getting an Object Variable Not Set on the line below yet both of these are Dimmed in the code. And vwDt is set just above it.

    Set dlgDoc = vwDt.GetDocumentByKey(userName)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Expert Comment

by:olaraak
ID: 21976918
Double-Check the vwDt name.

If db is opened OK, the wrong view name is the number 1 reason for Object variable not set error.
Check user name format also.
0
 

Author Comment

by:jforget1
ID: 21982813
I took the name of the view from the view prop and pasted it right into the code. I do see a VWDT in debugger but I do not have any details for this item, just that and Notes View to the right. I also checked the username for the session and the value of the first column of my view, which is sorted and they match. Strange it seems like it should be working here. Will keep plugging away.
0
 
LVL 7

Expert Comment

by:olaraak
ID: 21983017
If you see a variable VWDT but not its content in the middle of debugger screen, then the view is not initialized, resulting in Obj. var. not set error.

Probably the view name is still wrong.

 Maybe you have no parentheses around view name? Example code was using hidden view "(viewname)".  If your view is not hidden, remove the inner parentheses.
0
 

Author Comment

by:jforget1
ID: 21984222
Got past that one, now I am stuck on the line below. Where in the original code do I link this check to the underlying user record that I am evaluating the creation date. Do I need to do some sort of Set RepDoc = doc.ibpc_user_record?


    tmpDate =  Datevalue(RepDoc.Created(0))
0
 
LVL 7

Expert Comment

by:olaraak
ID: 21985072
As I understand, in original code you filter out documents with form blackberry_order_form
and now you want to set additional filter for only rows for selected time period?

Then add date comparison after check for document form.

1. Check form
2. Get creation date of document
3. Compare if the creation date is within date period.
4. If it is, write data to Excel rows.
5. Get next document

BTW, when using doc.Created property, maybe the tmpDate variable is not needed.
doc.Created is a Date type, so comparison might success without conversions. But i'll rather test  it before, date and time calculations are tricky in LotusScript.

Also, as doc.Created is property, not field, its value can be accessed with "doc.Created", not doc.Created(0). Latter is needed for field values, as they are always in the form of array.

   


Old code:
 

    x = 3

    While Not ( RepDoc Is Nothing )   ' While handle...            

        If  RepDoc.Form(0) = "blackberry_order_form" Then  
 

' get date value

' if you use doc.Created property, then (0) is not needed

' as with field values
 

            tmpDate =  Datevalue(RepDoc.Created(0))
 

           xlSheet.Rows("1:1").Select

            xlSheet.Rows(1).WrapText =True

	    xlsheet.Columns(1).Columnwidth = 22

			

	    ' --- other rows skipped 
 
 

' -------------------- New code -------------------
 

 While Not ( RepDoc Is Nothing )   ' While handle...

            

        If  RepDoc.Form(0) = "blackberry_order_form" Then  
 

           tmpDate =  Datevalue(RepDoc.Created)  ' 
 

' doc.Created is document class property, no (0) needed as

' for field items       
 

       If  tmpDate >= fromDate And tmpDate <= toDate Then
 

            xlSheet.Rows("1:1").Select

            xlSheet.Rows(1).WrapText =True

	    xlsheet.Columns(1).Columnwidth = 22

			

	    ' --- other rows skipped 

Open in new window

0
 

Author Comment

by:jforget1
ID: 21985167
The issue I seem to be having is happening higher up in the before it gets to the excel part, in the third line below
	

' Next lines go inside the loop

' get the date of current document

	

	tmpDate =  Datevalue(RepDoc.Created(0)) ' ?or creation date?

	

	If  tmpDate >= fromDate And tmpDate <= toDate Then

		

   ' --- Fill Excel row with current document data. ---

		RepKey = "ibpc_user)record"

		

		Set Repview = db.GetView( "(ibpc_export)")  

		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 BlackBerry Data.."

		xlApp.Workbooks.Add

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

		

		xlSheet.Cells(1,1).Value = "Name"     ' change your column headings

		xlSheet.Cells(1,2).Value = "Office ID"

		xlSheet.Cells(1,3).Value = "Order Type"

		

		

		x = 3

		While Not ( RepDoc Is Nothing )                 ' as long as it has a handle on a document do the following

			If  RepDoc.Form(0) = "ibpc_user_record" Then   		

				

				xlSheet.Rows("1:1").Select			

				xlSheet.Rows(1).WrapText =True

				xlsheet.Columns(1).Columnwidth = 22

Open in new window

0
 
LVL 7

Accepted Solution

by:
olaraak earned 500 total points
ID: 21985985
You are trying to use RepDoc.  at row 5 before the document object is created on row 13

Move rows 5 and 7 after row 28

In the same way, I suggest you need to refactor your code, remove unnecessary declarations and move logical pieces into separate functions/subs.

Otherwise it is very hard to follow, what's going on inside the code.



0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now