David Schmalzer
asked on
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.
You forgot to post your code !
ASKER
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\opla n\onetfold \report\to day\test\s chedwet1.x ls"
'get Excel Object
Print "Preparing to import..."
Set App = CreateObject("Excel.Applic ation")
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(WShe et.Cells(r ow, 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
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\opla
'get Excel Object
Print "Preparing to import..."
Set App = CreateObject("Excel.Applic
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
'-----> check if doc already exists
Dim dummyDoc As NotesDocument
Call view.Refresh
Set dummyDoc = view.GetDocumentByKey(Trim
If dummyDoc Is Nothing Then
'-----> check if doc already exists
Set doc = db.CreateDocument
doc.form = "YieldSheet1"
doc.batch = Trim(Cstr(WSheet.Cells(row
doc.fp = Trim(Cstr(WSheet.Cells(row
doc.s_a = Trim(Cstr(WSheet.Cells(row
doc.ipl_code = Trim(Cstr(WSheet.Cells(row
doc.dept = Trim(Cstr(WSheet.Cells(row
doc.descriptor = Trim(Cstr(WSheet.Cells(row
doc.qty = Trim(Cstr(WSheet.Cells(row
doc.g = Trim(Cstr(WSheet.Cells(row
doc.startdate = Trim(Cstr(WSheet.Cells(row
doc.due_date = Trim(Cstr(WSheet.Cells(row
doc.type = Trim(Cstr(WSheet.Cells(row
doc.save True, True
importCnt = importCnt + 1
Else
Print #fileNum, "Record already exists :" & Trim(Cstr(WSheet.Cells(row
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
I don't like coffee... :(
ASKER
Well, actually it was a Starbucks Grande White Chocolate Mocha. You should try it. It's awesome!.
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...
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 !
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
Else
doc.form = "YieldSheet1"
doc.batch = Trim(Cstr(WSheet.Cells(row
doc.fp = Trim(Cstr(WSheet.Cells(row
doc.s_a = Trim(Cstr(WSheet.Cells(row
doc.ipl_code = Trim(Cstr(WSheet.Cells(row
doc.dept = Trim(Cstr(WSheet.Cells(row
doc.descriptor = Trim(Cstr(WSheet.Cells(row
doc.qty = Trim(Cstr(WSheet.Cells(row
doc.g = Trim(Cstr(WSheet.Cells(row
doc.startdate = Trim(Cstr(WSheet.Cells(row
doc.due_date = Trim(Cstr(WSheet.Cells(row
doc.type = Trim(Cstr(WSheet.Cells(row
doc.save True, True
importCnt = importCnt + 1
End If
row = row + 1
Wend
I hope this helps !
ASKER
The first one should overwrite if the batch number exists. Import a new doc if it does not.
ASKER
Will the above still work for that scenario?
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
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
doc.fp = Trim(Cstr(WSheet.Cells(row
doc.s_a = Trim(Cstr(WSheet.Cells(row
doc.ipl_code = Trim(Cstr(WSheet.Cells(row
doc.dept = Trim(Cstr(WSheet.Cells(row
doc.descriptor = Trim(Cstr(WSheet.Cells(row
doc.qty = Trim(Cstr(WSheet.Cells(row
doc.g = Trim(Cstr(WSheet.Cells(row
doc.startdate = Trim(Cstr(WSheet.Cells(row
doc.due_date = Trim(Cstr(WSheet.Cells(row
doc.type = Trim(Cstr(WSheet.Cells(row
doc.save True, True
importCnt = importCnt + 1
Else
doc.form = "YieldSheet1"
doc.batch = Trim(Cstr(WSheet.Cells(row
doc.fp = Trim(Cstr(WSheet.Cells(row
doc.s_a = Trim(Cstr(WSheet.Cells(row
doc.ipl_code = Trim(Cstr(WSheet.Cells(row
doc.dept = Trim(Cstr(WSheet.Cells(row
doc.descriptor = Trim(Cstr(WSheet.Cells(row
doc.qty = Trim(Cstr(WSheet.Cells(row
doc.g = Trim(Cstr(WSheet.Cells(row
doc.startdate = Trim(Cstr(WSheet.Cells(row
doc.due_date = Trim(Cstr(WSheet.Cells(row
doc.type = Trim(Cstr(WSheet.Cells(row
doc.save True, True
importCnt = importCnt + 1
End If
row = row + 1
Wend
A slight correction.
In the Else clause
all the doc.
should be changed to
dummyDoc.
I hope this helps !
In the Else clause
all the doc.
should be changed to
dummyDoc.
I hope this helps !
ASKER
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\opla n\onetfold \report\to day\test\s chedwet1.x ls"
'get Excel Object
Print "Preparing to import..."
Set App = CreateObject("Excel.Applic ation")
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(WShe et.Cells(r ow, 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.
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\opla
'get Excel Object
Print "Preparing to import..."
Set App = CreateObject("Excel.Applic
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
'-----> check if doc already exists
Dim dummyDoc As NotesDocument
Call view.Refresh
Set dummyDoc = view.GetDocumentByKey(Trim
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
doc.fp = Trim(Cstr(WSheet.Cells(row
doc.s_a = Trim(Cstr(WSheet.Cells(row
doc.ipl_code = Trim(Cstr(WSheet.Cells(row
doc.dept = Trim(Cstr(WSheet.Cells(row
doc.descriptor = Trim(Cstr(WSheet.Cells(row
doc.qty = Trim(Cstr(WSheet.Cells(row
doc.g = Trim(Cstr(WSheet.Cells(row
doc.startdate = Trim(Cstr(WSheet.Cells(row
doc.due_date = Trim(Cstr(WSheet.Cells(row
doc.type = Trim(Cstr(WSheet.Cells(row
doc.save True, True
importCnt = importCnt + 1
Else
dummyDoc.form = "YieldSheet1"
dummyDoc.batch = Trim(Cstr(WSheet.Cells(row
dummyDoc.fp = Trim(Cstr(WSheet.Cells(row
dummyDoc.s_a = Trim(Cstr(WSheet.Cells(row
dummyDoc.ipl_code = Trim(Cstr(WSheet.Cells(row
dummyDoc.dept = Trim(Cstr(WSheet.Cells(row
dummyDoc.descriptor = Trim(Cstr(WSheet.Cells(row
dummyDoc.qty = Trim(Cstr(WSheet.Cells(row
dummyDoc.g = Trim(Cstr(WSheet.Cells(row
dummyDoc.startdate = Trim(Cstr(WSheet.Cells(row
dummyDoc.due_date = Trim(Cstr(WSheet.Cells(row
dummyDoc.type = Trim(Cstr(WSheet.Cells(row
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.
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("Start ed 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\opla n\onetfold \report\to day\test\s chedwet1.x ls"
'get Excel Object
Print "Preparing to import..."
Set App = CreateObject("Excel.Applic ation")
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(Batc h, 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("Finis hed the BATCH import at: " + Format(Now))
Call importlog.LogAction("Impor ted: " + Cstr(importCnt) + " records")
Call importlog.LogAction("Skipp ed: " + 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
-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("Start
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\opla
'get Excel Object
Print "Preparing to import..."
Set App = CreateObject("Excel.Applic
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
Batch = Trim(Cstr(WSheet.Cells(row
Print "Importing " + Cstr(row)
'-----> check if IMPORTdoc already exists
Set importdoc = view.GetDocumentByKey(Batc
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
.s_a = Trim(Cstr(WSheet.Cells(row
.ipl_code = Trim(Cstr(WSheet.Cells(row
.dept = Trim(Cstr(WSheet.Cells(row
.descriptor = Trim(Cstr(WSheet.Cells(row
.qty = Trim(Cstr(WSheet.Cells(row
.g = Trim(Cstr(WSheet.Cells(row
.startdate = Trim(Cstr(WSheet.Cells(row
c.due_date = Trim(Cstr(WSheet.Cells(row
.type = Trim(Cstr(WSheet.Cells(row
.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("Finis
Call importlog.LogAction("Impor
Call importlog.LogAction("Skipp
Call importlog.close
Exit Sub
Handle_Error:
On Error Goto 0
If Not importlog Is Nothing Then
Call importlog.LogAction("Error
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
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("Start ed 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\opla n\onetfold \report\to day\test\s chedwet1.x ls"
'get Excel Object
Print "Preparing to import..."
Set App = CreateObject("Excel.Applic ation")
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(Batc h, 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("Finis hed the BATCH import at: " + Format(Now))
Call importlog.LogAction("Impor ted: " + Cstr(importCnt) + " records")
Call importlog.LogAction("Skipp ed: " + 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
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("Start
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\opla
'get Excel Object
Print "Preparing to import..."
Set App = CreateObject("Excel.Applic
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
Batch = Trim(Cstr(WSheet.Cells(row
Print "Importing " + Cstr(row)
'-----> check if IMPORTdoc already exists
Set importdoc = view.GetDocumentByKey(Batc
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
.s_a = Trim(Cstr(WSheet.Cells(row
.ipl_code = Trim(Cstr(WSheet.Cells(row
.dept = Trim(Cstr(WSheet.Cells(row
.descriptor = Trim(Cstr(WSheet.Cells(row
.qty = Trim(Cstr(WSheet.Cells(row
.g = Trim(Cstr(WSheet.Cells(row
.startdate = Trim(Cstr(WSheet.Cells(row
.due_date = Trim(Cstr(WSheet.Cells(row
.type = Trim(Cstr(WSheet.Cells(row
.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("Finis
Call importlog.LogAction("Impor
Call importlog.LogAction("Skipp
Call importlog.close
Exit Sub
Handle_Error:
On Error Goto 0
If Not importlog Is Nothing Then
Call importlog.LogAction("Error
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
ASKER
Getting: Object variable not set.
Debugger stops on line: Call importlog.OpenMailLog ( SendTo, "Import from Excel" & " in " & db.Title )
Debugger stops on line: Call importlog.OpenMailLog ( SendTo, "Import from Excel" & " in " & db.Title )
db is not initialised.
Set db= ses.CurrentDatabase
Set db= ses.CurrentDatabase
ASKER
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).
ASKER
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.
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 !
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 !
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.
>>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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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!
marilyng , Based on your change, may correction would also have been fine.
It should not create any duplicates.
Just an FYI.
It should not create any duplicates.
Just an FYI.
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...
And I responded before I really read through the question add on...
ASKER
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.
ASKER
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.