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

    Run Macro in excel  before import

    jp98

      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
          wimdecorte

          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
            jp98

            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
              jp98

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

              • 4. Re: Run Macro in excel  before import
                wimdecorte

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

                BUT

                 

                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
                  jp98

                  good point. thanks again for your help!

                  • 6. Re: Run Macro in excel  before import
                    jp98

                    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

                     

                    ExcelMacroExample

                     

                    Sub ExcelMacroExample()

                     

                      Dim xlApp

                      Dim xlBook

                     

                      Set xlApp = CreateObject("Excel.Application")

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

                      xlApp.Run "MacroName"

                      xlApp.Quit

                     

                      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!