Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel Import Part 2

Posted on 2006-04-26
26
Medium Priority
?
310 Views
Last Modified: 2013-12-18
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.
0
Comment
Question by:schmad01
  • 11
  • 6
  • 6
  • +1
26 Comments
 

Author Comment

by:schmad01
ID: 16543719
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
 
LVL 63

Expert Comment

by:SysExpert
ID: 16543779
You forgot to post your code !

0
 

Author Comment

by:schmad01
ID: 16543824
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
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.

 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16544407
I don't like coffee... :(
0
 

Author Comment

by:schmad01
ID: 16544456
Well, actually it was a Starbucks Grande White Chocolate Mocha. You should try it. It's awesome!.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16544816
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
 
LVL 63

Expert Comment

by:SysExpert
ID: 16546347
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
 

Author Comment

by:schmad01
ID: 16546775
The first one should overwrite if the batch number exists. Import a new doc if it does not.
0
 

Author Comment

by:schmad01
ID: 16546787
Will the above still work for that scenario?
0
 
LVL 63

Expert Comment

by:SysExpert
ID: 16547108
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
 
LVL 63

Expert Comment

by:SysExpert
ID: 16547128
A slight correction.
In the Else clause
all the doc.

should be changed to
dummyDoc.

I hope this helps !
0
 

Author Comment

by:schmad01
ID: 16547276
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
 
LVL 18

Expert Comment

by:marilyng
ID: 16549815
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
 
LVL 18

Expert Comment

by:marilyng
ID: 16549921
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
 

Author Comment

by:schmad01
ID: 16552289
Getting: Object variable not set.

Debugger stops on line: Call importlog.OpenMailLog ( SendTo, "Import from Excel" & " in " & db.Title )
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16552374
db is not initialised.

Set db= ses.CurrentDatabase
0
 

Author Comment

by:schmad01
ID: 16552520
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
 

Author Comment

by:schmad01
ID: 16552975
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
 
LVL 63

Expert Comment

by:SysExpert
ID: 16553247
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
 
LVL 18

Expert Comment

by:marilyng
ID: 16554760
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
 
LVL 18

Accepted Solution

by:
marilyng earned 2000 total points
ID: 16554807
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
 
LVL 18

Expert Comment

by:marilyng
ID: 16554814
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
 

Author Comment

by:schmad01
ID: 16555005
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
 
LVL 63

Expert Comment

by:SysExpert
ID: 16556074
marilyng ,  Based on your change, may correction would also have been fine.

It should not create any duplicates.
Just an FYI.
0
 
LVL 18

Expert Comment

by:marilyng
ID: 16558409
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
 

Author Comment

by:schmad01
ID: 16564545
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
Screencast - Getting to Know the Pipeline
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month14 days, 17 hours left to enroll

577 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