asked on

how to upload excel file to notes using LS

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

HemanthaKumar

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

amd1979

ASKER

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.

amd1979

ASKER

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

HemanthaKumar

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

amd1979

ASKER

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

HemanthaKumar

I don't see

Set doc = nv.getdocumentbykey(testcode,True)

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

amd1979

ASKER

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.

amd1979

ASKER

i think there was a problem refreshing the my documents.

what do you think?

ASKER CERTIFIED SOLUTION

HemanthaKumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial