Solved

how to upload excel file to notes using LS

Posted on 2004-03-29
9
231 Views
Last Modified: 2013-12-18
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...











0
Comment
Question by:amd1979
  • 5
  • 4
9 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 10709839
For that you have to consider this section of the 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
Else ' There is already a document
               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

~Hemanth
0
 

Author Comment

by:amd1979
ID: 10710044
how about the category field? there is no cell for category on excel.

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


amd.
0
 

Author Comment

by:amd1979
ID: 10712395
i got the code by using  INSTR(.....) ,

but one more thing  i dont want to include those code that didnt start with letter plus a high pen
this are the 3  and only allowed to be included values..
"I-....",  "S-..." , "OSEC-....."

thanks...
my code below..


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 (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("vDocUp")
      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
      Dim rowi As Integer
      Dim catsub 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))
            faction=Trim(Cstr(xlapp.cells(rowi,13).value))
            
            
            
            catsub=Ucase(subject)
            
            testcode=Ucase(code)
            
            Set doc = nv.getdocumentbykey(testcode,True)
            If doc  Is Nothing  Then              
                  Set doc=db.CreateDocument
                'select form ducument                  
                  doc.form="fDocument"                   
                  doc.code=code
                  doc.suggecomm= notes + Chr(13) + type2 + Chr(13) + dtrem
                  doc.dtreceive=dtreceive
                  doc.sender=Sender
                  doc.ltrdated=Ltrdtd
                  doc.subject=Subject
                  doc.SecRem=Secrem
                  doc.refto=refto
                  doc.dtref=dtref
                  doc.status=curstat
                  doc.Ftaken=Faction
            Else ' There is already a document
                  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      
            
' checking for categories and assign values
            If  Instr( CODE, "I-") Then
                  doc.categories="Invitation"
            Elseif Instr(CODE,"S-") Then
                  doc.categories="For Signature"
            Elseif Instr(CODE,"OSEC") Then
                  If Instr(Subject,"CONFI") Then
                        doc.categories="Confidential"
                  Else
                        doc.categories="General Documents"
                  End If
            End If
            
            Call doc.save(True,False)
            
            rowi=rowi + 1
            testvar=Trim(Cstr(xlApp.cells(rowi,1).value))
      Wend      
      Call WS.ViewRefresh
      xlapp.cells(1,1).select
      Print "Finished uploadingyour file.",0+64+0+0,"Upload"
      xlApp.Visible = True
End Sub

0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 10713656
Meaning you want only those codes to be imported/updated and not the other ones.. this can be controlled like this .. by shifting instring code above the main if document like this


           category = ""
           If  Instr( CODE, "I-") Then
               category="Invitation"
          Elseif Instr(CODE,"S-") Then
               category="For Signature"
          Elseif Instr(CODE,"OSEC") Then
               If Instr(Subject,"CONFI") Then
                    category="Confidential"
               Else
                    category="General Documents"
               End If
          End If

         Set doc = nv.getdocumentbykey(testcode,True)
         If doc  Is Nothing  or category <> "" Then              
               Set doc=db.CreateDocument
                'select form ducument              
               doc.form="fDocument"                
               doc.code=code
               doc.suggecomm= notes + Chr(13) + type2 + Chr(13) + dtrem
               doc.dtreceive=dtreceive
               doc.sender=Sender
               doc.ltrdated=Ltrdtd
               doc.subject=Subject
               doc.SecRem=Secrem
               doc.refto=refto
               doc.dtref=dtref
               doc.status=curstat
               doc.Ftaken=Faction
          Else ' There is already a document
               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  



0
What Security Threats Are You Missing?

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.

 

Author Comment

by:amd1979
ID: 10722807
something weird is happenning on my code..
here we goes:

i have a test view  that i used to call the 'sub load1" function to  upload data from excel to notes the view name is above;
if i run my program  i acheive my desire output which is to create new document if it found nothing. and update only the documents if the code is already existing.


my problem begins when finalizing the final form to call the "sub load1" function,

on the firsttime that  uploaded the file from excel to notes my program didnt recognized that the document is already existing. example:

I-04-03-104       subject 1
I-04-03-104       subject 2
I-04-03-104        subject 2

my code will upload those 3 documents, this problem only  occured on the firstime that i uploaded
the said excel file.  but if im going to upload again the same file it didnt create duplicate.

