Excel Import Part 2

Ok, below is the code I am using to import an excel spreadsheet into a view.  What I need is 2 different versions of this code. If I need to submit even another question for this please let me know and I will do so.  The first version I need is for this to do an import and overwrite everything that matches the batch number in the import view.  The second version I need is this. When I click the button a prompt will ask me what batch number to import, I enter it, and it imports only the matching batch number. I know this is not that easy, I don't event know if it can be done. Thanks in advance.
schmad01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

schmad01Author Commented:
Let me re-phrase the first version. I may not have worded it correctly:

The first version I need is for this to do an import and overwrite everything that matches all the batch numbers between the spreadsheet and the import view. Anything that doesn't match is left alone.
0
SysExpertCommented:
You forgot to post your code !

0
schmad01Author Commented:
Ah yes. Coffee hasn't kicked in yet.  Here it is:


Sub Initialize
      Dim ws As New NotesUIWorkSpace
      Dim ses As New NotesSession
      Dim db  As NotesDatabase
      Dim view As NotesView
      Dim doc As NotesDocument
      
      Dim App As Variant, Wbook As Variant, WSheet As Variant
      
      Dim RetVal As Variant
      Dim XLfile As String
      Dim row As Long
      
      If Dir$(defDir,16) = "" Then
            Mkdir defDir
      End If
      
     '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
      importCnt = 0
      
      Print "Importing..."
      
      Dim fileNum As Integer
      Dim fileName As String
      fileNum = Freefile()
      fileName = defdir & "\Excel_Import_Log" & Format(Today,"ddmmyy") & ".txt"
      Open fileName For Output As filenum
      
      row = 2 '<--- skip the column header use 1 if your file has no column header
      While Trim(Cstr(WSheet.Cells(row, 1).Value)) <> ""      
            
'-----> check if doc already exists
            Dim dummyDoc As NotesDocument
            Call view.Refresh
            Set dummyDoc = view.GetDocumentByKey(Trim(Cstr(WSheet.Cells(row, 1).Value)))          
            If dummyDoc Is Nothing Then
'-----> check if doc already exists                        
                  Set doc = db.CreateDocument
                  doc.form = "YieldSheet1"
                  doc.batch = Trim(Cstr(WSheet.Cells(row, 1).Value))
                  doc.fp = Trim(Cstr(WSheet.Cells(row, 2).Value))
                  doc.s_a = Trim(Cstr(WSheet.Cells(row, 3).Value))
                  doc.ipl_code = Trim(Cstr(WSheet.Cells(row, 4).Value))
                  doc.dept = Trim(Cstr(WSheet.Cells(row, 5).Value))
                  doc.descriptor = Trim(Cstr(WSheet.Cells(row, 6).Value))
                  doc.qty = Trim(Cstr(WSheet.Cells(row, 7).Value))
                  doc.g = Trim(Cstr(WSheet.Cells(row, 8).Value))
                  doc.startdate = Trim(Cstr(WSheet.Cells(row, 9).Value))
                  doc.due_date = Trim(Cstr(WSheet.Cells(row, 10).Value))
                  doc.type = Trim(Cstr(WSheet.Cells(row, 11).Value))
                  
                  
                  doc.save True, True    
                  importCnt = importCnt + 1
            Else
                  Print #fileNum, "Record already exists :" & Trim(Cstr(WSheet.Cells(row, 1).Value))
            End If
            row = row + 1
      Wend
      Close fileNum
      App.Application.Quit
      Set App = Nothing
      Set Wbook = Nothing
      Set Wsheet = Nothing
      Msgbox "Successfully imported " & Cstr(importCnt) & " record(s).", 0 + 64, "Import from excel"
End Sub
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Sjef BosmanGroupware ConsultantCommented:
I don't like coffee... :(
0
schmad01Author Commented:
Well, actually it was a Starbucks Grande White Chocolate Mocha. You should try it. It's awesome!.
0
Sjef BosmanGroupware ConsultantCommented:
Hmm, sounds good, never heard of it this side of the pond... Still, I need an "eye-opener", I can't understand a single line of code today. Stack problems, registers dropping their content, memory failures, etc. I think I'd better reboot my head...
0
SysExpertCommented:
SO the first one ony does an update if the batch number exists.
The second one will only import  a specific batch number.

for #1

chnage
          If dummyDoc Is Nothing Then
