Link to home
Start Free TrialLog in
Avatar of chakrika
chakrika

asked on

Lotus Script Help

Hi, I'm extracting some data from an excel spread sheet and placing the values in the form I'm in.
When I'm doing this, I'm actually in the form. When I click the button this agent runs and it works fine.
But I've to back out of the document and come back in again to view the imported values.

Do I have to change something here...

here is the code:
Sub Initialize
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim view As NotesView
      Dim view1 As NotesView
      Dim doc As NotesDocument      
      Set db = session.CurrentDatabase
      Set doc = session.DocumentContext
      
      
      Let AddMANBANCount = 0
      Let UpdatedMANBANCount = 0
      Let DropMANBANCount = 0
      
      Dim FileNum As Integer
      Dim xlFilename As String
      On Error Goto Errorhandler
      Filenum% = Freefile()
      xlFileName$ = Inputbox("Enter the excel file name and path here? example:C:\filename.xls")
      
      If xlFileName$="" Then
            Messagebox "The file location was not specified correctly. Please enter the file location correctly.",,"Account Information"
            Exit Sub
      End If      
      
      Dim One As String      
      Dim row As Integer
      Dim Written As Integer
      Dim Dropped As Integer
      Dim Switch_Y As String
      
      '// Next we connect to Excel and open the file. Then start pulling over the records.
      Dim Excel As Variant
      Dim xlWorkbook As Variant
      Dim xlSheet As Variant
      Print "Connecting to Excel..."
        Set Excel = CreateObject( "Excel.Application" )
      Excel.Visible = False '// Don't display the Excel window
      Print "Opening " & xlFilename & "..."
      Excel.Workbooks.Open xlFilename '// Open the Excel file
      Set xlWorkbook = Excel.ActiveWorkbook
      Set xlSheet = xlWorkbook.ActiveSheet
      
      '// Cycle through the rows of the Excel file, pulling the data over to Notes
      Goto Records
      Print "Disconnecting from Excel..."
      xlWorkbook.Close False '// Close the Excel file without saving (we made no changes)
      Excel.Quit '// Close Excel
      Set Excel = Nothing '// Free the memory that we'd used
      Print " " '// Clear the status line
      
Records:
      Let row = 0 '// These integers intialize to zero anyway
      Let Written = 0
      Let tmpnullrows=0
      
      Print "Starting import from Excel file..."
      
      Do While True
            
Process:
            With xlSheet
                  Let row = row + 1
                  
                  If .Cells (row, 1).Value = "" Then
                        Let tmpnullrows=     tmpnullrows+1
                        If tmpnullrows=2 Then
                              Goto Done
                        End If
                        Goto Process
                        
                  End If
                  
                  If .Cells (row, 1).Value = "Accounts" Then
                        Let Switch_Y = "Y"
                        Goto Process
                  End If
                  
                  Dim EMAN As String
                  Dim EMANTrim As String
                  If Switch_Y = "Y" Then            
                        Let EMAN$ = Val(.Cells( row, 1 ).Value)
                        Dim MANItem As NotesItem
                        Set MANItem = doc.GetFirstItem("MTN")
                        
                        Let EMANTrim$ = Fulltrim(EMAN$)
                        
                        Select Case Len(EMANTrim$)
                        Case "13"                  
                              Call MANItem.AppendToTextList(EMANTrim$)  
                              Let Written = Written + 1
                              Print Written
                              Let tmpnullrows=0
                        Case "12"
                              Let EMANTrim$ = EMANTrim$ & " "
                              Call MANItem.AppendToTextList(EMANTrim$)  
                              Let Written = Written + 1
                              Print "Written Count is: " & Written & " (" & EMANTrim$ & ") "
                              Let tmpnullrows=0
                        Case "10"
                              EMANTrim$ = EMANTrim$ & "   "
                              Call MANItem.AppendToTextList(EMANTrim$)    
                              Let Written = Written + 1
                              Print "Written Count is: " & Written & " (" & EMANTrim$ & ") "
                              Let tmpnullrows=0
                        Case Else
                              Let Dropped = Dropped + 1
                              Print "Dropped Count is: " & Dropped & " (" & EMANTrim$ & ") "
                              Let tmpnullrows=0
                        End Select                        
                  End If      
                  
                  Call doc.Save( True, True ) '// Save the new doc
                  
                  Goto Process
                  
            End With
      Loop
      Return
      
      
Errorhandler:
      Print "Error" & Str(Err) & ": " & Error$
      Resume Next
      
Done:
      Print "Done"
      
End Sub
ASKER CERTIFIED SOLUTION
Avatar of kalios
kalios

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of qwaletee
qwaletee

You need to use the NotesDocument coming from the open form, so that there is an association between the UI and back end.  DocumentContext won't give that to you.

Dim ws as New NotesUiWorkspace
Dim doc as nptesDocument
Set doc = ws.currentDocument.document 'ws.currentDocument is the notesUiDocument object that represents the open form, currentDocument.document is a notesDocument that is synced into the currentDocument