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(E MANTrim$)
Let Written = Written + 1
Print Written
Let tmpnullrows=0
Case "12"
Let EMANTrim$ = EMANTrim$ & " "
Call MANItem.AppendToTextList(E MANTrim$)
Let Written = Written + 1
Print "Written Count is: " & Written & " (" & EMANTrim$ & ") "
Let tmpnullrows=0
Case "10"
EMANTrim$ = EMANTrim$ & " "
Call MANItem.AppendToTextList(E MANTrim$)
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
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(E
Let Written = Written + 1
Print Written
Let tmpnullrows=0
Case "12"
Let EMANTrim$ = EMANTrim$ & " "
Call MANItem.AppendToTextList(E
Let Written = Written + 1
Print "Written Count is: " & Written & " (" & EMANTrim$ & ") "
Let tmpnullrows=0
Case "10"
EMANTrim$ = EMANTrim$ & " "
Call MANItem.AppendToTextList(E
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dim ws as New NotesUiWorkspace
Dim doc as nptesDocument
Set doc = ws.currentDocument.documen