Solved

# Import from excel again

Posted on 2006-05-10
Medium Priority
269 Views
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

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 Question by:schmad01 • 3 • 3 • 2 8 Comments LVL 63 Expert Comment 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 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 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 LVL 18 Accepted Solution 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

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

0

LVL 18

Expert Comment

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

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

Author Comment

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

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
Course of the Month8 days, 4 hours left to enroll