1. Export all documents of any Notes view into multiple Excel sheets with N-number of rows per sheet
   
   This example will ask the user for the number of rows that are to be written per Excel sheet. Excel supports a maximum of 65535 rows per sheet and up to 256 sheets. This profile allows IN THEORY to export a maximum of 65535 * 256 or 16,776,960 rows in to a single Excel workbook.
   
2. Export all documents of any Notes view into multiple Excel sheets, one sheet per leading letter of the first sorted or categorised column in the view
   
   This example will create a new sheet for every alphabetic letter of the first sorted and or categorised column of the view. Excel supports up to 256 sheets which should be fine unless other characters then A-Z & 0-9 occur.
   
3. Export all documents of any categorised Notes view into multiple Excel sheets, one sheet per top level category
   
   This example will create a new sheet for every top level category. Excel supports up to 256 sheets, hence this profile allows to export up to 256 top level view categories. Categories for all levels are exported as they appear in the view.

1. Export View To Excel - New Sheet for Every X Rows
2. Export View To Excel - New Sheet per Alphabetic Letter
3. Export View To Excel - New Sheet per Category

Const CB_INITIALISE = 3
Const CB_ACTIONONLY = 16
Const CB_BFOREXPORT = 10
Const CB_INVALID = 12
Const CB_CATEGORY = 13
Const CB_TOTAL = 14
Const CB_CONFLICT = 15
Const CB_BFORREADNOTES = 6
Const CB_BFORWRITECOM = 1
Const CB_AFTERWRITECOM = 9
Const CB_BFORCOMACTION = 5
Const CB_BFORNOTESACTION = 2
Const CB_TERMINATE = 4

select case stats.cbstatus
case CB_INITIALISE

      dim tmpvar as variant
      dim myarray(2) as long
      dim ispbar as integer
   
      ispbar = stats.pbar.enabled

      if ispbar then
           Call stats.pbar.DisableProgressBar()
      end if

      tmpvar = Inputbox("Please enter the number of rows per sheet","Multiple Sheet Export",0)

      tmpvar = val("" & tmpvar)

      if tmpvar <= 0 then
            export.continue = false
      end if

      if ispbar then
           Call stats.pbar.EnableProgressBar(False) 
      end if

      myarray(0) = tmpvar ' stores number of rows per sheet
      myarray(1) = tmpvar ' number of rows to reach to change to next sheet
      myarray(2) = 0      ' current number of rows written to Excel

      stats.uservar = myarray

case CB_ACTIONONLY
case CB_BFOREXPORT
case CB_INVALID
case CB_CATEGORY

     ' when exporting a view using the option to format the result as the
     ' view Integra does export category entries. so we need to check on
     ' category change also if the maximum number of rows per sheet as been
     ' reached
     stats.uservar(2) = stats.uservar(2) + 1

     If stats.uservar(2) > stats.uservar(1) Then
           ' if the number of rows per sheet has been reached, increase the
           ' number of rows for the next sheet by the number of rows per
           ' sheet and insert a sheet

            stats.uservar(1) = stats.uservar(1) + stats.uservar(0)

            ' InsertNextSheet has two parameters:  the first parameter allows
            ' to pass on a name for the current sheet, the second parameter
            ' defines whether the passed on name is a prefix (True) and a
            ' sequential sheet number is to be added or if the passed on name
            ' has to be used as is.

            call InsertNextSheet("",true)
     end if

case CB_TOTAL
case CB_CONFLICT
case CB_BFORREADNOTES

     ' same as above, but now for a real Notes document to be exported...
     stats.uservar(2) = stats.uservar(2) + 1

     If stats.uservar(2) > stats.uservar(1) Then
            stats.uservar(1) = stats.uservar(1) + stats.uservar(0)

            call InsertNextSheet("",true)
     end if

case CB_BFORWRITECOM
case CB_AFTERWRITECOM
case CB_BFORCOMACTION

     ' as Integra requires the current data sheet to be the first one in the
     ' Excel workbook we need after all documents are exported to move
     ' the first sheet to the last position in the workbook

     Call MoveFirstSheetLast("",true)

case CB_BFORNOTESACTION

   if not stats.savdoc is nothing then
       ' before save as...
   else
       ' before send to
   end if

case CB_TERMINATE
end select