'-----> check if doc already exists                        
                Print #fileNum, "Record DOes not exist :" & Trim(Cstr(WSheet.Cells(row, 1).Value))
         
          Else
             
     doc.form = "YieldSheet1"
               doc.batch = Trim(Cstr(WSheet.Cells(row, 1).Value))
               doc.fp = Trim(Cstr(WSheet.Cells(row, 2).Value))
               doc.s_a = Trim(Cstr(WSheet.Cells(row, 3).Value))
               doc.ipl_code = Trim(Cstr(WSheet.Cells(row, 4).Value))
               doc.dept = Trim(Cstr(WSheet.Cells(row, 5).Value))
               doc.descriptor = Trim(Cstr(WSheet.Cells(row, 6).Value))
               doc.qty = Trim(Cstr(WSheet.Cells(row, 7).Value))
               doc.g = Trim(Cstr(WSheet.Cells(row, 8).Value))
               doc.startdate = Trim(Cstr(WSheet.Cells(row, 9).Value))
               doc.due_date = Trim(Cstr(WSheet.Cells(row, 10).Value))
               doc.type = Trim(Cstr(WSheet.Cells(row, 11).Value))
               
               
               doc.save True, True    
               importCnt = importCnt + 1
          End If
          row = row + 1
     Wend

I hope this helps !
0
schmad01Author Commented:
The first one should overwrite if the batch number exists. Import a new doc if it does not.
0
schmad01Author Commented:
Will the above still work for that scenario?
0
SysExpertCommented:
Not quite, it will ONLY import existing batch numbers.

a quick change should fix this.
    If dummyDoc Is Nothing Then
'-----> check if doc already exists                        
                Set doc = db.CreateDocument
               doc.form = "YieldSheet1"
               doc.batch = Trim(Cstr(WSheet.Cells(row, 1).Value))
               doc.fp = Trim(Cstr(WSheet.Cells(row, 2).Value))
               doc.s_a = Trim(Cstr(WSheet.Cells(row, 3).Value))
               doc.ipl_code = Trim(Cstr(WSheet.Cells(row, 4).Value))
               doc.dept = Trim(Cstr(WSheet.Cells(row, 5).Value))
               doc.descriptor = Trim(Cstr(WSheet.Cells(row, 6).Value))
               doc.qty = Trim(Cstr(WSheet.Cells(row, 7).Value))
               doc.g = Trim(Cstr(WSheet.Cells(row, 8).Value))
               doc.startdate = Trim(Cstr(WSheet.Cells(row, 9).Value))
               doc.due_date = Trim(Cstr(WSheet.Cells(row, 10).Value))
               doc.type = Trim(Cstr(WSheet.Cells(row, 11).Value))
               
               
               doc.save True, True    
               importCnt = importCnt + 1
         
          Else
             
               doc.form = "YieldSheet1"
               doc.batch = Trim(Cstr(WSheet.Cells(row, 1).Value))
               doc.fp = Trim(Cstr(WSheet.Cells(row, 2).Value))
               doc.s_a = Trim(Cstr(WSheet.Cells(row, 3).Value))
               doc.ipl_code = Trim(Cstr(WSheet.Cells(row, 4).Value))
               doc.dept = Trim(Cstr(WSheet.Cells(row, 5).Value))
               doc.descriptor = Trim(Cstr(WSheet.Cells(row, 6).Value))
               doc.qty = Trim(Cstr(WSheet.Cells(row, 7).Value))
               doc.g = Trim(Cstr(WSheet.Cells(row, 8).Value))
               doc.startdate = Trim(Cstr(WSheet.Cells(row, 9).Value))
               doc.due_date = Trim(Cstr(WSheet.Cells(row, 10).Value))
               doc.type = Trim(Cstr(WSheet.Cells(row, 11).Value))
               
               
               doc.save True, True    
               importCnt = importCnt + 1
          End If
          row = row + 1
     Wend
0
SysExpertCommented:
A slight correction.
In the Else clause
all the doc.

should be changed to
dummyDoc.

I hope this helps !
0
schmad01Author Commented:
Well, there were no errors. I tried it and it seemed to just keep importing. I let it go for about a minute then stopped. Nothing was imported, so no harm done. Not sure what is happening or how long it should take.  Here is the code as I have it now:

