?
Solved

Putting Logic in a Import Function

Posted on 2005-03-04
25
Medium Priority
?
296 Views
Last Modified: 2013-12-18
Right now I have a import program (Barry should know this well).  That imports a spreadsheet in to a form.

My Form = jat2
First Import Jan 1

[Group] [4thManager] [Area] [Catergory]
  Mono       Smith         Color      Travel

[Jan_Plan]  [Jan_OL]  [Jan_Act]  [JanDelta]
    10              10
    10              12
    10              08          08
[Feb_Plan]  [Feb_OL]  [Feb_Act]  [FebDelta]
    20              20          
    20              18
    20              18           18
[Mar_Plan]  [Mar_OL]  [Mar_Act]  [MarDelta]
     12              12
     12              09
     12              10            

I will try to use numbers to explain my question.
The First Import places the values in *_Plan and *_OL
Then the second Import (Jan 30) leaves *_Plan as is and changes *_OL (See Above) - Great working as it should
Then the next Import (Feb 1) comes.  It should Leave *_Plan alone and place the new imported value in *_OL and *_ACT. (see Above)

I would like for the program look at current date and if the month is Feb thanks the column value for Jan.Col(4) places it in Jan_OL and Jan_Act and then puts the rest in the *_OL .

If the Date is March the import value for Jan.Col(4) = Jan_Act  Feb.Col(5) = Feb_Act  Mar.Col(6) = Mar_Ol  Apr.Col(7) = Apr_Ol.

I hope this makes sense.  Get current month and place the current value in OL and all previous months get placed in Actual.

This is like for planning people.  

I think in Jan I need 5 people so that is my Plan and my OL
in Feb I only needed 4.  I want to keep my Plan and OL for Reference but I only needed Actually 4.  So all previous months are month_acts.
0
Comment
Question by:Jaziar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 10
25 Comments
 
LVL 7

Expert Comment

by:BarryTice
ID: 13459503
I have some questions about this, Jaziar, for what's supposed to happen in, say, January. Should it affect December? By the time we reach August, should it still be making changes to January?

By Jan.Col(4), do you mean the JanDelta? Or, are you referring to column 4 from the spreadsheet from which you're importing?

-- b.r.t.

p.s. Question 2 would have been quicker...
0
 

Author Comment

by:Jaziar
ID: 13459876
No, January will never affect Decemmber - due to January being a new year.  Once we leave January and go into feb.  There is no need to update January - when we reach August -  We will update July_Act and the All months until Dec_OL.  here is no need to worry with June - May - Etc.  

Jan.Col(4), I was just saying that was a vlaue off the spreadsheet - no importance.  I dont think we are going to do Question 2.
0
 

Author Comment

by:Jaziar
ID: 13460005
Barry I dont mond giving more points - I understand this is a hard challenge.  But very important.
0
Independent Software Vendors: 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!

 
LVL 7

Expert Comment

by:BarryTice
ID: 13460145
Bummer. Question 2 was easy.

OK, what happens in December? Will there be a reason to update predictions for 2006? For that matter, will there be a new spreadsheet for 2006? I believe I mentioned the other day that you might want to put a year field in these documents somewhere, and include that as part of the key that the GetDoc() function uses to determine if a document already exists. You would have to have some kind of prompt in there (just before or just after you prompt for the file name to import). Perhaps if it's not January or December, just assume it's the current year and not prompt?

From my understanding, any import in the current month updates the current month and the previous month (excepting January), plus all future months in the same calendar year, right? Should this occur only for the first import in the new month, or every time the spreadsheet is imported?

-- b.r.t.
0
 
LVL 7

Expert Comment

by:BarryTice
ID: 13460159
I'm fine with the points you're offering, Jaziar. You've been more than generous, and have pushed me up to 10th place in Notes for the year.
0
 
LVL 14

Expert Comment