Export View To Excel - New Sheet per Alphabetic Letter

Const CB_INITIALISE = 3
Const CB_ACTIONONLY = 16
Const CB_BFOREXPORT = 10
Const CB_INVALID = 12
Const CB_CATEGORY = 13
Const CB_TOTAL = 14
Const CB_CONFLICT = 15
Const CB_BFORREADNOTES = 6
Const CB_BFORWRITECOM = 1
Const CB_AFTERWRITECOM = 9
Const CB_BFORCOMACTION = 5
Const CB_BFORNOTESACTION = 2
Const CB_TERMINATE = 4

select case stats.cbstatus
case CB_INITIALISE

      stats.uservar = ""

case CB_ACTIONONLY
case CB_BFOREXPORT
case CB_INVALID
case CB_CATEGORY

        ' similar to the previous code, we need to check if either the first
        ' letter of the current category value has changed or not. in case
        ' the view is not categorised this event will simply never occur

        if stats.uservar = "" then
             ' very first call, we need to save the current first letter 

             stats.uservar = ucase(left(stats.curcatvalue,1))

        elseif not stats.curcatlevel = 0 then
              ' if not the top level category, we don't need to do anything

        elseif not ucase(left(stats.curcatvalue,1)) = stats.uservar then
             ' if the first letter has changed we need to insert a new sheet
             ' note: the parameters here are different in that the name of
             ' the current sheet is going to be the first letter of the top
             ' level category string

             call InsertNextSheet("" & stats.uservar,False)

             stats.uservar = ucase(left(stats.curcatvalue,1))
        end if

case CB_TOTAL
case CB_CONFLICT
case CB_BFORREADNOTES

     ' same as above, but if the current view is not categorised

     if stats.colcat(0) then
            ' view is categorised, so we deal with this in CB_CATEGORY

     elseif stats.uservar = "" Then
             ' very first call, we need to save the current first letter 

            stats.uservar = ucase(left(stats.entry.columnvalues(stats.colnum(0)-1),1))

     elseif not ucase(left(stats.entry.columnvalues(stats.colnum(0)-1),1)) = stats.uservar then
             ' if the first letter has changed we need to insert a new sheet
             
            call InsertNextSheet("" & stats.uservar,False)

            stats.uservar = ucase(left(stats.entry.columnvalues(stats.colnum(0)-1),1))
     end if

case CB_BFORWRITECOM
case CB_AFTERWRITECOM
case CB_BFORCOMACTION

     Call MoveFirstSheetLast("" & stats.uservar,False)

case CB_BFORNOTESACTION

   if not stats.savdoc is nothing then
       ' before save as...
   else
       ' before send to
   end if

case CB_TERMINATE
end select

Export View To Excel - New Sheet per Category

Const CB_INITIALISE = 3
Const CB_ACTIONONLY = 16
Const CB_BFOREXPORT = 10
Const CB_INVALID = 12
Const CB_CATEGORY = 13
Const CB_TOTAL = 14
Const CB_CONFLICT = 15
Const CB_BFORREADNOTES = 6
Const CB_BFORWRITECOM = 1
Const CB_AFTERWRITECOM = 9
Const CB_BFORCOMACTION = 5
Const CB_BFORNOTESACTION = 2
Const CB_TERMINATE = 4

select case stats.cbstatus
case CB_INITIALISE

      stats.uservar = 0

case CB_ACTIONONLY
case CB_BFOREXPORT
case CB_INVALID
case CB_CATEGORY

        ' this one is a bit simpler in that we always need to change if a
        ' category event occurs and the current category is top level 

        if stats.uservar = 0 then
        elseif stats.curcatlevel = 0 then
             stats.uservar = 0

             call InsertNextSheet("",true)
        end if

case CB_TOTAL
case CB_CONFLICT
case CB_BFORREADNOTES
case CB_BFORWRITECOM

       stats.uservar = stats.uservar + 1

case CB_AFTERWRITECOM
case CB_BFORCOMACTION

     Call MoveFirstSheetLast("",True)

case CB_BFORNOTESACTION

   if not stats.savdoc is nothing then
       ' before save as...
   else
       ' before send to
   end if

case CB_TERMINATE
end select

• Compare Integra
• Upgrade
• Manuals
• FAQs