Dim ws As New NotesUIWorkSpace
      Dim ses As New NotesSession
      Dim db  As NotesDatabase
      Dim view As NotesView
      Dim doc As NotesDocument
      
      Dim App As Variant, Wbook As Variant, WSheet As Variant
      
      Dim RetVal As Variant
      Dim XLfile As String
      Dim row As Long
      
      If Dir$(defDir,16) = "" Then
            Mkdir defDir
      End If
      
     '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
      importCnt = 0
      
      Print "Importing..."
      
      Dim fileNum As Integer
      Dim fileName As String
      fileNum = Freefile()
      fileName = defdir & "\Excel_Import_Log" & Format(Today,"ddmmyy") & ".txt"
      Open fileName For Output As filenum
      
      row = 2 '<--- skip the column header use 1 if your file has no column header
      While Trim(Cstr(WSheet.Cells(row, 1).Value)) <> ""      
            
'-----> check if doc already exists
            Dim dummyDoc As NotesDocument
            Call view.Refresh
            Set dummyDoc = view.GetDocumentByKey(Trim(Cstr(WSheet.Cells(row, 1).Value)))          
            If dummyDoc Is Nothing Then
                  If dummyDoc Is Nothing Then
'-----> check if doc already exists                        
                        Set doc = db.CreateDocument
                        doc.form = "YieldSheet1"
                        doc.batch = Trim(Cstr(WSheet.Cells(row, 1).Value))
                        doc.fp = Trim(Cstr(WSheet.Cells(row, 2).Value))
                        doc.s_a = Trim(Cstr(WSheet.Cells(row, 3).Value))
                        doc.ipl_code = Trim(Cstr(WSheet.Cells(row, 4).Value))
                        doc.dept = Trim(Cstr(WSheet.Cells(row, 5).Value))
                        doc.descriptor = Trim(Cstr(WSheet.Cells(row, 6).Value))
                        doc.qty = Trim(Cstr(WSheet.Cells(row, 7).Value))
                        doc.g = Trim(Cstr(WSheet.Cells(row, 8).Value))
                        doc.startdate = Trim(Cstr(WSheet.Cells(row, 9).Value))
                        doc.due_date = Trim(Cstr(WSheet.Cells(row, 10).Value))
                        doc.type = Trim(Cstr(WSheet.Cells(row, 11).Value))
                        
                        
                        doc.save True, True    
                        importCnt = importCnt + 1
                        
                  Else
                        
                        dummyDoc.form = "YieldSheet1"
                        dummyDoc.batch = Trim(Cstr(WSheet.Cells(row, 1).Value))
                        dummyDoc.fp = Trim(Cstr(WSheet.Cells(row, 2).Value))
                        dummyDoc.s_a = Trim(Cstr(WSheet.Cells(row, 3).Value))
                        dummyDoc.ipl_code = Trim(Cstr(WSheet.Cells(row, 4).Value))
                        dummyDoc.dept = Trim(Cstr(WSheet.Cells(row, 5).Value))
                        dummyDoc.descriptor = Trim(Cstr(WSheet.Cells(row, 6).Value))
                        dummyDoc.qty = Trim(Cstr(WSheet.Cells(row, 7).Value))
                        dummyDoc.g = Trim(Cstr(WSheet.Cells(row, 8).Value))
                        dummyDoc.startdate = Trim(Cstr(WSheet.Cells(row, 9).Value))
                        dummyDoc.due_date = Trim(Cstr(WSheet.Cells(row, 10).Value))
                        dummyDoc.type = Trim(Cstr(WSheet.Cells(row, 11).Value))
                        
                        
                        doc.save True, True    
                        importCnt = importCnt + 1
                  End If
                  row = row + 1
            End If
      Wend



----------------------------------------------------------------------

Is that correct?
Also, I know this will throw a wrench into the whole thing, but I hope not.  I was just told by the project manager that they want it so that if anything is updated in any cell in any particular row in the spreadsheet, then that corresponding document should be either updated or overwritten in the view during import (the batch number is still the key).  The import agent will be scheduled to run a few times throughout the day. Right now I am running the agent manually for our testing purposes.
0
marilyngCommented:
No it is not correct.  You've got incomplete code everywhere, and incomplete references.   The MkDir isn't making any directory, so I'm surprised the agent doesn't abort at that point.  

-Doc and dummydoc..? you didn't change them all so nothing saves
-The text file log.. well, you don't instantiate a directory, so even if you tried to save to a file, it wouldn't save, but you're not writing to it.  Instead, why not create an email log and mail it, this way you don't have to go and find a log on the server.  If you need a record, then create a separate log document for each import.

