macOS下Python调用AppleScript操作Excel遇语法错误求助

运行以下Python代码时返回错误：420:421: syntax error: Esperava-se final de linha, encontrou-se """. (-2741)，代码目的是打开Excel表格、添加创建按钮的VBA代码后关闭表格：

import subprocess

# This function is called at the end of another python file (everything else works fine, the error is right here)

def executeMAC():
    applescript = """
    tell application "Microsoft Excel"
    activate
    open "/---/---/---/registers2024.xlsx"

       tell workbook 1
           set vbaCode to "
           sub CreateButtons()
               Dim btnDate As Object
               Dim btnName As Object
               Dim btnShowAll As Object
               Dim ws As Worksheet
               Set ws = ThisWorkbook.Sheets(""Sheet1"")
    
               Set btnDate = ws.Buttons.Add(700, 50, 100, 30)
               btnDate.OnAction = ""DateFilter.DateFilter""
               btnDate.Caption = ""Filter by Date""
               btnDate.Placement = xlFreeFloating
    
               Set btnName = ws.Buttons.Add(700, 100, 100, 30)
               btnName.OnAction = ""NameFilter.NameFilter""
               btnName.Caption = ""Filter by Name""
               btnName.Placement = xlFreeFloating
    
               Set btnShowAll = ws.Buttons.Add(700, 150, 100, 30)
               btnShowAll.OnAction = ""ShowAllData.ShowAllData""
               btnShowAll.Caption = ""Show All Data""
               btnShowAll.Placement = xlFreeFloating
    
               Set btnGeneratePDF = ws.Buttons.Add(700, 200, 100, 30)
               btnGeneratePDF.OnAction = ""GeneratePDF.GeneratePDF""
               btnGeneratePDF.Caption = ""Generate PDF""
               btnGeneratePDF.Placement = xlFreeFloating
           End Sub
           "
           set vbModule to make new VB project at end
           tell vbModule
               make new VB component at end with properties {name:"CreateButtons", code content:vbaCode}
           end tell
       end tell
    
       save workbook 1 in "/---/---/---/registers2024.xlsm"
       close workbook 1

    end tell
    """

    # AppleScript Execute
    process = subprocess.run(['osascript', '-e', applescript], text=True)
    if process.returncode == 0:
        print('- VBA code inserted')
    else:
        print('- An error occurred while inserting VBA Code.')

错误根源是多层嵌套字符串的引号转义错误，以及AppleScript处理多行VBA代码的语法问题，同时VBA中的常量xlFreeFloating在AppleScript环境中无法识别，需要替换为对应数值。以下是修正后的代码：

import subprocess

def executeMAC():
    applescript = """
    tell application "Microsoft Excel"
        activate
        set sourcePath to "/---/---/---/registers2024.xlsx"
        set savePath to "/---/---/---/registers2024.xlsm"
        open sourcePath
        
        tell workbook 1
            -- 用AppleScript的linefeed拼接多行VBA代码，避免换行语法错误
            set vbaCode to "Sub CreateButtons()" & linefeed & ¬
                "    Dim btnDate As Object" & linefeed & ¬
                "    Dim btnName As Object" & linefeed & ¬
                "    Dim btnShowAll As Object" & linefeed & ¬
                "    Dim btnGeneratePDF As Object" & linefeed & ¬
                "    Dim ws As Worksheet" & linefeed & ¬
                "    Set ws = ThisWorkbook.Sheets(\"\"\"Sheet1\"\"\")" & linefeed & ¬
                "" & linefeed & ¬
                "    Set btnDate = ws.Buttons.Add(700, 50, 100, 30)" & linefeed & ¬
                "    btnDate.OnAction = \"\"\"DateFilter.DateFilter\"\"\"" & linefeed & ¬
                "    btnDate.Caption = \"\"\"Filter by Date\"\"\"" & linefeed & ¬
                "    btnDate.Placement = 3" & linefeed & ¬ -- xlFreeFloating的数值是3
                "" & linefeed & ¬
                "    Set btnName = ws.Buttons.Add(700, 100, 100, 30)" & linefeed & ¬
                "    btnName.OnAction = \"\"\"NameFilter.NameFilter\"\"\"" & linefeed & ¬
                "    btnName.Caption = \"\"\"Filter by Name\"\"\"" & linefeed & ¬
                "    btnName.Placement = 3" & linefeed & ¬
                "" & linefeed & ¬
                "    Set btnShowAll = ws.Buttons.Add(700, 150, 100, 30)" & linefeed & ¬
                "    btnShowAll.OnAction = \"\"\"ShowAllData.ShowAllData\"\"\"" & linefeed & ¬
                "    btnShowAll.Caption = \"\"\"Show All Data\"\"\"" & linefeed & ¬
                "    btnShowAll.Placement = 3" & linefeed & ¬
                "" & linefeed & ¬
                "    Set btnGeneratePDF = ws.Buttons.Add(700, 200, 100, 30)" & linefeed & ¬
                "    btnGeneratePDF.OnAction = \"\"\"GeneratePDF.GeneratePDF\"\"\"" & linefeed & ¬
                "    btnGeneratePDF.Caption = \"\"\"Generate PDF\"\"\"" & linefeed & ¬
                "    btnGeneratePDF.Placement = 3" & linefeed & ¬
                "End Sub"
            
            -- 创建VB项目并添加组件的正确语法
            set vbProject to VB project of workbook 1
            set vbComponent to make new VB component at end of vbProject with properties {name:"CreateButtons", code content:vbaCode}
            
            save workbook 1 in savePath
            close workbook 1
        end tell
    end tell
    """

    process = subprocess.run(['osascript', '-e', applescript], text=True)
    if process.returncode == 0:
        print('- VBA code inserted')
    else:
        print('- An error occurred while inserting VBA Code.')

关键修正点： #

• 引号转义：在Python的三重引号字符串中，要让AppleScript传递给VBA的双引号，需要写成\"\"\"（Python解析后传给AppleScript是""，AppleScript再解析为VBA的单个"）。
• 多行代码处理：用linefeed和¬（AppleScript的行续符）拼接多行VBA代码，避免直接换行导致的语法错误。
• 常量替换：将VBA常量xlFreeFloating替换为对应的数值3，因为AppleScript无法识别VBA的内置常量。
• VB项目操作语法：修正创建VB组件的方式，直接操作工作簿的VB项目，而非创建新的VB项目。

内容的提问来源于stack exchange，提问作者ItalloEduardo