asked on

Import from excel again

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

SysExpert

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.

marilyng

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

David Schmalzer

ASKER

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.

ASKER CERTIFIED SOLUTION

marilyng

membership

This solution is only available to members.

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

Start Free Trial

SysExpert

And Again Marilyng, You have made the eact modifications I suggested.

marilyng

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.

David Schmalzer

ASKER

Ok, Marilyn, that does the trick as usual thanks alot!

David Schmalzer

ASKER

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.