At any rate, this should work, but I didn't test it because I don't have your environment set up.  I am not comfortable with the excel calls, as they are incomplete and don't usually work on newer versions of excel. (I do wish who ever is circulating this bit of obsolete code would stop or at least update it)  Also, if you intend to run on a server, YOU MUST ERROR TRAP AND YOU MUST CLOSE THE EXCEL OBJECT.

This is the first on that overwrites the BATCH when found, and skips the ones not found.  Writes to an email and will send you the log if you change this line to your notes name: SendToName="ENTER YOUR NOTES EMAIL NAME"


Sub Initialize
      Dim ws As New NotesUIWorkSpace
      Dim ses As New NotesSession
      Dim db  As NotesDatabase
      Dim view As NotesView
      Dim importdoc As NotesDocument
      
      '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
      SendToName="ENTER YOUR NOTES EMAIL NAME"   '<<<<CHANGE THIS TO YOUR EMAIL ADDRESS>>>>>
      Set importlog = New NotesLog( "Import from Excel" )
      Call importlog.OpenMailLog ( SendToName, "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
      Dim importdoc As NotesDocument       
      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       
                  'There is NO match, so DON't overwrite, but add to log
                  Call importlog.LogAction ("Skipped: " + 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))
                  c.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
      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      
      End If
      
End Sub

0
marilyngCommented:
Never fails, after you rant for awhile, you discover typo's in your posted code...

Sub Initialize
      Dim ws As New NotesUIWorkSpace
      Dim ses As New NotesSession
      Dim db  As NotesDatabase
      Dim view As NotesView
      Dim importdoc As NotesDocument
      
      '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
      SendTo="ENTER YOUR NOTES EMAIL NAME"
      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       
                  'There is NO match, so DON't overwrite, but add to log
                  Call importlog.LogAction ("Skipped: " + 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
      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      
      
End Sub
0
schmad01Author Commented:
Getting: Object variable not set.

Debugger stops on line: Call importlog.OpenMailLog ( SendTo, "Import from Excel" & " in " & db.Title )
0
Sjef BosmanGroupware ConsultantCommented:
db is not initialised.

Set db= ses.CurrentDatabase
0
schmad01Author Commented:
Ok, sjef, that got rid of the error, however, there is a problem, if it doesn't find the batch number in the database, it should import, however it does not.  If it does find the batch number, it should overwrite it (will check this when the first problem is solved).
0
schmad01Author Commented:
I just tested the overwrite if the batch number exists, and that works ok. It just doesn't import if the batch number doesn't exist. If we can get that right, we'll be done. I tried figuring out what is wrong myself, but I just can't seem to grasp all of the script.
0
SysExpertCommented:
missing

Set importdoc = db.CreateDocument instead of the

        If importdoc Is Nothing Then      
               'There is NO match, so DON't overwrite, but add to log
               Call importlog.LogAction ("Skipped: " + Batch + " number not in database")              
               skipcnt = skipcnt +1
               Goto nextRow
          End If    
use
 If importdoc Is Nothing Then      
           Set importdoc = db.CreateDocument
     End If    

I hope this helps !
0
marilyngCommented:
No not missing,

>>The first version I need is for this to do an import and overwrite everything that matches the batch number in the import view.<<  Make up your mind.  

So, no if there is not already an imported document  that matches the batch number, this one doesn't create it.  question solved.  The other is a second question, I believe.

And  sysExpert's note will just render the agent useless, btw, and create duplicates everywhere.
0
marilyngCommented:
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="ENTER YOUR NOTES EMAIL NAME"

     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
     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    
     
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marilyngCommented:
If you don't enter your email address, then you will get an error: Debugger stops on line: Call importlog.OpenMailLog ( SendTo, "Import from Excel" & " in " & db.Title ), because sendto is nothing.
0
schmad01Author Commented:
That was it! Thank you so much. I sent you an email this morning, I hope you got it.  Thanks again! ! ! One day I will grasp lotusscript, I hope!
0
SysExpertCommented:
marilyng ,  Based on your change, may correction would also have been fine.

It should not create any duplicates.
Just an FYI.
0
marilyngCommented:
Didn't mean to be short... it's just that the tiny snippet suggestions weren't working and were being translated oddly - we had doc, then dummydoc with doc, an uninstantiated directory.. it looked and acted like a patch job.

And I responded before I really read through the question add on...
0
schmad01Author Commented:
Unfortunately a little caveat came up with this. Although everything works fine with the import, there is a dblookup formula in a computed field, and although it does the lookup and finds the data, and it is in the form, it isn't showing up in the view.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.