6 Replies Latest reply on Jan 10, 2016 3:30 PM by jp98

    Run Macro in excel  before import


      How do i open an excel spreadsheet, run a macro inside that sheet, save and close it and then import into my database all from a script in filemaker that is triggered by a button?

        • 1. Re: Run Macro in excel  before import

          The easiest and most effective way is to *NOT* store the macro in excel but just use the equivalent VBscript code and trigger that from FM.  That will be easier to maintain the code and not have a lot of security warnings issues.

          1 of 1 people found this helpful
          • 2. Re: Run Macro in excel  before import

            so will the vbscript run on any excel document that doesn't even have macros in it - is that correct?


            and so you just have to specify the file that the vbscript will run in and then import it - correct again?


            how do you make a vbscript from the macros?

            • 3. Re: Run Macro in excel  before import

              could a macro be run in an excel document via send dde execute?

              • 4. Re: Run Macro in excel  before import

                Sure it can... google "excel command line switches"



                embedded macros are going to trigger all sort of security warnings just because they are embedded.  The only safe way around it is to get a "code signing certificate" to sign the macro code.  That's going to set you back a few hundred dollars.  And you still have a deployment issue.  What if you come up with some slight changes to the macro code.  Then you need to make sure that everyone is working off the same version of the excel files.  Whereas if you keep the code inside FM then you do not need to distribute or worry about code that is outside of your solution.

                • 5. Re: Run Macro in excel  before import

                  good point. thanks again for your help!

                  • 6. Re: Run Macro in excel  before import

                    i just tried using the following vbscript and it works perfectly with no security warnings or anything. however there are still the other points you mentioned so i will only be using this as a last resort.


                    Option Explicit


                    On Error Resume Next




                    Sub ExcelMacroExample()


                      Dim xlApp

                      Dim xlBook


                      Set xlApp = CreateObject("Excel.Application")

                      Set xlBook = xlApp.Workbooks.Open("C:\FileName.xls", 0, True)

                      xlApp.Run "MacroName"



                      Set xlBook = Nothing

                      Set xlApp = Nothing


                    End Sub



                    edit: the only problem with using send event from filemaker is the macro opens a popup and even if it is set to bring target application to foreground it doesn't show up!