Advansys

August 22, 2002 at 5:11 pm #6484
Participant
Here is a simple example of exporting GroupWise appointments to Excel. We created this applet using GroupWise 6 and Excel 2000.
We hope this helps.
Advansys Support
'-------------------------------------------------------------------------------
' Formativ Example
' Export GroupWise appointments to Excel
' Designed by: Formativ Business Solution Team
' Advansys Corporation (www.advansyscorp.com)
' Version 1.5
' 
' DESCRIPTION:
' This applet will export GroupWise appointments into MS Excel.  The applet also 
' displays the total duration of the appointments in excel.
'-------------------------------------------------------------------------------


HRT = Chr(13) & Chr(10)
DEFAULTFOLDER = Utilities.GetDataDirectory
Const CAPTION = "Formativ Examples"


'-------------------------------------------------------------------------------
' Main line processing
'-------------------------------------------------------------------------------
Sub Main(Client, GWEvent)
  
  dim iStartDate
  dim iEndDate  
  dim iDialogClass
  dim iControllerClass
  
  cExit = 999
  cIntro = 101
  cDateRange = 103  
  Cmd = cIntro
  
  set iControllerClass = new ControllerClass
  set iDialogClass = new DialogClass 
  
  iDialogClass.DisplayDlg 
  
  do while Cmd <> cExit
    
    ' Introduction dialog
    if (Cmd = cIntro) then
      select case iDialogClass.iDlg.Execute
        case Btn1 Cmd = cDateRange
        case else Cmd = cExit
      end select
    end if
    
    ' Check date range
    if (Cmd = cDateRange) then  
      iStartDate = iDialogClass.iStartDateCTL.Date
      iEndDate = iDialogClass.iEndDateCTL.Date    
      if  (iEndDate >= iStartDate) then
        if iControllerClass.ExportMessages(iStartDate, iEndDate) then
          Cmd = cExit  
        else
          Cmd = cIntro
        end if  
      else
        call msgbox("Invalid end date. The end date need to be after the start date.", vbInformation, CAPTION)
        Cmd = cIntro
      end if
    end if
    
  loop
  
  set iDialogClass = nothing
  set iControllerClass = nothing
  
End Sub


