Link to home
Start Free TrialLog in
Avatar of Jaziar
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.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

ASKER CERTIFIED SOLUTION
Avatar of TSO Fong
TSO Fong
Flag of Sweden image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jaziar
Jaziar

ASKER

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.

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.
Avatar of Jaziar

ASKER

I have the first 4 being unique now.  So I am in the process of applying
Avatar of Jaziar

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
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.
Avatar of Jaziar

ASKER

You are correct I dod not paste the subs in first
Avatar of Jaziar

ASKER

am I putting the code in the (jat2 docs)?  being hidden it will be hard to use the upload button
Avatar of Jaziar

ASKER

Barry could this code in GetDoc be causing a type mismatch

      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.
Avatar of Jaziar

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 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.
Avatar of Jaziar

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.
Avatar of Jaziar

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.



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.
Avatar of Jaziar

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
Avatar of Jaziar

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

Avatar of Jaziar

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