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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

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.Application")
      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

0
Jaziar
Asked:
Jaziar
  • 12
  • 8
1 Solution
 
BarryTiceCommented:
Good morning, Jaziar.

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

The first thing you'll need to do is create a view that lets you look up existing records. I suggest calling it (Jat2 Docs). (Note that the name of the view is in parentheses to keep it hidden.) Set the view selection as:
SELECT Form = "Jat2"

Make this the formula for the first (only) column:
@Trim(Group) + @Trim(Manager4th) + @Trim(Area) + @Trim(Category)

Make the first column sorted standard. Save and close.

Change your import (above) to this:
' ==== BEGIN CODE ====
Sub Click(Source As Button)
      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.Application")
      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      ' 30 will take you out to column AD
            colValues(intCount) = Trim$(Str$(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 = GetDoc(db, colValues)
            If (doc Is Nothing) Then
                  Set doc = MakeNewDoc(db, colValues)
            End If
            ' This function adds all the current values, whether the doc is new or existing.
            Call AdjustDoc(doc, colValues)
          ' 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
End Sub
' ===== END CODE =====

Add the following functions and subroutine. (You can do this by going to the line after End Sub [press Enter if you need to] and pasting this in.)

' ==== BEGIN CODE ====
Function GetDoc(db As NotesDatabase, colValues() As Variant) As NotesDocument
      Dim view As NotesView
      Dim strKey As String
      Dim doc As NotesDocument
      Dim intCount As Integer
      Dim item As NotesItem
      
      ' See if there is already a document with the same first four values
      Set view = db.GetView("(Jat2 Docs)")
      ' Create a temporary document
      Set doc = New NotesDocument(db)
      strKey = ""
      ' Concatenate the first four colValues into a string key
      For intCount = 1 To 4
            ' Create temporary items in that document, so we can reliably get the text of the variant colValues
            Set item = New NotesItem(doc, "Temp" & Trim$(Str$(intCount)), colValues(intCount))
            strKey = strKey & Trim$(item.Text)
      Next
      If strKey = "" Then
            ' If the key is blank, let the outer routine make a new document.
            ' (This should probably never happen, by the way.)
            Set doc = Nothing
      Else
            ' Get the document that matches this key.
            Set doc = view.GetDocumentByKey(strKey, True)
            ' Note that if no document matches the key, doc = Nothing.
      End If
      ' Return the doc (or Nothing) that we found.
      Set GetDoc = doc
End Function
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.Category = colValues(4)
      
      Set MakeNewDoc = doc
End Function
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 = colValues(5)
      doc.Feb = colValues(6)
      doc.Mar = colValues(7)
      doc.Q1_Total = colValues(8)
      Call doc.Save(True, False)
End Sub
' ===== END CODE =====

What this does is read a line from the spreadsheet and call the GetDoc function to see if the document already exists. GetDoc concatenates those four key fields into one string and performs a lookup against this new view you just created. If it finds a document, it returns it for update. If not, it returns "Nothing" and the main routine then knows it needs to use the MakeNewDoc function to create a new document. (This way, if someone adds a line to your spreadsheet the first import will be seamless.)

Then the document (either already existing or just created in MakeNewDoc) is passed to AdjustDoc, which updates all the other fields.

If there are fields you don't want to have updated every time, put them in the MakeNewDoc function. Any field that should be updated every time the import is run should be in the AdjustDoc subroutine.

Does this make sense?

-- b.r.t.
0
 
JaziarAuthor Commented:
wow - nice job - Let me look over this and do some test.  I am still a little unclear about the

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.

0
 
BarryTiceCommented:
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JaziarAuthor Commented:
I have the first 4 being unique now.  So I am in the process of applying
0
 
JaziarAuthor Commented:
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
0
 
BarryTiceCommented:
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.
0
 
JaziarAuthor Commented:
You are correct I dod not paste the subs in first
0
 
JaziarAuthor Commented:
am I putting the code in the (jat2 docs)?  being hidden it will be hard to use the upload button
0
 
JaziarAuthor Commented:
Barry could this code in GetDoc be causing a type mismatch

      Set item = New NotesItem(doc, "Temp" & Trim$(Str$(intCount)), colValues(intCount))
0
 
BarryTiceCommented:
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.
0
 
JaziarAuthor Commented:
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.


0
 
BarryTiceCommented:
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.
0
 
JaziarAuthor Commented:
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.
0
 
BarryTiceCommented:
OK, Jaziar. Let me know if I can be any more help.

-- b.r.t.
0
 
JaziarAuthor Commented:
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.



0
 
BarryTiceCommented:
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.
0
 
JaziarAuthor Commented:
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
0
 
JaziarAuthor Commented:
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?
0
 
BarryTiceCommented:
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.

0
 
JaziarAuthor Commented:
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
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 12
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now