here is 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 Isempty(xlsFileName) Then
            Exit Sub
      End If
      
      Dim testfile As String
      testfile=Cstr(xlsFileName(0))
      If Instr(testfile,".xls")=0 Then
            Msgbox "Invalid excel file"+_
            Chr(13)+Chr(13)+"Upload aborted" ,0+16+0+0,"Upload"
            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 (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("vDocUp")
      
      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
      Dim rowi As Integer
      Dim catsub As String
      Dim tvar As Variant
      Dim fstr As String
'      Dim ndcoll As NotesdocumentCollection
      
      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(Cstr(xlapp.cells(rowi,3).value))
            type2=Trim(Cstr(xlapp.cells(rowi,4).value))
            Sender=Trim(Cstr(xlapp.cells(rowi,5).value))
            ltrdated=Trim(Cdat(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(Cdat(xlapp.cells(rowi,10).value))
            dtrem=Trim(Cstr(xlapp.cells(rowi,11).value))
            curstat=Trim(Cstr(xlapp.cells(rowi,12).value))
            faction=Trim(Cstr(xlapp.cells(rowi,13).value))
            
            
            catsub=Ucase(subject)
            testcode=Ucase(code)
            
            ' checking for categories and assign value            
            category = ""
            If  Instr(CODE,"I-")>0 Then
                  category="Invitation"
            Elseif Instr(CODE,"S-")>0 Then
                  category="For Signature"
            Elseif Instr(CODE,"OSEC-")>0 Then
                  If Instr(Subject,"CONFIDENTIAL")>0 Then
                        category="Confidential"
                  Else
                        category="General Documents"
                  End If
            End If
            
            
            If doc  Is Nothing  And Not(category ="") Then              
                  
                  Set doc=db.CreateDocument
               'select form ducument                  
                  doc.form="fDocument"                   
                  doc.code=code
                  doc.hasEditor=""
                  doc.datainput="old"
                  doc.StatusNum="0"
                  doc.DocST1txt=""
                  doc.Status="Draft"
                  tvar=Evaluate(|@Unique|)
                  doc.tmpID=Cstr(tvar(0))
                  tvar=Evaluate(|@ProperCase(@Name([CN];@UserName))|)
                  doc.DEditedBy=Cstr(tvar(0))
                  doc.DEditedOn=getServerDate
                  doc.DAction="Data uploaded"
                  doc.categories=category
            End If
            
            If Not (doc Is Nothing)  Then
                  If Cstr(doc.hasEditor(0))="" Then
                        doc.suggecomm= curstat +Chr(13) + notes + Chr(13) + type2 + Chr(13) + dtrem
                        doc.dtreceive=Cdat(dtreceive)
                        doc.sender=Sender
                        doc.ltrdated=Cdat(Ltrdtd)
                        doc.subject=Subject
                        doc.SecRem=Secrem
                        doc.refto=refto
                        doc.dtref=Cdat(dtref)
                        doc.Ftaken=Faction
                        Call doc.save(True,False)                        
                        Call WS.ViewRefresh
                  End If
            End If
            Print " Finished uploading "  +  Ucase(Code) +":  " + Cstr(rowi)
            
            
            rowi=rowi + 1
            testvar=Trim(Cstr(xlApp.cells(rowi,1).value))
      Wend      
      
      xlapp.cells(1,1).select
      Print "Finished uploading your file."
      xlApp.Visible = True
End Sub






















0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 10724043
I don't see

Set doc = nv.getdocumentbykey(testcode,True)


So that everytime doc is nothing and it will create duplicates ??
0
 

Author Comment

by:amd1979
ID: 10728579
sorry i accidentally deleted this code
Set doc = nv.getdocumentbykey(testcode,True)
when i paste this code here. but this code is included on my LS.

my code didnt recognized that the doc is already existing, but this problem occured only on the firstime that i uploaded a certain file.








0
 

Author Comment

by:amd1979
ID: 10728735
i think there was a problem refreshing the my documents.

what do you think?
0
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 250 total points
ID: 10728872
I am confused.. we were working on one code and you presented me with a new code now.. There is no save in first if conditon where you create document and how come you see duplicates ??
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
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…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 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

21 Experts available now in Live!

Get 1:1 Help Now