用Python抓取Outlook日历

Question

我有一个VBA脚本，可以抓取MS Outlook项目（特别是Outlook约会），并在excel表中显示结果。它只是抓住了接下来7天的会议

我想知道是否有办法让这两个 a） 转换为等效的python脚本或 b） 调用以运行此vba脚本并将结果输出到python列表中

阻力最小的道路是什么

Sub FindAppts()

Application.EnableEvents = False
    Dim myStart As Date
    Dim myEnd As Date
    Dim ws1 As Worksheet
    Dim CalRawOutput As Range
    Dim o As Outlook.Application
    Dim ons As Outlook.Namespace
    Dim oCalendar As Outlook.Folder
    Dim oItems As Outlook.Items
    Dim oItemsInDateRange As Outlook.Items
    Dim oFinalItems As Outlook.Items
    Dim oAppt As Outlook.AppointmentItem
    Dim strRestriction As String
    Dim lastrownum As Integer
    Dim OutputRange As Range

Set ws1 = ActiveWorkbook.Sheets("Calendar")
lastrownum = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).row
Set OutputRange = ws1.Range(Cells(53, 2).Address(), Cells(lastrownum, 5).Address())
OutputRange.ClearContents


    myStart = Date
    myEnd = DateAdd("d", 7, myStart)

    'Construct filter for the next 7-day date range
    strRestriction = "[Start] >= '" & _
    Format$(myStart, "mm/dd/yyyy hh:mm AMPM") _
    & "' AND [End] <= '" & _
    Format$(myEnd, "mm/dd/yyyy hh:mm AMPM") & "'"
    'Check the restriction string
    Set o = New Outlook.Application
    Set ons = o.GetNamespace("MAPI")
    Set oCalendar = ons.GetDefaultFolder(olFolderCalendar)
    Set oItems = oCalendar.Items
    oItems.IncludeRecurrences = True
    oItems.Sort "[Start]"
    'Restrict the Items collection for the 7-day date range
    Set oItemsInDateRange = oItems.Restrict(strRestriction)
        & "0x0037001E" & Chr(34) & " like '%team%'"
    oItemsInDateRange.Sort "[Start]"
r = 1
Set CalRawOutput = ws1.Range("B53:E100")
    For Each oAppt In oItemsInDateRange


            CalRawOutput.Cells(r, 1).Value = oAppt.Subject
            CalRawOutput.Cells(r, 2).Value = oAppt.Start
            CalRawOutput.Cells(r, 3).Value = oAppt.End
            CalRawOutput.Cells(r, 4).Value = oAppt.Location
            r = r + 1
    Next
Application.EnableEvents = True
End Sub

Answer 1

Application.Run方法允许运行宏或调用函数。这可用于运行用Visual Basic或Microsoft Excel宏语言编写的宏，或运行DLL或XLL中的函数。第一个参数可以是带有宏名称的字符串、指示函数所在位置的范围对象，也可以是已注册DLL（XLL）函数的寄存器ID。如果使用字符串，将在活动工作表的上下文中计算该字符串

Application.Run "FindAppts"

如果VBA子文件位于特定文件中，则需要使用以下结构：

Application.Run "'My Work Book.xls'!FindAppts"

因此，基本上您只需要从Python自动化Excel并使用Run方法来运行代码

但是Excel对象模型在所有类型的应用程序或编程语言中都很常见。您可以将代码移植到Python，有关详细信息，请参见Automation Excel from Python。例如，看看Python Win32 extensions和openpyxl