by:p_partha
ID: 13460328
Barry
You deserve that post.. You are also one of the guys whose answers I read on a regular basis...Keep up the good work.

-Partha
0
 
LVL 7

Expert Comment

by:BarryTice
ID: 13460641
I'm humbled by your recognition, Partha. You've got more points in Notes this year than I have since I joined five years ago. I pitch in where I can, but am still learning far more than I'm teaching.

-- b.r.t.
0
 

Author Comment

by:Jaziar
ID: 13461156
Yes - Year has became important.

In 2006 A new excel spreadsheet will be imported.

Each Month has a plan, ol, act and delta field.

First import sets all the months plan and ol.  The Plan fields never change.  
Second import should check date for month.  If the month is equal to March - here is what happens

Jan = We dont care  
Feb_Act is set
Mar_OL is set
April_OL is set
May_OL is set
ETC

If the current month is may

Jan = Dont Care
Feb = Dont Care
Mar = Dont Care
April_Act is set
May_OL is Set
June_OL is Set

The Current Month and all future months the OL field is set
The Previus Month the Act field is set
ALL months before that the Previous Month - we dont care about, but the Act field can be reset be the spread sheet, becuase that number will remain the same.
0
 

Author Comment

by:Jaziar
ID: 13461174
[Plan]                                       [OL]                                                                             [Act]
   ^                                            ^                                                                                  ^
 Always set on first Import          Always the Current Month and Future Months               Always the Previous Month

Not so worried about YearEnd right now

Hope this helps
0
 
LVL 7

Expert Comment

by:BarryTice
ID: 13461201
I'll look into it in a bit, Jaziar (if nobody beats me to it!). I've got to leave for a dental appointment.
0
 

Author Comment

by:Jaziar
ID: 13461408
Good Luck with that - I dont have about 2 hours left to my work day.  we can pick this up on Monday Morning
0
 
LVL 7

Expert Comment

by:BarryTice
ID: 13463690
I believe this will do what you want it to. I also believe you'll need to adjust the index values for the colValues array. The way things are numbered now would work if columns 5 through 16 were your twelve months. But from the look of things, you have quarterly breakdowns in the middle, so it will need to be fixed.

This is, more or less, what your AdjustDoc subroutine should look like:
==== BEGIN PASTE ====
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
      Dim intMonth As Integer
      
      intMonth = Month(Now)
      
      doc.Dec_OL = colValues(16)
      If intMonth = 12 Then
            doc.Nov_Act = colValues(15)
      End If
      If intMonth < 12 Then
            doc.Nov_OL = colValues(15)
            If intMonth = 11 Then
                  doc.Oct_Act = colValues(14)
            End If
      End If
      If intMonth < 11 Then
            doc.Oct_OL = colValues(14)
            If intMonth = 10 Then
                  doc.Sep_Act = colValues(13)
            End If
      End If
      If intMonth < 10 Then
            doc.Sep_OL = colValues(13)
            If intMonth = 9 Then
                  doc.Aug_Act = colValues(12)
            End If
      End If
      If intMonth < 9 Then
            doc.Aug_OL = colValues(12)
            If intMonth = 8 Then
                  doc.Jul_Act = colValues(11)
            End If
      End If
      If intMonth < 8 Then
            doc.Jul_OL = colValues(11)
            If intMonth = 7 Then
                  doc.Jun_Act = colValues(10)
            End If
      End If
      If intMonth < 7 Then
            doc.Jun_OL = colValues(10)
            If intMonth = 6 Then
                  doc.May_Act = colValues(9)
            End If
      End If
      If intMonth < 6 Then
            doc.May_OL = colValues(9)
            If intMonth = 5 Then
                  doc.Apr_Act = colValues(8)
            End If
      End If
      If intMonth < 5 Then
            doc.Apr_OL = colValues(8)
            If intMonth = 4 Then
                  doc.Mar_Act = colValues(7)
            End If
      End If
      If intMonth < 4 Then
            doc.Mar_OL = colValues(7)
            If intMonth = 3 Then
                  doc.Feb_Act = colValues(6)
            End If
      End If
      If intMonth < 3 Then
            doc.Feb_OL = colValues(6)
            If intMonth = 2 Then
                  doc.Jan_Act = colValues(5)
            Else
                  doc.Jan.OL = colValues(5)
            End If
      End If
      
      ' doc.Q1_Total_OL = colValues(8)      ' Either value 8 is first quarter, or it's April. You may need to make some adjustments.
      
      Call doc.Save(True, False)
End Sub
===== END PASTE =====

Just as this uses Month(Now) to return an integer of the current month, there is a Year(Now) function that returns a four-digit integer of the current year. (I guess that means it's not Y10K compliant...) You can use some combination of Month(Now) and Year(Now) to determine (A) whether you need to prompt for the which year to affect in, say, November through January; and (B) what year to stuff into your documents in the MakeNewDoc routine accordingly.

For the sake of simplicity, I would recommend saving the year in the documents as text rather than as a number. That way, the formula for the column in the hidden view, which uses includes the year as part of the document key, won't have to perform an @Text conversion at runtime. That will speed things up some small amount in view indexing. You can do that in your MakeNewDoc function like this:

doc.Year = Trim$(Str$(Year(Now)))

The Str$() converts the numeric year to a text string, but does so with a space at the beginning (the space that would be filled with a minus sign were the number negative). The Trim$() removes that space. (You could use Trim(Str(Year(Now))) without the $s, as those functions return variants of type String rather than Strings directly. But my coding days go back to Applesoft, and to me the functions just don't look right without them.)

Then, the formula for that column in your (Jat2 Docs) view would be:
Year + @Trim(Group) + @Trim(Manager4th) + @Trim(Area) + @Trim(Category)

This assumes you were using the column formula I gave you in the Phase 2 question.

It also means that in the GetDoc() function, you'll need to change
strKey = ""
to
strKey = strYear

You would do this just before the for/next loop that concatenates the four colValues into strKey, near the top. This assumes that you have declared strYear somewhere, and that you have performed your prompt or done your calculation to get the year.

Aw, nuts. Here, the code all together looks like this:
==== BEGIN PASTE ====
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
      Dim intMonth As Integer
      Dim intYear As Integer
      Dim strYear As String
      Dim strYearss(0 To 1) As String
      Dim varYear As Variant
      
      Set db = session.CurrentDatabase
      
      file = ws.OpenFileDialog(False, "File List", "", "c:")
      If Isempty(file) Then
            Exit Sub
      End If
      
      intYear = Year(Now)
      strYear = Trim$(Str$(intYear))
      intMonth = Month(Now)
      
      If intMonth = 1 Then
            strYears(0) = Trim$(Str$(intYear - 1))      ' Previous year
            strYears(1) = strYear      ' This year
      Elseif intMonth > 10 Then
            strYears(0) = strYear      ' This year
            strYears(1) = Trim$(Str$(intYear + 1))      ' Next year
      Else
            strYears(0) = ""            ' Empty, meaning use this year without prompting
      End If
      
      If strYears(0) >< "" Then
            ' {
            varYear = ws.Prompt(PROMPT_OKCANCELLIST, "Select Year", _
            "Please indicate the year for which you are importing data:", strYear, strYears)
            ' }
            If Isempty(varyear) Then      ' User hit Cancel
                  Exit Sub
            End If
            strYear = varYear
      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 (or update) a document out of what we do have.
      Do While Not blankLine
            Set doc = GetDoc(strYear, db, colValues)
            If (doc Is Nothing) Then
                  Set doc = MakeNewDoc(strYear, 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 PASTE =====

==== BEGIN PASTE ====
Function GetDoc(strYear As String, 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 = strYear
      ' 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
===== END PASTE =====

==== BEGIN PASTE ====
Function MakeNewDoc(strYear As String, db As NotesDatabase, colValues() As Variant) As NotesDocument
      Dim doc As NotesDocument
      
      Set doc = New NotesDocument(db)
      doc.Form = "Jat2"
      doc.Year = strYear
      doc.Group = colValues(1)
      doc.Manager_4th = colValues(2)
      doc.Area = colValues(3)
      doc.Category = 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
===== END PASTE ====

Note that the year is now being passed as a parameter to the MakeNewDoc and GetDoc functions. The test near the top of the Click subroutine is set to prompt for the year if the month is 1 (January), 11 (November), or 12 (December). That way you can deal with the previous year's data in January, and can begin importing next year's data in November. You can adjust that as necessary.

Whew!

Let me know how this works for you.

-- b.r.t.
0
 
LVL 7

Expert Comment

by:BarryTice
ID: 13463699
I've just realized that the above code will NEVER set Dec_Act. You'll have to do that manually. Of course, that means there's no need to ever run this against last year's data. Which means that part of the ...

Aw, nuts.

Ignore that last post. I'll get back to this later.

-- b.r.t.
0
 
LVL 7

Accepted Solution

by:
BarryTice earned 2000 total points
ID: 13463840
OK. Use this Click subroutine and AdjustDoc subroutine. The GetDoc and MakeNewDoc functions above should be OK.

==== BEGIN PASTE ====
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
      Dim intMonth As Integer
      Dim intYear As Integer
      Dim strYear As String
      Dim strYears(0 To 1) As String
      Dim varYear As Variant
      
      Set db = session.CurrentDatabase
      
      file = ws.OpenFileDialog(False, "File List", "", "c:")
      If Isempty(file) Then
            Exit Sub
      End If
      
      intYear = Year(Now)
      strYear = Trim$(Str$(intYear))
      intMonth = Month(Now)
      
      If intMonth = 1 Then
            strYears(0) = Trim$(Str$(intYear - 1))      ' Previous year
            strYears(1) = strYear      ' This year
      Elseif intMonth > 10 Then
            strYears(0) = strYear      ' This year
            strYears(1) = Trim$(Str$(intYear + 1))      ' Next year
      Else
            strYears(0) = ""            ' Empty, meaning use this year without prompting
      End If
      
      If strYears(0) >< "" Then
            ' {
            varYear = ws.Prompt(PROMPT_OKCANCELLIST, "Select Year", _
            "Please indicate the year for which you are importing data:", strYear, strYears)
            ' }
            If Isempty(varyear) Then      ' User hit Cancel
                  Exit Sub
            End If
            strYear = varYear
      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 (or update) a document out of what we do have.
      Do While Not blankLine
            Set doc = GetDoc(strYear, db, colValues)
            If (doc Is Nothing) Then
                  Set doc = MakeNewDoc(strYear, db, colValues)
            End If
            ' This function adds/adjusts all the current values, whether the doc is new or existing.
            Call AdjustDoc(strYear, 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 PASTE =====

==== BEGIN PASTE ====
Sub AdjustDoc(strYear As String, 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
      Dim intMonth As Integer
      
      intMonth = Month(Now)
      
      If Val(strYear) = Year(Now) Then
            ' We're processing the current year. Set everything as normal.
            doc.Dec_OL = colValues(16)
            If intMonth = 12 Then
                  doc.Nov_Act = colValues(15)
            End If
            If intMonth < 12 Then
                  doc.Nov_OL = colValues(15)
                  If intMonth = 11 Then
                        doc.Oct_Act = colValues(14)
                  End If
            End If
            If intMonth < 11 Then
                  doc.Oct_OL = colValues(14)
                  If intMonth = 10 Then
                        doc.Sep_Act = colValues(13)
                  End If
            End If
            If intMonth < 10 Then
                  doc.Sep_OL = colValues(13)
                  If intMonth = 9 Then
                        doc.Aug_Act = colValues(12)
                  End If
            End If
            If intMonth < 9 Then
                  doc.Aug_OL = colValues(12)
                  If intMonth = 8 Then
                        doc.Jul_Act = colValues(11)
                  End If
            End If
            If intMonth < 8 Then
                  doc.Jul_OL = colValues(11)
                  If intMonth = 7 Then
                        doc.Jun_Act = colValues(10)
                  End If
            End If
            If intMonth < 7 Then
                  doc.Jun_OL = colValues(10)
                  If intMonth = 6 Then
                        doc.May_Act = colValues(9)
                  End If
            End If
            If intMonth < 6 Then
                  doc.May_OL = colValues(9)
                  If intMonth = 5 Then
                        doc.Apr_Act = colValues(8)
                  End If
            End If
            If intMonth < 5 Then
                  doc.Apr_OL = colValues(8)
                  If intMonth = 4 Then
                        doc.Mar_Act = colValues(7)
                  End If
            End If
            If intMonth < 4 Then
                  doc.Mar_OL = colValues(7)
                  If intMonth = 3 Then
                        doc.Feb_Act = colValues(6)
                  End If
            End If
            If intMonth < 3 Then
                  doc.Feb_OL = colValues(6)
                  If intMonth = 2 Then
                        doc.Jan_Act = colValues(5)
                  Else
                        doc.Jan_OL = colValues(5)
                  End If
            End If
      Else
            ' We're processing a year other than the current year.
            If Val(strYear) = Year(Now) + 1 Then
                  ' We must be processing a spreadsheet for next year. Set all the OL values.
                  doc.Jan_OL = colValues(5)
                  doc.Feb_OL = colValues(6)
                  doc.Mar_OL = colValues(7)
                  doc.Apr_OL = colValues(8)
                  doc.May_OL = colValues(9)
                  doc.Jun_OL = colValues(10)
                  doc.Jul_OL = colValues(11)
                  doc.Aug_OL = colValues(12)
                  doc.Sep_OL = colValues(13)
                  doc.Oct_OL = colValues(14)
                  doc.Nov_OL = colValues(15)
                  doc.Dec_OL = colValues(16)
            Else
                  ' We must be processing a spreadsheet from last year. Set the December value only.
                  doc.Dec_Act = colValues(16)
            End If
      End If
      
      ' doc.Q1_Total_OL = colValues(8)      ' Either value 8 is first quarter, or it's April. You may need to make some adjustments.
      
      Call doc.Save(True, False)
End Sub
===== END PASTE =====

That should do it.

-- b.r.t.
0
 

Author Comment

by:Jaziar
ID: 13477379
WOW - Ok I am trying to parse through all this information.  
0
 
LVL 7

Expert Comment

by:BarryTice
ID: 13477420
Take your time...
0
 

Author Comment

by:Jaziar
ID: 13480322
It seems to be working - WOW.  I will manually have to go in to each document and add The Jan_OL and Jan_act.  BUt I think after that it will work great.  Barry you have done a great job.  And Guess what - I have yet other questions coming :).

Not regarding this stuff - but more of mailing tools.
0
 
LVL 7

Expert Comment

by:BarryTice
ID: 13480930
Thanks for the points, Jaziar. At this rate, I'll be in the top nine...

You could write a one-off agent to add the Jan_OL and Jan_Act, at this point. It might be quicker than manually updating 130 documents.

Or, you might be able to get away with setting the clock/calendar on your local PC to a date in January, running the import, setting it to a date in February, running the import, and resetting it correctly.

-- b.r.t.
0
 
LVL 7

Expert Comment

by:BarryTice
ID: 13486802
OK, so you've reset your date, and now you're getting duplicate docuemnts?
0
 

Author Comment

by:Jaziar
ID: 13486845
Yes - It did not duplicate yesterday, but today I cleaned all the documents and reset my date to January and imported.


IT worked well.  Then I set my date to Feb and imported - the logic worked but it made duplicate documents.  Here is the code I am using, maybe I messed something up

Click

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
      Dim intMonth As Integer
      Dim intYear As Integer
      Dim strYear As String
      Dim strYears(0 To 1) As String
      Dim varYear As Variant
      
      Set db = session.CurrentDatabase
      
      file = ws.OpenFileDialog(False, "File List", "", "c:")
      If Isempty(file) Then
            Exit Sub
      End If
      
      intYear = Year(Now)
      strYear = Trim$(Str$(intYear))
      intMonth = Month(Now)
      
      If intMonth = 1 Then
            strYears(0) = Trim$(Str$(intYear - 1))     ' Previous year
            strYears(1) = strYear     ' This year
      Elseif intMonth > 10 Then
            strYears(0) = strYear     ' This year
            strYears(1) = Trim$(Str$(intYear + 1))     ' Next year
      Else
            strYears(0) = ""          ' Empty, meaning use this year without prompting
      End If
      
      If strYears(0) >< "" Then
          ' {
            varYear = ws.Prompt(PROMPT_OKCANCELLIST, "Select Year", _
            "Please indicate the year for which you are importing data:", strYear, strYears)
          ' }
            If Isempty(varyear) Then     ' User hit Cancel
                  Exit Sub
            End If
            strYear = varYear
      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) = xlSheet.Cells(intRow, intCount).Value
            If colValues(intCount) >< "" Then blankLine = False
      Next
      
     ' While we don't have a blank line, create (or update) a document out of what we do have.
      Do While Not blankLine
            Set doc = GetDoc(strYear, db, colValues)
            If (doc Is Nothing) Then
                  Set doc = MakeNewDoc(strYear, db, colValues)
            End If
          ' This function adds/adjusts all the current values, whether the doc is new or existing.
            Call AdjustDoc(strYear, 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


MakeNewDoc

Function MakeNewDoc(strYear As String, db As NotesDatabase, colValues() As Variant) As NotesDocument
      Dim doc As NotesDocument
      
      Set doc = New NotesDocument(db)
      doc.Form = "Jat2"
      doc.Year = strYear
      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)
      doc.Apr_Plan = colValues(9)
      doc.May_Plan = colValues(10)
      doc.Jun_Plan = colValues(11)
      doc.Q2_Total_Plan = colValues(12)
      doc.Jul_Plan = colValues(13)
      doc.Aug_Plan = colValues(14)
      doc.Sep_Plan = colValues(15)
      doc.Q3_Total_Plan = colValues(16)
      doc.Oct_Plan = colValues(17)
      doc.Nov_Plan = colValues(18)
      doc.Dec_Plan = colValues(19)
      doc.Q4_Total_Plan = colValues(20)
      Set MakeNewDoc = doc
End Function

GetDoc

Function GetDoc(strYear As String, 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 = strYear
     ' 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


AdjustDoc

Sub AdjustDoc(strYear As String, 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
      Dim intMonth As Integer
      
      intMonth = Month(Now)
      
      If Val(strYear) = Year(Now) Then
          ' We're processing the current year. Set everything as normal.
            doc.Dec_OL = colValues(19)
            If intMonth = 12 Then
                  doc.Nov_Act = colValues(18)
            End If
            If intMonth < 12 Then
                  doc.Nov_OL = colValues(18)
                  If intMonth = 11 Then
                        doc.Oct_Act = colValues(17)
                  End If
            End If
            If intMonth < 11 Then
                  doc.Oct_OL = colValues(17)
                  If intMonth = 10 Then
                        doc.Sep_Act = colValues(15)
                  End If
            End If
            If intMonth < 10 Then
                  doc.Sep_OL = colValues(15)
                  If intMonth = 9 Then
                        doc.Aug_Act = colValues(14)
                  End If
            End If
            If intMonth < 9 Then
                  doc.Aug_OL = colValues(14)
                  If intMonth = 8 Then
                        doc.Jul_Act = colValues(13)
                  End If
            End If
            If intMonth < 8 Then
                  doc.Jul_OL = colValues(13)
                  If intMonth = 7 Then
                        doc.Jun_Act = colValues(11)
                  End If
            End If
            If intMonth < 7 Then
                  doc.Jun_OL = colValues(11)
                  If intMonth = 6 Then
                        doc.May_Act = colValues(10)
                  End If
            End If
            If intMonth < 6 Then
                  doc.May_OL = colValues(10)
                  If intMonth = 5 Then
                        doc.Apr_Act = colValues(9)
                  End If
            End If
            If intMonth < 5 Then
                  doc.Apr_OL = colValues(9)
                  If intMonth = 4 Then
                        doc.Mar_Act = colValues(7)
                  End If
            End If
            If intMonth < 4 Then
                  doc.Mar_OL = colValues(7)
                  If intMonth = 3 Then
                        doc.Feb_Act = colValues(6)
                  End If
            End If
            If intMonth < 3 Then
                  doc.Feb_OL = colValues(6)
                  If intMonth = 2 Then
                        doc.Jan_Act = colValues(5)
                  Else
                        doc.Jan_OL = colValues(5)
                  End If
            End If
      Else
          ' We're processing a year other than the current year.
            If Val(strYear) = Year(Now) + 1 Then
               ' We must be processing a spreadsheet for next year. Set all the OL values.
                  doc.Jan_OL = colValues(5)
                  doc.Feb_OL = colValues(6)
                  doc.Mar_OL = colValues(7)
                  doc.Apr_OL = colValues(9)
                  doc.May_OL = colValues(10)
                  doc.Jun_OL = colValues(11)
                  doc.Jul_OL = colValues(13)
                  doc.Aug_OL = colValues(14)
                  doc.Sep_OL = colValues(15)
                  doc.Oct_OL = colValues(17)
                  doc.Nov_OL = colValues(18)
                  doc.Dec_OL = colValues(19)
            Else
               ' We must be processing a spreadsheet from last year. Set the December value only.
                  doc.Dec_Act = colValues(19)
            End If
      End If
      
     ' doc.Q1_Total_OL = colValues(8)     ' Either value 8 is first quarter, or it's April. You may need to make some adjustments.
      
      Call doc.Save(True, False)
End Sub



0
 
LVL 7

Expert Comment

by:BarryTice
ID: 13486890
The obvious suspect is the GetDoc() function. If it can't find the existing document, it returns Nothing, which is a sign to the main subroutine to make a new document.

So I suspect the problem is between that and the view it uses.

What is the column formula you're using in your (Jat2 Docs) view?

-- b.r.t.
0
 

Author Comment

by:Jaziar
ID: 13486946
@Trim(Group) + @Trim(Manager4th) + @Trim(Area) + @Trim(Catergory)
0
 
LVL 7

Expert Comment

by:BarryTice
ID: 13487174
That's the problem, Jaziar.

The long post that was two posts before the accepted answer had this in the middle:

Then, the formula for that column in your (Jat2 Docs) view would be:
Year + @Trim(Group) + @Trim(Manager4th) + @Trim(Area) + @Trim(Category)

You might want to look over that whole chunk of text, because it talks about storing the year as text rather than as a number to speed up view indexing.

The code you're using includes the year as part of that key to find the files. The view doesn't include the year at all, so it's never going to find a match.

-- b.r.t.
0
 

Author Comment

by:Jaziar
ID: 13487216
I have said it once and I will say it again - You are the MAN!!!

Yes it is working now!!!!!!!
0
 
LVL 7

Expert Comment

by:BarryTice
ID: 13487356
Jaziar --

The difference between you and me is I've already found these problems once. It's easy to find troubles in code when you know what you're looking for, and it's easy to know what you're looking for when you've had to find it before.

Glad to help.
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question