?
Solved

Import from excel again

Posted on 2006-05-10
8
Medium Priority
?
269 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
  • 3
  • 3
  • 2
8 Comments
 
LVL 63

Expert Comment

by:SysExpert
ID: 16654491
Need to know which column contains the "release" field

SO all the other fieds can be imported overwriting the info except Release ?

add
Dim NewBatch as boolean

change

       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
                newBatch =  True
      else
                   newBatch =  False
          End If    

 Then you can use this to prevent changing the value.

At the moment I do not see whee the relase Field is changed.

0
 
LVL 18

Expert Comment

by:marilyng
ID: 16654558
Hey,  that was my original post to not overwrite, so it's in the original post.  YOu made me change it to overwrite. and that's when sysExpert pitched in with the WRONG answer. :)

And, sysExpert, you've done it again, you jumped in with an incorrect solution.  Let's get some answers and clarification first before we start wildly posting (incorrect) code, OK?  

Ok- the rules
If a document exists in the view with the field "batch",
>>don't overwrite the document just update the fields in the document<< update what fields?
Also there will be a field in the document named "release" don't touch this at all.

So,

if the document exists and contains "Batch" update some unknown fields except "release"
if the document exists and doesn't contain "batch" then do what?
If the document doesn't exist, then do what?


Answer these questions please, and we'll provide a rewrite.  If there's any etiquette in the world, the other EE's will permit me to update my own code before horning in. :)
0
 

Author Comment

by:schmad01
ID: 16654843
Marilyn,

release is not in the column fields, it is just a hidden text field in the YieldSheet1 form with a default value of "No" therefore if the excel document has a matching batch number in the view, then update the column fields, and just leave release and any other non-column fields alone.

if the excel file has a new batch number with no match in the view, then add it to the view.

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 18

Accepted Solution

by:
marilyng earned 2000 total points
ID: 16654997
Ok, a touch confused, because the agent was already doing what you want, that is, it was updating and refreshing fields with old docs and creating new docs when batch wasn't found.   There is no yield in your columns, and no other statements that update other fields. But I added a flag for new and updated docs so you can do what you want.  The updated docs don't overwrite batch or form, and these are logged and counted now.

Sub Initialize
      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, updateCount As Long
      
      importCnt = 0
      skipcnt = 0
      updateCount = 0
      
      'Setting a boolean value to chk if I need to skip stuff
      Dim docExists As Boolean
      docExists = False
      
      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  
                  'Set docexists to false
                  docExists = False
               'There is NO match, create new document <<mgl changed.
                  Call importlog.LogAction ("Created new: " + Batch + " number not in database")              
                  skipcnt = skipcnt +1                  
               'Goto nextRow     <<See this was set to already overwrite the file
            Else
                  docExists = True
                  updateCount = updateCount +1
            End If    
            With importdoc                  
                  If Not docExists Then
                        'only set batch on new documents and you can also do or update other stuff here
                        .form = "YieldSheet1"
                        .batch = batch
                  End If
                  .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) + " total records")
      Call importlog.LogAction("Updated: " + Cstr(updateCount) + " records")
      Call importlog.LogAction("Added: " + Cstr(skipCnt) + " new 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    
End Sub
0
 
LVL 63

Expert Comment

by:SysExpert
ID: 16657390
And Again Marilyng, You have made the eact modifications I suggested.

0
 
LVL 18

Expert Comment

by:marilyng
ID: 16660247
Apologies.. but your's didn't complete the request, to exclude stuff - or even ask for clarification (since the original code had a skip provision).

I think what I was trying to say is that I would have liked the opportunity to correct my own code, before someone else starts messing with it.  

I think I try to extend that courtesy to all, and usually only add stuff after the original EE has had time to make the corrections, or if the EE  abandoned the question, didn't have a solution, etc.

But the rule is, first in with the correct answer should get the points.  Your answer is correct, albeit, incomplete.

0
 

Author Comment

by:schmad01
ID: 16660746
Ok, Marilyn, that does the trick as usual thanks alot!
0
 

Author Comment

by:schmad01
ID: 16661913
Gees, I have another scenario to add to this and then I swear that should be it as far as importing goes.  Still need help with the column multi-field split questiont though. Anyway new question coming.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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…
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?
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…
Suggested Courses

615 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