Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Exporting dynamic date range.

Posted on 2008-06-09
13
Medium Priority
?
430 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

604 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