'-------------------------------------------------------------------------------
' ControllerClass
' Manage the whole process
'-------------------------------------------------------------------------------
Class ControllerClass
  
  private iTotalDuration
  private iExcel
  private iStatusDlg
  private iCalendar
  
  ' Constructor
  private sub class_initialize  
    iTotalDuration = 0
    Set iStatusDlg = Utilities.NewStatusDialog    
    set iCalendar = Groupwise.Account.Calendar
  end sub
    
  ' Destructor
  private sub class_terminate
    set iExcel = nothing
    set iCalendar = nothing
    set iStatusDlg = nothing
  End Sub
  
  
  ' Get the excel object
  private function GetExcelObject()
    GetExcelObject = TRUE
    On error resume next
    Set iExcel = GetObject("Excel.Application")
    if IsEmpty(iExcel) then
      Set iExcel = CreateObject("Excel.Application")
      if IsEmpty(iExcel) then
        iStatusDlg.Hide
        Call MsgBox("Could not connect to Excel - Please check it is correctly installed and try again.", vbCritical, CAPTION)
        GetExcelObject = FALSE
      end If 
    end if  
  end function  
  
  
 ' Write the heading into the cell
  private sub WriteHeadingIntoCell(iNewWorkBook, aTotal)
    
    iRow = 1
    
    ' Total appointments
    with iNewWorkBook.ActiveSheet.Cells(iRow, 1)
      .Value = "Total Appointments:"
      .Font.Size = 11   
      .Font.FontStyle = "Bold"  
      .Font.Color = vbBlue       
    end with  
    
    with iNewWorkBook.ActiveSheet.Cells(iRow, 2)
      .Value = aTotal
      .Font.Size = 11  
      .Font.FontStyle = "Bold"  
      .Font.Color = vbBlue       
    end with  
        
    iRow = iRow + 1
        
    with iNewWorkBook.ActiveSheet.Cells(iRow, 1)
      .Value = "Total Duration:"
      .Font.Size = 11   
      .Font.FontStyle = "Bold"  
      .Font.Color = vbBlue       
    end with   
    
    if (iTotalDuration >= 60) then
      iHour = round((iTotalDuration /60), 0) 
      iMinutes = iTotalDuration mod 60     
      
      if (iMinutes > 0) then
        iTotalDuration = iHour & " hours and " & iMinutes & " minutes"
      else
        iTotalDuration = iHour & " hours"    
      end if  
    else
      iTotalDuration = iTotalDuration & " (Minutes)"
    end if
       
    with iNewWorkBook.ActiveSheet.Cells(iRow, 2)
      .Value = iTotalDuration
      .Font.Size = 11   
      .Font.FontStyle = "Bold"  
      .Font.Color = vbBlue       
    end with  
           
    iRow = iRow + 2  
    
    '  
    with iNewWorkBook.ActiveSheet.Cells(iRow, 1)
      .Value = "Subject"
      .Font.Size = 10   
      .Font.FontStyle = "Bold"  
    end with  
         
    with iNewWorkBook.ActiveSheet.Cells(iRow, 2)
      .Value = "Place"
      .Font.Size = 10   
      .Font.FontStyle = "Bold"  
    end with 
             
    with iNewWorkBook.ActiveSheet.Cells(iRow, 3)
      .Value = "Start Date"
      .Font.Size = 10   
      .Font.FontStyle = "Bold"  
    end with 
          
    with iNewWorkBook.ActiveSheet.Cells(iRow, 4)
      .Value = "End Date"
      .Font.Size = 10   
      .Font.FontStyle = "Bold"  
    end with
       
    with iNewWorkBook.ActiveSheet.Cells(iRow, 5)
      .Value = "Duration (Minutes)"
      .Font.Size = 10   
      .Font.FontStyle = "Bold"  
    end with
                                  
  end sub  
  
  
  ' Write the entry into the cell
  private sub WriteIntoCell(iNewWorkBook, iRow, aSubject, aPlace, aStartDate, aEndDate)
    
    iDuration = DateDiff("n", aStartDate, aEndDate)
    
    with iNewWorkBook.ActiveSheet
      .Cells(iRow, 1).Value = aSubject    
      .Cells(iRow, 2).Value = aPlace           
      .Cells(iRow, 3).Value = aStartDate     
      .Cells(iRow, 4).Value = aEndDate        
      .Cells(iRow, 5).Value = iDuration                                                            
    end with
    iTotalDuration = iTotalDuration + iDuration
    iRow = iRow + 1
  end sub
    
    
  ' Write duration column total
  private sub WriteColumnTotal(iNewWorkBook, iRow, aTotal)
    with iNewWorkBook.ActiveSheet  
      .Cells(iRow, 4).Value = "Total"    
      .Cells(iRow, 4).Font.FontStyle = "Bold"            
      .Cells(iRow, 5).Value = aTotal      
      .Cells(iRow, 5).Font.FontStyle = "Bold"                                                                 
    end with    
  end sub    
  
  
  ' Publish appointments into excel
  private sub PublishIntoMSExcel(iTotalItems, iMessagesList)
    
    dim iNewWorkBook
    
    iRow = 5
    
    iExcel.visible = TRUE
    
    with iStatusDlg
      .Title = "Writing into excel..."
      .ProgressRange = iTotalItems
      .show
    end with
            
    Set iNewWorkBook = iExcel.WorkBooks.Add
    
    
    ' Loop through the messages list and extract the message object      
    for x = 1 to iTotalItems
      set iMsg = iMessagesList.Item(x)
      
      if not iMsg is nothing then
        call WriteIntoCell(iNewWorkBook, iRow, iMsg.Subject, iMsg.Place, iMsg.StartDate, iMsg.EndDate)    
        iStatusDlg.statustext = "Subject: " & iMsg.Subject
      end if
      
     iStatusDlg.progressposition = x
    next
    
    ' Type the duration column total
    call WriteColumnTotal(iNewWorkBook, iRow, iTotalDuration)
            
    ' Type the heading
    call WriteHeadingIntoCell(iNewWorkBook, iTotalItems)
            
    ' Auto fit the section
    with iExcel
      .Cells.Select
      .Selection.Columns.AutoFit   
      .Range("A1:A1").Select
    end with
    
    iStatusDlg.hide
    call msgbox("MS Excel document created and opened for review.", vbInformation, CAPTION)
         
  end sub
    
  
  ' Find appointments messages and export into excel
  public function ExportMessages(aStartDate, aEndDate)
    
    dim iTotalItems
    dim iMessagesList
    
    ExportMessages = TRUE
    
    with iStatusDlg
      .Title = "Initializing..."
      .Show
    end with
        
    aStartDate = Year(aStartDate) & "/" & Month(aStartDate) & "/" & Day(aStartDate) 
    aEndDate = Year(aEndDate) & "/" & Month(aEndDate) & "/" & Day(aEndDate)     
    iFilter = ("(APPOINTMENT) AND (ACCEPTED) AND (ON_CALENDAR) AND (START_DATE >= " & aStartDate & " AT 00:00:00 AND START_DATE <= " & aEndDate & " AT 23:59:59)")
  
    set iMessagesList = iCalendar.FindMessages(iFilter)
    iTotalItems =  iMessagesList.Count    
    
    ' If there is any appointments messages found
    if (iTotalItems > 0) then
      if GetExcelObject then
        call PublishIntoMSExcel(iTotalItems, iMessagesList)
      end if      
    else
      iStatusDlg.hide
      call msgbox("No appointments were found.  You may need to modify the date range.", vbInformation, CAPTION)
      ExportMessages = FALSE
    end if
    
    set iMessagesList = nothing
  end function
  
End Class



'-------------------------------------------------------------------------------
' DialogClass
' Manage all the dialogs in this applet
'-------------------------------------------------------------------------------
Class DialogClass
  
  public iDlg
  public iEndDateCTL
  public iStartDateCTL
  
  ' Constructor
  private sub class_initialize  
  end sub
    
  ' Destructor
  private sub class_terminate
    set iDlg = nothing
    set iEndDateCTL = nothing
    set iStartDateCTL = nothing
  End Sub
  
  ' Introduction dialog
  public sub DisplayDlg
    Set iDlg = Utilities.NewControlBoxDialog    
    with iDlg
      .AutoSize = TRUE
  	  .Caption = CAPTION
  	  .Title = "Export Appointments to Excel"
  	  .Description = HRT & "This applet exports GroupWise appointments to MS Excel.  Please specify a date range and press OK to continue."      
    end with     
    
    Set iStartDateCTL = iDlg.AddDateTimeControl
    With iStartDateCTL
      .Caption = "Start date"
      .Hint = "Select start date"
    End With
               
               
    Set iEndDateCTL = iDlg.AddDateTimeControl
    With iEndDateCTL
      .Caption = "End date"
      .Hint = "Select end date"
    End With
                   
  end sub
    
End Class
Reply To: publish to excel?
