Applescript reporting syntax error. Automation script for excel using python, xlwings, VBA and applescript on macOS - Stack Over

admin管理员组

文章数量:1022989

The code aims to open an Excel spreadsheet, add VBA code (to create some buttons) and close the spreadsheet.

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.')

The code returns the following error:

420:421: syntax error: Esperava-se final de linha, encontrou-se """. (-2741)

I've been reading and re-reading these lines for 2 days now. I've tried using chatGPT, but all it gives me is what I already know: AppleScript uses 2 double quotes to cancel them when there's code inside code - in this case, VBA inside AppleScript - and, apparently, I'm using it wrong somewhere, I just can't figure out where it is.

If anyone with more experience can help, please do.

The code aims to open an Excel spreadsheet, add VBA code (to create some buttons) and close the spreadsheet.

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.')

The code returns the following error:

420:421: syntax error: Esperava-se final de linha, encontrou-se """. (-2741)

I've been reading and re-reading these lines for 2 days now. I've tried using chatGPT, but all it gives me is what I already know: AppleScript uses 2 double quotes to cancel them when there's code inside code - in this case, VBA inside AppleScript - and, apparently, I'm using it wrong somewhere, I just can't figure out where it is.

If anyone with more experience can help, please do.

本文标签：