publish to excel?

[oferseinfeld]

my bos asked me i can export tje detaila of his groupwise appointment to excel because he want to summerize the time he spend for each subject?

i want to use the applet of publish folder to word and change it to excel. i want that the fields of start time and end time will export to excel too
can i do it?
which changes did i have to do in this applets

Topic

[Support 1a]

Yes, this is certainly possible. We will prepare a simple example demonstrating the basics of Excel integration and post it here in the next couple of days.

Advansys Support

[Support 1a]

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

[oferseinfeld]

I COPY THE EXPORT TO EXCEL EXAMPLE TO MY FORMATIVE 1.X VERSION AND GW 5.5

THE EDITOR CAN NOT RUN THIS APPLET AND I HAVE ERROR MESSAGE “ASSERTION FAILURE”
I CAN NOT SUCCED TO SEE THE CONTENT OF THIS APLLET WITH FORMATIVE EDITOR
WHAT CAN I DO?

[Support 1a]

Assuming you are referring to the example code above, how did you get the source code into an applet?

To use the example, create a new applet in the Formativ editor, then copy and paste the source code above directly into the Formativ editor. (You cannot take source code and save it as a .VBF file. VBF files contain more than just source code).

If you still have problems after trying the above, please advise your GW version and the exact assert error message text.

Advansys Support

[Anonymous]

Hi,

I have problem exporting data to Excel when the data is a block of strings, meaning the hard return is long and have many lines. Got an error says:”Unexpected runtime error”

Should I format the cell or the data? and How?

Thanks

[Support 1]

There are probably several ways you can do this. Here are two:

• Set the Excel cell wraptext to accept hard returns:

  ActiveSheet.Cells(0, 1).WrapText = True

• Before exporting to Excel, remove hard returns:

  iValue = Replace(iValue, vbCrLf, ” “)

I hope this helps you.

Advansys Support

[Anonymous]

Hi, thanks for your help

I tried to use the WrapText method in the procedure and when I call the procedure (codes below), but it doesn’t seem to work for me. Can you tell me if I’ve put the line of code in the wrong place or what seems to be the problem? I appreciate your helps.

private sub WriteIntoCell(iNewWorkBook, iRow, aSubject, aMessageID, aStartDate, aDueDate, aBodyText)

iDuration = DateDiff(“n”, aStartDate, aDueDate)

iNewWorkBook.ActiveSheet.Cells(iRow, 6).WrapText = True

with iNewWorkBook.ActiveSheet
.Cells(iRow, 1).Value = aSubject
.Cells(iRow, 2).Value = aMessageID
.Cells(iRow, 3).Value = aStartDate
.Cells(iRow, 4).Value = aDueDate
.Cells(iRow, 5).Value = iDuration
.Cells(iRow,6).Value = aBodyText (ERROR MESSAGE ON THIS LINE: UNKNOW RUNTIME ERROR)
end with

iTotalDuration = iTotalDuration + iDuration
iRow = iRow + 1
end sub

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
MsgBox(iMsg.Subject & ” – ” & iMsg.MessageID & ” – ” & iMsg.BodyText)

call WriteIntoCell(iNewWorkBook, iRow, iMsg.Subject, iMsg.MessageID, iMsg.StartDate, iMsg.DueDate, iMsg.BodyText)

With iNewWorkBook.ActiveSheet
.Cells(iRow,6).WrapText = True
End With
iStatusDlg.statustext = “Subject: ” & iMsg.Subject

end if

iStatusDlg.progressposition = x
next

[Support 1]

We cannot reproduce the problem here. Does the error come up for all or only some messages?

If WriteIntoCell fails only with some messages, you may wish to send an email to support@advansyscorp.com . If this suits you, I suggest you attach to your email some sample messages that fail. Please note that Advansys treats client test data as strictly confidential.

Have you tried to use the .Text property instead of .Value?

Have you tried using my other suggestion, to replace carriage return/linefeed pairs by a space character? You could experiment with replacing vbCr and vbLf instead of vbCrLf.

I hope this helps you.

Advansys Support

[Anonymous]

How can i export the alarmtime to the excel-sheet?

[Support 1]

I know of two ways to obtain the alarm time.

• Use AlarmTime to get the time at which the AlarmProgram is to execute. If the alarm is not set, this property will return “12:00:00 AM”

  MsgBox oMsg.AlarmTime

• Use AlarmSet to check that an alarm was set for this appointment, then use AlarmReminderMinutes to get the number of minutes before the alarm rings. These properties were introduced in GroupWise 6.5 SP2 and later.

  if (GroupWise.EnvVersionName >= "6.5") then
    if oMsg.AlarmSet then
      MsgBox oMsg.AlarmReminderMinutes
    end if
  end if

See the online GroupWise API reference here.

I hope this helps you.

Advansys Support

[enam]

What can I use if I would like to extract the created date as well?

[Support 1a]

You would access the

CreationDate

property of the item. See the GroupWise Object API reference mentioned in the post above.

Advansys Support

[Author]

Replies