troubleshooting Question

how to upload excel file to notes using LS

Avatar of amd1979
amd1979 asked on
Lotus IBM
9 Comments1 Solution297 ViewsLast Modified:
i have a collection of data on excel that i want to upload to excel.

here we goes:

i have a view that i called "vcode"
this view is a colection of all documents code which is unique or the code is my primary key.
if the code start with letter S-..... category is for signature. if I-....category is for Invitation if OSEC is for General documents but i have a problem with the last Category which is Confidential it is also start with OSEC- the only thing the will describe them as a confidentiaL is the field "subject" its contain a word "cofidential".

this is are the things that i want to happen:

if the document code is existing it will only update the other fields,

but if the document code IS NOTHING it will createdocument. here is the initial code that i made to help you familiarized the cell value and the corresponding field value on my notes document please correct my code:



Sub load1
      Dim ws As New NotesUIWorkspace
      Dim xlfilename As Variant
      Dim szFilter As String
      
      szFilter = "Excel Spreadsheet|*.xls|All Files|*.*|"
      xlsFileName = ws.OpenFileDialog (False, "Select Spreadsheet to upload", szFilter)
      If xlsFileName(0) ="" Then
            Exit Sub
      End If
      
      Dim xlApp As Variant, xlsheet As Variant,xlWbk As Variant
      Set xlApp = CreateObject("Excel.Application") 'start Excel with OLE Automation
      xlApp.StatusBar = "Creating WorkSheet. Please be patient..."
      
      Set xlWbk = xlApp.Workbooks.Open(xlsFileName(0))
      Set xlsheet = xlWbk.Worksheets(1) 'select first worksheet
      Call xlSheet.Activate
      Call InitExcel2(xlApp,xlsheet, xlWbk )
      
      
      If Not(Trim(Cstr(xlApp.cells(1,1).value))="#") Then
            Msgbox "File not valid for upload.",0+48+0+0,"Error"
            xlApp.Visible = True
            Exit Sub
      End If
      
      
      Dim s As New NotesSession
      Dim db As NotesDatabase
      Dim nv As NotesView
      Set db=s.CurrentDatabase
      Set nv=db.Getview("vCode")
      Dim doc As notesDocument
      Dim code As String
      Dim notes As String
      Dim dtreceive As String
      Dim type2 As String
      Dim Sender As String
      Dim ltrdated As String
      Dim subject As String
      Dim secrem As String
      Dim refto As String
      Dim dtref As String
      Dim dtrem As String
      Dim curstat  As String
      Dim faction As String
      Dim testvar As String
      Dim testcode As String
      
      rowi=1
      testvar=Trim(Cstr(xlApp.cells(rowi,1).value))
      While Not (testvar="")
            
            
            code=Trim(Cstr(xlapp.cells(rowi,1).value))
            notes=Trim(Cstr(xlapp.cells(rowi,2).value))
            dtreceive=Trim(Cdat(xlapp.cells(rowi,3).value))
            type2=Trim(Cstr(xlapp.cells(rowi,4).value))
            Sender=Trim(Cstr(xlapp.cells(rowi,5).value))
            ltrdated=Trim(Cstr(xlapp.cells(rowi,6).value))
            subject=Trim(Cstr(xlapp.cells(rowi,7).value))
            secrem=Trim(Cstr(xlapp.cells(rowi,8).value))
            refto=Trim(Cstr(xlapp.cells(rowi,9).value))
            dtref=Trim(Cstr(xlapp.cells(rowi,10).value))
            'dtrem=Trim(Cstr(xlapp.cells(rowi,11).value))
            curstat=Trim(Cstr(xlapp.cells(rowi,12).value))
            'ftaken=Trim(Cstr(xlapp.cells(rowi,13).value))
            faction=Trim(Cstr(xlapp.cells(rowi,14).value))
            
            
            
            testcode=Ucase(code)
            
            Set doc = nv.getdocumentbykey(testcode,True)
            If doc  Is Nothing  Then
                  
                  Set doc=db.CreateDocument
                  doc.code=code
                  doc.suggecomm=notes
                  doc.dtreceive=dtreceive
                  doc.sender=Sender
                  doc.ltrdated=Ltrdtd
                  doc.subject=subject
                  doc.SecRem=Secrem
                  doc.refto=refto
                  doc.dtref=dtref
                  'doc.
                  doc.status=curstat
                  doc.Ftaken=Faction
            End If
            
            Call doc.save(True,False)
            
            rowi=rowi + 1
            tesvar=Trim(Cstr(xIapp.cell(rowi,1).value))
      Wend      
      xIapp.cells(1,1).select
      Print "Finished uploadingyour file.",0+64+0+0,"Upload"
      xIapp.visibible=True
      
End Sub




thanks in advance...











Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros