如何将Outlook日历导出到Excel每周时间表? [英] How to export an Outlook calendar into an Excel weekly timesheet?

问题描述

我的老板在Outlook中安排了(不同项目的)会议日历.

My boss has a calendar of meetings scheduled (of different projects) in Outlook.

如何在Excel工作表中输入每天在每个项目上花费的时间?

How do I input the time spent on each project on each day in an Excel worksheet?

Excel工作表由一列中编写的所有项目组成，一周中的每一天为一列.

The Excel worksheet consists of all projects written in a column, and one column for each day of the week.

推荐答案

一个简单的Google搜索，并提供一些参考:

A simple google search, and a little refacto :

Option Explicit

Sub ListAppointments()
    Dim olApp As Object
    Dim olNS As Object
    Dim olFolder As Object
    Dim olApt As Object
    Dim NextRow As Long
    Dim FromDate As Date
    Dim ToDate As Date

    FromDate = CDate("08/25/2017")
    ToDate = CDate("12/31/2017")

    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If Err.Number > 0 Then Set olApp = CreateObject("Outlook.Application")
    On Error GoTo 0

    Set olNS = olApp.GetNamespace("MAPI")
    Set olFolder = olNS.GetDefaultFolder(9) 'olFolderCalendar
    NextRow = 2

    With Sheets("Sheet1") 'Change the name of the sheet here
        .Range("A1:D1").Value = Array("Project", "Date", "Time spent", "Location")
        For Each olApt In olFolder.Items
            If (olApt.Start >= FromDate And olApt.Start <= ToDate) Then
                .Cells(NextRow, "A").Value = olApt.Subject
                .Cells(NextRow, "B").Value = CDate(olApt.Start)
                .Cells(NextRow, "C").Value = olApt.End - olApt.Start
                .Cells(NextRow, "C").NumberFormat = "HH:MM:SS"
                .Cells(NextRow, "D").Value = olApt.Location
                .Cells(NextRow, "E").Value = olApt.Categories
                NextRow = NextRow + 1
            Else
            End If
        Next olApt
        .Columns.AutoFit
    End With

    Set olApt = Nothing
    Set olFolder = Nothing
    Set olNS = Nothing
    Set olApp = Nothing
End Sub

这篇关于如何将Outlook日历导出到Excel每周时间表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！