?
Solved

Import from Excel-New Scenario

Posted on 2006-05-11
8
Medium Priority
?
317 Views
Last Modified: 2013-12-18
Just when I think I got what they want, the scenario changes.  Need the below code changed like this . If a document exists in the view with the field batch, don't overwrite the document just update the fields in the document. Also there will be a field in the document named release. I don't want this field touched at all. I want the value left as is.  Again, here is the code I am using kindly provided by Marilyng.

Dim ws As New NotesUIWorkSpace
     Dim ses As New NotesSession
     Dim db  As NotesDatabase
     Set db = ses.currentdatabase
     Dim view As NotesView
     Dim importdoc As NotesDocument
     
     On Error Goto Handle_Error    
     
     'Rather than write to a text file, why not create a mail log and mail it to yourself?
     Dim importlog As NotesLog
     Dim sendTo As String
     
     
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
'>>>YOU MUST ENTER YOUR EMAIL ADDRESS BELOW<<<
     SendTo="David P Schmalzer"
     
     Set importlog = New NotesLog( "Import from Excel" )
     Call importlog.OpenMailLog ( SendTo, "Import from Excel" & " in " & db.Title )
     Call importlog.LogAction("Started the Excel Import BATCH overwrite at " + Format(Now))
     On Error Goto Handle_Error
     
     Dim App As Variant, Wbook As Variant, WSheet As Variant
     
     Dim RetVal As Variant
     Dim XLfile As String
     Dim row As Long
     
     'Choose Excel file
     XLfile = "f:\cabinets\schedule\oplan\onetfold\report\today\test\schedwet1.xls"
     
     'get Excel Object
     Print "Preparing to import..."
     Set App = CreateObject("Excel.Application")
     If App Is Nothing Then
          Msgbox "You do not have Excel installed.", 0 + 16, "Import from excel"
          Exit Sub
     End If
     
     App.Visible = False      
     App.Workbooks.Open XLFile
     Set Wbook = App.ActiveWorkbook
     Set WSheet = Wbook.ActiveSheet
     
     Set db = ses.CurrentDatabase
     Set view =  db.GetView("YieldImport")    
     Dim importCnt As Long ,Batch As String, skipCnt As Long
     
     importCnt = 0
     skipcnt = 0
     
     row = 2 '<--- skip the column header use 1 if your file has no column header
     While Trim(Cstr(WSheet.Cells(row, 1).Value)) <> ""      
          Batch = Trim(Cstr(WSheet.Cells(row, 1).Value))
          Print "Importing " + Cstr(row)
           '-----> check if IMPORTdoc already exists          
          Set importdoc = view.GetDocumentByKey(Batch, True)          
          If importdoc Is Nothing Then  
              'Then create the document if there is no match
               Set importdoc = db.createDocument  
               'There is NO match, so DON't overwrite, but add to log
               Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
               skipcnt = skipcnt +1
               'Goto nextRow
          End If    
          With importdoc
               .form = "YieldSheet1"
               .batch = batch
               .fp = Trim(Cstr(WSheet.Cells(row, 2).Value))
               .s_a = Trim(Cstr(WSheet.Cells(row, 3).Value))
               .ipl_code = Trim(Cstr(WSheet.Cells(row, 4).Value))
               .dept = Trim(Cstr(WSheet.Cells(row, 5).Value))
               .descriptor = Trim(Cstr(WSheet.Cells(row, 6).Value))
               .qty = Trim(Cstr(WSheet.Cells(row, 7).Value))
               .g = Trim(Cstr(WSheet.Cells(row, 8).Value))
               .startdate = Trim(Cstr(WSheet.Cells(row, 9).Value))
               .due_date = Trim(Cstr(WSheet.Cells(row, 10).Value))
               .type = Trim(Cstr(WSheet.Cells(row, 11).Value))    
               .save True, False,True    
          End With          
          importCnt = importCnt + 1    
NextRow:
          row = row + 1
         
     Wend
     
     On Error Goto 0
     
     Print "Disconnecting from Excel..."
     App.activeworkbook.close    
     App.Quit '// Close Excel
     Set App= Nothing '// Free the memory that we'd used
     
'----------------------------------------
     Call view.Refresh
     
     Call importlog.LogAction("Finished the BATCH import at: " + Format(Now))
     Call importlog.LogAction("Imported: " + Cstr(importCnt) + " records")
     Call importlog.LogAction("Skipped: " + Cstr(skipCnt) + " records")
     Call importlog.close
     Exit Sub
Handle_Error:
     On Error Goto 0
     If Not importlog Is Nothing Then
          Call importlog.LogAction("Error processing import: " + Error$ + "-" +Str(Err))
          Call importlog.close
     End If
     
     
     Print "Disconnecting from Excel..."
     App.activeworkbook.close    
     App.Quit '// Close Excel
     Set App= Nothing '// Free the memory that we'd used    
0
Comment
Question by:schmad01
  • 4
  • 3
8 Comments
 
LVL 63

Expert Comment

by:SysExpert
ID: 16663539
What is the difference between this and your previous Q ?

Also - Again, the Release field is not defined anywhere ( unless you are using a different name ), so it is never changed in the code provided.

You need to dig a little deeper.

Please provide a full list of all of the fields on the form so that we attempt to match them up, or tell us which column is the Release column in your imported data ?

I hope this helps !


0
 
LVL 14

Expert Comment

by:cezarF
ID: 16664612
what you want is already being done in this part of your code....

          Set importdoc = view.GetDocumentByKey(Batch, True)          
          If importdoc Is Nothing Then  
              'Then create the document if there is no match
               Set importdoc = db.createDocument  
               'There is NO match, so DON't overwrite, but add to log
               Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
               skipcnt = skipcnt +1
               'Goto nextRow
          End If    
0
 

Author Comment

by:schmad01
ID: 16664839
Sorry, something wasn't working right with the EE site yesterday and somehow I posted the wrong question. I will post the right one when I get into work in the morning.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:schmad01
ID: 16667158
Ok, this is what I meant to post.  The above code still stands but here is the new scenario.  Ok, I import the data from the excel spreadsheet into a notes view called "YieldImport". Ok, that works great. Now , in this view I have a button called "ReleasetoProd". When I click this button it updates a field on form "Yieldsheet1" called "release" from "No"(default value) to "Yes".  This moves form from the import view to another view called "Released to Prod".

The problem is that if I hit the "Import from excel" button again in the "YieldImport" view, it will add another instance of the document I just moved because it thinks it doesn't exist. This can't happen so I guess I need an amendment that will check the "Released to Prod" view as well, and if exists, update the fields as before, but don't add another document in the "YieldImport" view.

Hope this makes sense.
0
 
LVL 14

Expert Comment

by:cezarF
ID: 16667330
use another view that displays all Yieldsheet1 docs (regardless of the value of the release field) and use it to check if the doc exists in your code above.

Set view =  db.GetView("YieldImport")  to Set view =  db.GetView("NewView")        
0
 
LVL 14

Accepted Solution

by:
cezarF earned 1000 total points
ID: 16667370
or if your "Released to Prod" is sorted by Batch,  add another check.

Dim viewProd As NotesView
Set viewProd  =  db.GetView("Released to Prod")

           '-----> check if IMPORTdoc already exists          
          Set importdoc = view.GetDocumentByKey(Batch, True)          
 --->   If importdoc Is Nothing Then  
 --->         Set importdoc = viewProd.GetDocumentByKey(Batch, True)          
 --->   End If
          If importdoc Is Nothing Then  
              'Then create the document if there is no match
               Set importdoc = db.createDocument  
               'There is NO match, so DON't overwrite, but add to log
               Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
               skipcnt = skipcnt +1
               'Goto nextRow
          End If    
0
 

Author Comment

by:schmad01
ID: 16667667
Worked like a charm! Thanks cezar!
0
 
LVL 14

Expert Comment

by:cezarF
ID: 16667697
you're welcome. thanks for the points :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

829 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