0

I want to schedule an Excel macro to run every week on MacOS. I am using Applescript but when I run the script, it seems to open the file but the macro doesn't run. Also, how do I automatically enable macros when the Excel file is opened?

This is the script :

-- Set the path to your Excel file
set excelFile to "/Users/spagcoder/Desktop/proms webform/PROMS.xlsm"

-- Set the name of the macro to run
set macroName to "findRecipients"

-- Launch Excel in the background
tell application "Microsoft Excel"
    activate
    open workbook workbook file name excelFile
    
    tell workbook 1
        run VB macro macroName
    end tell
    
    -- Optionally save and close the workbook
    save workbook 1
    close workbook 1 saving yes
    
    -- Optionally quit Excel
    quit
end tell
SpagCoder
  • 1
  • 1
  • Works for me (using excel 2011). Maybe it's something in your macro. As an aside, given that you have provided the name of your workbook (ie excelFile), why don't you use it when referring to it, eg `save workbook excelFile` instead of `save workbook 1`? Also, the don't ask to enable option is in the Security preferences. It is a global setting though so it applies to every file you open, not just this one. – Mockman Jul 26 '23 at 00:18
  • @Mockman Thanks. When I run it, I get the error "...doesn’t understand the “run VB macro” message." – SpagCoder Jul 26 '23 at 10:03
  • That typically means that the command isn't framed in away that makes sense. For example, if you have a line that is only `run vb macro` inside a `tell worksheet` block, you will likely get that error. Try it while using the macro's actual name (rather than the variable). And maybe try referencing it fully, i.e. include the macro's sheet name, e.g. "sheet!findrec". BTW, I'm assuming that the macro is in this workbook. Does the macro have any arguments? – Mockman Jul 26 '23 at 10:14
  • @Mockman I got it to work by defining the macro as sheet1.findRecipients and I also had to place the "run VB macro macroName" under tell appliation, rather than tell worksheet. Thanks! – SpagCoder Jul 27 '23 at 10:53
  • My pleasure. Glad it worked out. Sometimes in the applescript dictionary, it will give you clues about where a command should go but it's not always clear. – Mockman Jul 27 '23 at 15:07

0 Answers0