Jaziar
asked on
Phase 2 of the import
OK now I have the ability to import values from a excel spread sheet in to a form. Here is the next issue I need to solve.
When the import runs it creates a document for every row in the spreadsheet. This is fine. When the second import comes at end of month I need it to edit the documents with the new data and not create all new documents. Here is the way I am thinking - there are two fields in the form ( a lot more this just a example)
Jan_Plan
Jan_OL
The first import puts a value from the spreadsheet in Jan_Plan, then the second and others imports only edit the Jan_OL field.
Jan_Plan is a target field and Jan_OL is actual. Please tell me if I need to make this more clear. I will try to sum it up.
Import 1
All fields are populated from a spreadsheet and a bunch of documents are created. The fields pertaining to months are populated in the Month_Plan fields.
Import 2
All the documents that were created is now edited and no new documents are created. It only creates documents on the first import. The values are now assigned to Month_OL. That way I can have a 3rd field calculating the delta between Month_Plan and Month_OL
Here is the first import
Sub Click(Source As Button)
On Error Goto barry
Dim file As Variant
Dim ws As New NotesUIWorkspace
Dim xlFilename As String
Dim session As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim colValues(1 To 30) As Variant
Dim intCount As Integer
Dim intRow As Integer
Dim Excel As Variant
Dim xlWorkbook As Variant
Dim xlSheet As Variant
Dim blankLine As Variant
Set db = session.CurrentDatabase
file = ws.OpenFileDialog(False, "File List", "", "c:")
If Isempty(file) Then
Exit Sub
End If
' Connect to Excel and open the file. Then start pulling over the records.
xlFilename = Cstr(File(0)) '// This is the name of the Excel file that will be imported
Set Excel = CreateObject("Excel.Applic ation")
Excel.Visible = False '// Don't display the Excel window
Excel.Workbooks.Open xlFilename '// Open the Excel file
Set xlWorkbook = Excel.ActiveWorkbook
Set xlSheet = xlWorkbook.ActiveSheet
' Get the data from the first row in Excel
blankLine = True
intRow = 1 ' Start with 2 instead of 1 if your Excel sheet has a header row
For intCount = 1 To 30
colValues(intCount) = xlSheet.Cells(intRow, intCount).Value
If colValues(intCount) >< "" Then blankLine = False
Next
' While we don't have a blank line, create a document out of what we do have.
Do While Not blankLine
Set doc = New NotesDocument(db)
doc.Form = "Jat2"
doc.Group = colValues(1)
doc.Manager4th = colValues(2)
doc.Area = colValues(3)
doc.Category = colValues(4)
doc.Jan = colValues(4)
doc.Feb = colValues(5)
doc.Mar = colValues(6)
doc.Q1_Total = colValues(7)
Call doc.Save(True, False)
' Get the data from the next line.
intRow = intRow + 1
blankLine = True
For intCount = 1 To 30
colValues(intCount) = xlSheet.Cells(intRow, intCount).Value
If colValues(intCount) >< "" Then blankLine = False
Next
Loop
xlWorkbook.Close False '// Close the Excel file without saving (we made no changes)
Excel.Quit '// Close Excel
Set Excel = Nothing '// Free the memory that we'd used
Exit Sub
barry:
Msgbox "error occured on line number" & Erl & "error is " & Error
Exit Sub
End Sub
When the import runs it creates a document for every row in the spreadsheet. This is fine. When the second import comes at end of month I need it to edit the documents with the new data and not create all new documents. Here is the way I am thinking - there are two fields in the form ( a lot more this just a example)
Jan_Plan
Jan_OL
The first import puts a value from the spreadsheet in Jan_Plan, then the second and others imports only edit the Jan_OL field.
Jan_Plan is a target field and Jan_OL is actual. Please tell me if I need to make this more clear. I will try to sum it up.
Import 1
All fields are populated from a spreadsheet and a bunch of documents are created. The fields pertaining to months are populated in the Month_Plan fields.
Import 2
All the documents that were created is now edited and no new documents are created. It only creates documents on the first import. The values are now assigned to Month_OL. That way I can have a 3rd field calculating the delta between Month_Plan and Month_OL
Here is the first import
Sub Click(Source As Button)
On Error Goto barry
Dim file As Variant
Dim ws As New NotesUIWorkspace
Dim xlFilename As String
Dim session As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim colValues(1 To 30) As Variant
Dim intCount As Integer
Dim intRow As Integer
Dim Excel As Variant
Dim xlWorkbook As Variant
Dim xlSheet As Variant
Dim blankLine As Variant
Set db = session.CurrentDatabase
file = ws.OpenFileDialog(False, "File List", "", "c:")
If Isempty(file) Then
Exit Sub
End If
' Connect to Excel and open the file. Then start pulling over the records.
xlFilename = Cstr(File(0)) '// This is the name of the Excel file that will be imported
Set Excel = CreateObject("Excel.Applic
Excel.Visible = False '// Don't display the Excel window
Excel.Workbooks.Open xlFilename '// Open the Excel file
Set xlWorkbook = Excel.ActiveWorkbook
Set xlSheet = xlWorkbook.ActiveSheet
' Get the data from the first row in Excel
blankLine = True
intRow = 1 ' Start with 2 instead of 1 if your Excel sheet has a header row
For intCount = 1 To 30
colValues(intCount) = xlSheet.Cells(intRow, intCount).Value
If colValues(intCount) >< "" Then blankLine = False
Next
' While we don't have a blank line, create a document out of what we do have.
Do While Not blankLine
Set doc = New NotesDocument(db)
doc.Form = "Jat2"
doc.Group = colValues(1)
doc.Manager4th = colValues(2)
doc.Area = colValues(3)
doc.Category = colValues(4)
doc.Jan = colValues(4)
doc.Feb = colValues(5)
doc.Mar = colValues(6)
doc.Q1_Total = colValues(7)
Call doc.Save(True, False)
' Get the data from the next line.
intRow = intRow + 1
blankLine = True
For intCount = 1 To 30
colValues(intCount) = xlSheet.Cells(intRow, intCount).Value
If colValues(intCount) >< "" Then blankLine = False
Next
Loop
xlWorkbook.Close False '// Close the Excel file without saving (we made no changes)
Excel.Quit '// Close Excel
Set Excel = Nothing '// Free the memory that we'd used
Exit Sub
barry:
Msgbox "error occured on line number" & Erl & "error is " & Error
Exit Sub
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Consider for a moment that if two lines in the spreadsheet have the same information in them, why do you need them both? Clearly there must be something that distinguishes the two lines, that makes them both necessary.
Apparently this database is to keep track of some objects that are in some group, category, and area, under some manager. If there are two identical such objects out there, how do you tell them apart in the spreadsheet? How do you know which one is which when it comes time for someone to update the spreadsheet? **THAT** is your key value (or at least needs to be some part of your key value).
The key fields don't have to be the first columns. You can have them as columns 1, 17, 23 and 27-30 if that's what fits your situation.
Does this clear it up any?
-- b.r.t.
Apparently this database is to keep track of some objects that are in some group, category, and area, under some manager. If there are two identical such objects out there, how do you tell them apart in the spreadsheet? How do you know which one is which when it comes time for someone to update the spreadsheet? **THAT** is your key value (or at least needs to be some part of your key value).
The key fields don't have to be the first columns. You can have them as columns 1, 17, 23 and 27-30 if that's what fits your situation.
Does this clear it up any?
-- b.r.t.
ASKER
I have the first 4 being unique now. So I am in the process of applying
ASKER
Barry the first import has some issues in the code
Do While Not blankLine
Set doc = GetDoc(db, colValues) <= Illegal parenthesized referenced: GETDOC
If (doc Is Nothing) Then
Set doc = MakeNewDoc(db, colValues)<= Illegal parenthesized referenced: MakeNewDoc
End
Call AdjustDoc(doc, colValues) <= Not a sub or function name : ADJUSTDOC
Do While Not blankLine
Set doc = GetDoc(db, colValues) <= Illegal parenthesized referenced: GETDOC
If (doc Is Nothing) Then
Set doc = MakeNewDoc(db, colValues)<= Illegal parenthesized referenced: MakeNewDoc
End
Call AdjustDoc(doc, colValues) <= Not a sub or function name : ADJUSTDOC
Jaziar --
When you're looking at the code for the button click, the code selector pane (not its official name) on the left should have the following options in it:
(Options)
(Declarations)
Click
Objectexecute
Initialize
Terminate
GetDoc
MakeNewDoc
AdjustDoc
The main part of your code would probably be in your Click event.
If you don't have those bottom three options there, you didn't paste in (or somehow it lost) those three subroutines.
Select the first of the functions I included above, from where it begins "Function GetDoc(db As NotesDatabase, colValues() As Variant) As NotesDocument" to the first place it says "End Function", copy that, and go back to the Notes developer. Go to the end of the End Sub line for the Click event. Press Enter to go to the next line. Paste that code in.
That should create a GetDoc function with its own entry in the code listing. Repeat that process for the MakeNewDoc function and the AdjustDoc function.
These functions and this subroutine would be callable from any of the events in this button (e.g., the Initialize event or the Terminate event) if you wanted. Or, they can call each other. The "scope" of these routines is the life of this button-click event.
Does this help?
-- b.r.t.
When you're looking at the code for the button click, the code selector pane (not its official name) on the left should have the following options in it:
(Options)
(Declarations)
Click
Objectexecute
Initialize
Terminate
GetDoc
MakeNewDoc
AdjustDoc
The main part of your code would probably be in your Click event.
If you don't have those bottom three options there, you didn't paste in (or somehow it lost) those three subroutines.
Select the first of the functions I included above, from where it begins "Function GetDoc(db As NotesDatabase, colValues() As Variant) As NotesDocument" to the first place it says "End Function", copy that, and go back to the Notes developer. Go to the end of the End Sub line for the Click event. Press Enter to go to the next line. Paste that code in.
That should create a GetDoc function with its own entry in the code listing. Repeat that process for the MakeNewDoc function and the AdjustDoc function.
These functions and this subroutine would be callable from any of the events in this button (e.g., the Initialize event or the Terminate event) if you wanted. Or, they can call each other. The "scope" of these routines is the life of this button-click event.
Does this help?
-- b.r.t.
ASKER
You are correct I dod not paste the subs in first
ASKER
am I putting the code in the (jat2 docs)? being hidden it will be hard to use the upload button
ASKER
Barry could this code in GetDoc be causing a type mismatch
Set item = New NotesItem(doc, "Temp" & Trim$(Str$(intCount)), colValues(intCount))
Set item = New NotesItem(doc, "Temp" & Trim$(Str$(intCount)), colValues(intCount))
Jaziar --
(Jat2 docs) is a behind-the-scenes view used to lookup existing documents in the GetDoc function. You put the code for this where you put the code from yesterday, replacing that code.
I don't see why it would, off the top of my head. The parameters are:
doc as a NotesDocument
"Temp" & Trim$(Str$(intCount)) as a string
colValues(intCount) as a variant, as the value to go into the new item.
I take it you've run this in debug mode and that's where you're getting the problem?
-- b.r.t.
(Jat2 docs) is a behind-the-scenes view used to lookup existing documents in the GetDoc function. You put the code for this where you put the code from yesterday, replacing that code.
I don't see why it would, off the top of my head. The parameters are:
doc as a NotesDocument
"Temp" & Trim$(Str$(intCount)) as a string
colValues(intCount) as a variant, as the value to go into the new item.
I take it you've run this in debug mode and that's where you're getting the problem?
-- b.r.t.
ASKER
OK here is the results - I fixed the mismatch problem.
I run the first upload
I have 132 documents created - using jat2 form which looks like this
[group] [4thmanager] [area] [catergory]
[jan_plan] [jan_ol] [jan_delta]
[jan_act] [jan_delta]
each month is like this. The delta's are calculated. After the first upload
jan_plan = 10 and jan_ol = 10 which is what I want. I put this code in
Function MakeNewDoc(db As NotesDatabase, colValues() As Variant) As NotesDocument
Dim doc As NotesDocument
Set doc = New NotesDocument(db)
doc.Form = "Jat2"
doc.Group = colValues(1)
doc.Manager4th = colValues(2)
doc.Area = colValues(3)
doc.Catergory = colValues(4)
doc.Jan_Plan = colValues(5)
doc.Feb_Plan = colValues(6)
doc.Mar_Plan = colValues(7)
doc.Q1_Total_Plan = colValues(8)
Set MakeNewDoc = doc
End Function
and
Sub AdjustDoc(doc As NotesDocument, colValues() As Variant)
' This function adds all the current values, whether the doc is new or existing.
' Coded this way to give one place to maintain code
doc.Jan_OL = colValues(5)
doc.Feb_OL = colValues(6)
doc.Mar_OL = colValues(7)
doc.Q1_Total_OL = colValues(8)
Call doc.Save(True, False)
End Sub
So first pass is correct.
Then I go to the spreadsheet and change a value of Jan (colValues(5)) from 10 to 12 - save
go to database and hit the upload button again - does it thing and finishes. I now have 180 documents. It creates documents for all the months again - so my view looks like this
something
manager
area
jan_plan = 10 jan_ol = 10
jan_plan = 12 jan_ol = 12
I need for the view to look like this
something
manager
area
jan_plan = 10 jan_ol = 12
After the first import jan_plan will never change only OLs. plus it was creating new documents - I was hoping just to replace the OL values in the existing documents.
I run the first upload
I have 132 documents created - using jat2 form which looks like this
[group] [4thmanager] [area] [catergory]
[jan_plan] [jan_ol] [jan_delta]
[jan_act] [jan_delta]
each month is like this. The delta's are calculated. After the first upload
jan_plan = 10 and jan_ol = 10 which is what I want. I put this code in
Function MakeNewDoc(db As NotesDatabase, colValues() As Variant) As NotesDocument
Dim doc As NotesDocument
Set doc = New NotesDocument(db)
doc.Form = "Jat2"
doc.Group = colValues(1)
doc.Manager4th = colValues(2)
doc.Area = colValues(3)
doc.Catergory = colValues(4)
doc.Jan_Plan = colValues(5)
doc.Feb_Plan = colValues(6)
doc.Mar_Plan = colValues(7)
doc.Q1_Total_Plan = colValues(8)
Set MakeNewDoc = doc
End Function
and
Sub AdjustDoc(doc As NotesDocument, colValues() As Variant)
' This function adds all the current values, whether the doc is new or existing.
' Coded this way to give one place to maintain code
doc.Jan_OL = colValues(5)
doc.Feb_OL = colValues(6)
doc.Mar_OL = colValues(7)
doc.Q1_Total_OL = colValues(8)
Call doc.Save(True, False)
End Sub
So first pass is correct.
Then I go to the spreadsheet and change a value of Jan (colValues(5)) from 10 to 12 - save
go to database and hit the upload button again - does it thing and finishes. I now have 180 documents. It creates documents for all the months again - so my view looks like this
something
manager
area
jan_plan = 10 jan_ol = 10
jan_plan = 12 jan_ol = 12
I need for the view to look like this
something
manager
area
jan_plan = 10 jan_ol = 12
After the first import jan_plan will never change only OLs. plus it was creating new documents - I was hoping just to replace the OL values in the existing documents.
I don't know if this is it for sure, Jaziar, but in your MakeNewDoc function you are spelling "Category" as "Catergory". If the Category field is part of the "key" that ensures you have a unique document, that could be part of the problem.
The crux of this problem is that the GetDoc function isn't finding the existing document when it checks.
Take a look at the (Jat2 Docs) view in the developer. Refresh the view in the developer to show you what documents are appearing in it. You should be seeing something like:
ColorSocksBobCategory2
MonoSmithJohnCategory1
etc.
The one column that exists in that view is just these four key fields smashed together, and it's critical that the column be sorted.
Try putting this line in the GetDoc function just before the end:
if doc is nothing then print "No Doc. Key = " & strKey
This will print some information on the status line at the bottom of the screen that will be helpful in debugging. For every time it doesn't find an existing document, it will print a message with the key it was using to try to find the document.
Report back with an example of what that's giving you, please.
-- b.r.t.
The crux of this problem is that the GetDoc function isn't finding the existing document when it checks.
Take a look at the (Jat2 Docs) view in the developer. Refresh the view in the developer to show you what documents are appearing in it. You should be seeing something like:
ColorSocksBobCategory2
MonoSmithJohnCategory1
etc.
The one column that exists in that view is just these four key fields smashed together, and it's critical that the column be sorted.
Try putting this line in the GetDoc function just before the end:
if doc is nothing then print "No Doc. Key = " & strKey
This will print some information on the status line at the bottom of the screen that will be helpful in debugging. For every time it doesn't find an existing document, it will print a message with the key it was using to try to find the document.
Report back with an example of what that's giving you, please.
-- b.r.t.
ASKER
I got it working - I had a key field messed up. This is great!!!!!! Thank you very much. I will close this tomorrow after I run some more test.
OK, Jaziar. Let me know if I can be any more help.
-- b.r.t.
-- b.r.t.
ASKER
Trust me I have two more coming tomorrow!
1. Is there logic in your subs to check the current date depending on the date assign the column value to either
Jan_OL or Jan_Act - what this does it the following
Jan_Plan = never changes after first input
Jan_OL = Is the current value before and while in the current month
Jan_Act = the actual number after the number has pasted
2 Is it possible to write a agent that looks at the excel file and sees if it have been saved on the current day and if it has then run the import. Actually we could just set it to run every 6 hours or something.
This would keep the user from having to input in to notes everything they make a change - the system would do it for them.
THose two questions are coming. This database is very important me and I thank you for your help thus far.
1. Is there logic in your subs to check the current date depending on the date assign the column value to either
Jan_OL or Jan_Act - what this does it the following
Jan_Plan = never changes after first input
Jan_OL = Is the current value before and while in the current month
Jan_Act = the actual number after the number has pasted
2 Is it possible to write a agent that looks at the excel file and sees if it have been saved on the current day and if it has then run the import. Actually we could just set it to run every 6 hours or something.
This would keep the user from having to input in to notes everything they make a change - the system would do it for them.
THose two questions are coming. This database is very important me and I thank you for your help thus far.
Jaziar --
The short answers are:
1) No. There is no logic there now, but there certainly could be. I would need better requirements before I could start planning an approach, though.
2) Yes it is possible. You could either set it up so when it's run manually it checks to see if it should even bother, or have it run on schedule and import only after there's been a change to the Excel file. You would use the FileDateTime function for that one. (You can learn some about it in the help files, or by asking at EE.)
-- b.r.t.
The short answers are:
1) No. There is no logic there now, but there certainly could be. I would need better requirements before I could start planning an approach, though.
2) Yes it is possible. You could either set it up so when it's run manually it checks to see if it should even bother, or have it run on schedule and import only after there's been a change to the Excel file. You would use the FileDateTime function for that one. (You can learn some about it in the help files, or by asking at EE.)
-- b.r.t.
ASKER
1 Yea I know the logic is not there now, but I may have to go that way - still waiting to see.
2. Great
Thanks for all your help - look for the next question in the morning
2. Great
Thanks for all your help - look for the next question in the morning
ASKER
Here is a quick question that I think goes with this.
I have a computed field that @Sum(Jan_Plan Jan_OL)
The only way I can get this value to show up in a view is to go in the form open for edit and save. Then it shows up in the views. Is there a way we can do this in the program?
I have a computed field that @Sum(Jan_Plan Jan_OL)
The only way I can get this value to show up in a view is to go in the form open for edit and save. Then it shows up in the views. Is there a way we can do this in the program?
Absolutely, Jaziar.
Let's assume this field is called Jan_Total, for purposes of this code. After Jan_Plan and Jan_OL are set by your code, you can have this line in your script:
doc.JanTotal = Jan_Plan(0) + Jan_OL(0)
Remember that all Notes items (fields) are, by their nature, lists. So any time you're referring to a field's value in LotusScript, you must refer to it as though you're dealing with an array of values. Jan_Plan(0) gives you the first (and, in this case, only) entry in that array of values.
-- b.r.t.
Let's assume this field is called Jan_Total, for purposes of this code. After Jan_Plan and Jan_OL are set by your code, you can have this line in your script:
doc.JanTotal = Jan_Plan(0) + Jan_OL(0)
Remember that all Notes items (fields) are, by their nature, lists. So any time you're referring to a field's value in LotusScript, you must refer to it as though you're dealing with an array of values. Jan_Plan(0) gives you the first (and, in this case, only) entry in that array of values.
-- b.r.t.
ASKER
You are correct - that works - I have a meeting at 10:00 if it goes well I will be asking the question about seeing current time - question 1 from above.
Thanks
Thanks
ASKER
This will require that every line in your Excel spreadsheet have some "key" combination of fields that will be unique. Say your first four columns, Group, Manager4th, Area, and Category end up being your key. That means that regardless of what other data is in your other columns, there will never be two rows that have the same combination of these four. (If you can do it with fewer than four, that's fine. This example assumes four.)
It is possible that there could be the same combo of those four. Maybe not I will try and adjust the spreadsheet so that don't happen.