• Creator
    Topic
  • #3880
    oferseinfeld
    Participant

    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

  • Author
    Replies
  • #6476
    Support 1a
    Participant

    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

    #6484
    Support 1a
    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
    #6483
    oferseinfeld
    Participant

    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?

    #6482
    Support 1a
    Participant

    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

    #6481
    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

    #6480
    Support 1
    Participant

    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

    #6478
    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

    #6486
    Support 1
    Participant

    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

    #6479
    Anonymous

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

    #6477
    Support 1
    Participant

    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

    #6485
    enam
    Participant

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

    #6475
    Support 1a
    Participant

    You would access the

    CreationDate

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

    Advansys Support

Viewing 12 replies - 1 through 12 (of 12 total)
  • You must be logged in to reply to this topic.